Geek Answers Handbook

How to call a stored procedure in oracle with daily scheduled tasks?

I am new to oracle scripts. I wrote a cleaning procedure to clear all old data and save the latest data for 3 months ... the procedure is successful. its work when i called also manually. The procedure is as follows:

CREATE OR REPLACE PROCEDURE Archive IS v_query varchar2(2048); v_tablename VARCHAR2(50); v_condition varchar2(50); TYPE cur_typ IS REF CURSOR; c cur_typ; BEGIN OPEN c for 'select tablename,columnname from pseb.purge_tables'; FETCH c INTO v_tablename,v_condition; LOOP EXIT WHEN c%NOTFOUND; if(v_tablename ='cfw.DCTBLPERFCUMULATIVEMASTER') then v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB3MAINREG where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB4TODENERG where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERDFCUMULATIVEB5MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB6TODREG where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB7MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB8MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; v_query:='delete FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; else begin v_query:='delete FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90'; execute immediate v_query; end; end if; FETCH c INTO v_tablename,v_condition; end LOOP; close c; END; --Procedure

my JOb script looks like this:

 begin DBMS_SCHEDULER.CREATE_JOB ( job_name => 'purgeproc_automation', job_type => 'STORED_PROCEDURE', job_action => 'call pseb.archive();', repeat_interval => 'FREQ=DAILY;INTERVAL=2', /* every other day */ auto_drop => false, enabled => true, comments => 'My new job'); end; /

The task was successfully created, but the task status failed, but failed. What is the reason for this? it returns the following error:

 ORA-06550: line 1, column 728: PLS-00103: Encountered the symbol "PSEB" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "PSEB" to continue.

please help me solve this ...

+4
source share
3 answers

Omg, your code looks so complicated. First consider this simplification:

 CREATE OR REPLACE PROCEDURE Archive IS v_query varchar2(2048); BEGIN FOR REC IN (select tablename,columnname condition from pseb.purge_tables) LOOP if(rec.tablename ='cfw.DCTBLPERFCUMULATIVEMASTER') then v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB3MAINREG where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB4TODENERG where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERDFCUMULATIVEB5MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB6TODREG where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB7MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB8MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; v_query:='delete FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; else v_query:='delete FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90'; execute immediate v_query; end if; END LOOP; END; --Procedure

Alternative job definition by dbms_job.submit:

 declare jid number; begin dbms_job.submit( JOB => jid, WHAT => 'pseb.archive;', NEXT_DATE => SYSDATE, INTERVAL => 'sysdate +2'); end; / commit; -- <<--added commit here

Task verification method:

 select * from user_jobs;
+6
source

It looks like you are mixing two different ways to use create_job with creating a program. Please change your script to something like this:

 begin DBMS_SCHEDULER.CREATE_JOB ( job_name => 'purgeproc_automation', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN call pseb.archive(); END;', repeat_interval => 'FREQ=DAILY;INTERVAL=2', /* every other day */ auto_drop => false, enabled => true, comments => 'My new job'); end; /
0
source

The easiest way to execute a stored procedure from a scheduler job is to change job_type .

Rest you can use your own values. Try it and post the results.

Example:

 job_type => 'STORED_PROCEDURE', job_action => '"OWNER"."PROCEDURE_NAME"',
0
source

More articles:


All Articles