CODEWITHSUNDEEP
sqloracle-databaseplsql
Piero Sanchez
Piero Sanchez

Reputation: 1

Create Job for running procedure with variable

I´m try to create a JOB whit this form:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'OWNER.TEST_PROM'
      ,start_date      => TO_TIMESTAMP_TZ('2018/12/07 03:00:00.324000 -05:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'freq=daily'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
    ,job_action      => ' 

    BEGIN 

    DECLARE

    VAR_1    VARCHAR2(30) := '001';
    VAR_2    VARCHAR2(20) := '';
    VAR_3    VARCHAR2(20) := TO_CHAR(TO_DATE(SYSDATE-15),''YYYYMMDD'');
    VAR_4    VARCHAR2(20) := TO_CHAR(TO_DATE(SYSDATE),''YYYYMMDD'');    

    OWNER.LOAD_DATA_LOCAL.LOAD_PROM          (VAR_1,VAR_2,VAR_3,VAR_4); 
    OWNER.LOAD_DATA_LOCAL.LOAD_COB_PROM      (VAR_1,VAR_2,VAR_3,VAR_4);
    OWNER.LOAD_DATA_LOCAL.LOAD_CUP           (VAR_1,VAR_2,VAR_3,VAR_4); 
    end;'
    ,comments        => 'Ejecuta LOAD_prueba_cupones'
    );
end;
/

When I run it, I get the following error:

Error at line 1
ORA-01756: quoted string not properly terminated
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

Script Terminated on line 1.

Upvotes: 0

Views: 640

Answers (3)

alexs
alexs

Reputation: 406

Or use the Oracle quoting string construct q'[ ... ]' and do not worry about the number of escape quotes:

 BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'OWNER.TEST_PROM'
      ,start_date      => TO_TIMESTAMP_TZ('2018/12/07 03:00:00.324000 -05:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'freq=daily'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
    ,job_action      => q'[ 

    BEGIN 

    DECLARE

    VAR_1    VARCHAR2(30) := '001';
    VAR_2    VARCHAR2(20) := '';
    VAR_3    VARCHAR2(20) := TO_CHAR(TO_DATE(SYSDATE-15),'YYYYMMDD');
    VAR_4    VARCHAR2(20) := TO_CHAR(TO_DATE(SYSDATE),'YYYYMMDD');    

    OWNER.LOAD_DATA_LOCAL.LOAD_PROM          (VAR_1,VAR_2,VAR_3,VAR_4); 
    OWNER.LOAD_DATA_LOCAL.LOAD_COB_PROM      (VAR_1,VAR_2,VAR_3,VAR_4);
    OWNER.LOAD_DATA_LOCAL.LOAD_CUP           (VAR_1,VAR_2,VAR_3,VAR_4); 
    end;]'
    ,comments        => 'Ejecuta LOAD_prueba_cupones'
    );
end;
/

Upvotes: 2

GMB
GMB

Reputation: 222432

The job_action declaration is enclosed in single quotes. Hence, you need to escape all single quotes that are within it, else Oracle is not able to properly parse it.

So you should replace every ' with '' within the job_action, namely in the declarations of VAR1 and VAR2 (VAR3 and VAR4 are fine already) :

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'OWNER.TEST_PROM'
      ,start_date      => TO_TIMESTAMP_TZ('2018/12/07 03:00:00.324000 -05:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'freq=daily'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
    ,job_action      => ' 

    BEGIN 

    DECLARE

    VAR_1    VARCHAR2(30) := ''001'';
    VAR_2    VARCHAR2(20) := '''';
    VAR_3    VARCHAR2(20) := TO_CHAR(TO_DATE(SYSDATE-15),''YYYYMMDD'');
    VAR_4    VARCHAR2(20) := TO_CHAR(TO_DATE(SYSDATE),''YYYYMMDD'');    

    OWNER.LOAD_DATA_LOCAL.LOAD_PROM          (VAR_1,VAR_2,VAR_3,VAR_4); 
    OWNER.LOAD_DATA_LOCAL.LOAD_COB_PROM      (VAR_1,VAR_2,VAR_3,VAR_4);
    OWNER.LOAD_DATA_LOCAL.LOAD_CUP           (VAR_1,VAR_2,VAR_3,VAR_4); 
    end;'
    ,comments        => 'Ejecuta LOAD_prueba_cupones'
    );
end;
/

See this Oracle doc for examples of creating jobs.

Upvotes: 2

Roger Cornejo
Roger Cornejo

Reputation: 1547

Try:

BEGIN 

DECLARE

VAR_1    VARCHAR2(30) := '001';
VAR_2    VARCHAR2(20) := '';
VAR_3    VARCHAR2(20) := TO_CHAR(TO_DATE(SYSDATE-15),'YYYYMMDD');
VAR_4    VARCHAR2(20) := TO_CHAR(TO_DATE(SYSDATE),'YYYYMMDD');    

begin
OWNER.LOAD_DATA_LOCAL.LOAD_PROM          (VAR_1,VAR_2,VAR_3,VAR_4); 
OWNER.LOAD_DATA_LOCAL.LOAD_COB_PROM      (VAR_1,VAR_2,VAR_3,VAR_4);
OWNER.LOAD_DATA_LOCAL.LOAD_CUP           (VAR_1,VAR_2,VAR_3,VAR_4); 
end;
--comments        => 'Ejecuta LOAD_prueba_cupones'
--);

end;

I'm guessing here because I have no way of testing the code, but there seemed to be some syntactical errors at first glance.

Upvotes: -1

Related Questions