Document Type | Technical Information
Category | Administration
Applicable Product Versions | 6FS07, 6FS07PS, 7FS02, 7FS02PS
Document Number | TADTI096
Overview
When creating a JOB, if a Procedure, Function, or general SQL statement contains single quotes (') and they are not properly escaped, errors may occur.
This document provides guidance with examples on how to handle single quotes (') within query syntax when creating or modifying JOBs.
Method
Single quotes (') are symbols used to represent string literals.
Therefore, to represent a single quote within an SQL statement, you must use two consecutive single quotes ('').
When Creating a JOB
โ Incorrect Example
The following example shows an error caused by unescaped single quotes in the statement dbms_output.put_line('ok') within the JOB syntax.
SQL> DECLARE
job_no NUMBER;
BEGIN
DBMS_JOB.SUBMIT(job_no, 'dbms_output.put_line('ok');', SYSDATE, 'SYSDATE + 1');
END;
/
TBR-15146: PSM compilation error.
at line 4, column 47:
dbms_output.put_line('ok');
^^
โ Correct Example (Using Two Single Quotes)
SQL> DECLARE
job_no NUMBER;
BEGIN
DBMS_JOB.SUBMIT(job_no, 'dbms_output.put_line(''ok'');', SYSDATE, 'SYSDATE + 1');
END;
/As shown above, writing single quotes twice ('''') allows the JOB to be registered correctly.
When Modifying a JOB
When modifying an existing JOB using TiberoStudio's [Edit - Job] feature, the same string escape rules as tbSQL apply.
In other words, not only when creating a JOB but also when editing (modifying), you must write two single quotes ('').
1. Create a JOB
2. Select the created [JOB], right-click -> Select [Edit - Job]
3. Modify the string to DEV in the [What to execute] tab
An error occurs as shown below: JDBC-15146: PSM compilation error.
4. Write two single quotes ('')
|