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 ('')