Oracle SQL Error ORA-01756 when inserting a string pattern in Jenkins, but works in IntelliJ IDEA Database Tool

Question

I'm trying to insert a pattern string into the PATTERN_SETTING table in Oracle using the following SQL:

INSERT INTO PATTERN_SETTING (ID, NAME, PATTERN)
VALUES (30, 'Alphanumeric and symbol', '^[a-zA-Z0-9!"#$%&''()*+,\-./:;<=>?@\\[\\]^_`{|}~]+$');

When I run this SQL in IntelliJ IDEA’s DB tool, it works perfectly. However, when I try to execute the same script via Jenkins, I get the following error:

INSERT INTO PATTERN_SETTING (ID, NAME, PATTERN)
VALUES (30, 'Alphanumeric and symbol', '^[a-zA-Z0-9!\"#$%&''()*+,\-./:
[WARN]   code :1756
java.sql.SQLSyntaxErrorException: ORA-01756: quoted string not properly terminated

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:45)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:933)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
    at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1640)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1603)
    at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:308)
    at com.tcg.sql.patch.DbScriptsExecutor.runScript(DbScriptsExecutor.java:212)
    at com.tcg.sql.patch.DbScriptsExecutor.integrate(DbScriptsExecutor.java:168)
    at com.tcg.sql.patch.DbScriptsExecutor.patch(DbScriptsExecutor.java:121)
    at com.tcg.sql.patch.DbScriptsExecutor.execute(DbScriptsExecutor.java:72)
    at com.tcg.sql.patch.DBPatchRunner.run(DBPatchRunner.java:49)
    at com.tcg.sql.patch.Main.entry(Main.java:19)
    at com.tcg.sql.patch.Main.main(Main.java:35)

It seems like the special characters in the PATTERN column may be causing issues during script execution in Jenkins. How can I resolve this issue?

I’ve already tried escaping single quotes by doubling them (e.g., ''), but the error persists in Jenkins. Any ideas on how to handle this?

From the SQL shown in the error message it appears be choking on the semicolon within the pattern, not the quotes. How is the statement text supplied to Jenkins? Also, have you considered using character classes? — Alex Poole
– Alex Poole, Commented Oct 9, 2024 at 16:59
It would be best if you showed your Jenkins code. I suspect it uses ; as a statement delimiter, and that needs to be escaped when you include it in the SQL. — Barmar
– Barmar, Commented Oct 9, 2024 at 17:20

Marmite Bomber · Accepted Answer · 2024-10-10 14:31:36Z

0

If nothing else helps use the chr function to get the semicolon, which is chr(59)

Rewritten insert

INSERT INTO PATTERN_SETTING (ID, NAME, PATTERN)
VALUES (30, 'Alphanumeric and symbol', '^[a-zA-Z0-9!"#$%&''()*+,\-./:'||
chr(59)||
'<=>?@\\[\\]^_`{|}~]+$');

answered Oct 10, 2024 at 14:31

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Error ORA-01756 when inserting a string pattern in Jenkins, but works in IntelliJ IDEA Database Tool

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related