Syntax error when a job is executed in anonymous block in Oracle

oracle

I'm trying to execute a job with an alter system command but I get a error. I know that the error is in SID=''INST1'''', but I don't know how to avoid it.
I can create the job but the error is when the job is executed.

This is the code of the job:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'JOB_Cambia_Planes_Prueba',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 
'BEGIN 
    EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''INST1'''';
exception when others then dbms_output.put_line(sqlerrm);       
END;
',
   start_date           =>  TO_DATE('16-12-2019 17:33','DD-MM-YYYY HH24:MI'),
   repeat_interval      => 'FREQ=DAILY', 
   enabled              =>  TRUE,
   comments             => 'Cambiar Planes');
exception when others then dbms_output.put_line(sqlerrm);      
END;

And the error is 

ORA-06550: line 2, column 89:
PLS-00103: Encountered the symbol "INST1" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem return
   returning <an exponent (**)> <> or != or ~= >= <= <> and or
   like like2 like4 likec between into using || multiset bulk
   member submultiset
The symbol "* was inserted before "INST1" to continue.

Best Answer

Not enough apostrophes.

SQL> !cat 1.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'JOB_Cambia_Planes_Prueba',
   job_type             => 'PLSQL_BLOCK',
   job_action           =>
'BEGIN
        EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''INST1'''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',
   start_date           =>  TO_DATE('16-12-2019 17:33','DD-MM-YYYY HH24:MI'),
   repeat_interval      => 'FREQ=DAILY',
   enabled              =>  TRUE,
   comments             => 'Cambiar Planes');
exception when others then dbms_output.put_line(sqlerrm);
END;
/

SQL> @1

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.run_job('JOB_Cambia_Planes_Prueba');
BEGIN dbms_scheduler.run_job('JOB_Cambia_Planes_Prueba'); END;

*
ERROR at line 1:
ORA-06550: line 2, column 82:
PLS-00103: Encountered the symbol "INST1" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || multiset bulk
member submultiset
The symbol "* was inserted before "INST1" to continue.
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 1

SQL> exec dbms_scheduler.drop_job('JOB_Cambia_Planes_Prueba');

PL/SQL procedure successfully completed.

SQL> !vi 2.sql

SQL> !cat 2.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'JOB_Cambia_Planes_Prueba',
   job_type             => 'PLSQL_BLOCK',
   job_action           =>
'BEGIN
        EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''''INST1'''''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',
   start_date           =>  TO_DATE('16-12-2019 17:33','DD-MM-YYYY HH24:MI'),
   repeat_interval      => 'FREQ=DAILY',
   enabled              =>  TRUE,
   comments             => 'Cambiar Planes');
exception when others then dbms_output.put_line(sqlerrm);
END;
/

SQL> @2

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.run_job('JOB_Cambia_Planes_Prueba');

PL/SQL procedure successfully completed.

To elaborate, this value of job_action

'BEGIN
        EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''INST1'''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',

is a string literal. Obviously, string literals are delimited with single quotes ('). That string, however, is a script containing another string literal, the ALTER SYSTEM command, which must be delimited too. As you know, you escape a quotation mark inside a string by doubling it. So, the ALTER SYSTEM command inside the string literal must be delimited with '' rather than '. This is what you have done, so far so good.

Now, the ALTER SYSTEM command, in its turn, also contains a string literal, namely INST1. This is now the second level of string nesting. You need to double the delimiters again, so INST1 should be delimited with ''''. In your snippet, however, it is delimited with '', which is wrong.

Therefore, the solution is to rewrite the … SID=''INST1'''' bit as … SID=''''INST1'''''', as already illustrated above. The closing six apostrophes are the four closing apostrophes for the INST1 value plus the two closing apostrophes for the ALTER SYSTEM command.

Related Question