how to call the stored procedure in oracle with the daily scheduled jobs?

Question

I am new to the oracle job scripts. I wrote some purge procedure to clean all the old data and retain the last 3 months data... procedure is executed successfully. its working when im calling manually also. 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 is as follows:

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;
/

Job was created successfully, but the job status is failed, not succeed . Whats the reason behind it? 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 guide me to solve this...

Solution 1

Omg, your code looks so complicated. Consider this simplification first:

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

A way to check job:

select * from user_jobs;

Solution 2

The easiest way to execute a stored procedure from scheduler job is by changing the job_type.

Rest you can use your own values. Try this and post back results.

Example:

job_type             => 'STORED_PROCEDURE',

job_action           => '"OWNER"."PROCEDURE_NAME"',

Comments

[Peter Å]

Then it seems like the scheduler can't access your package.

[Saranya Jothiprakasam]

Then how can i complete that task?

[Peter Å]

You need to find out with which user that the scheduler is running the job. Assuming that your package is in another schema you have to make sure that the scheduler user can access that schema where the package is created. Sorry, but little bit hard to be more specific when I don't have access to your environment.

[Saranya Jothiprakasam]

AS of ur guidance i checked the user that the scheduler is running. in Pseb schema itself one sample job is running. I created one more job in that same user for doing the same procedure its giving the same error. :(

[Saranya Jothiprakasam]

Hi mmm, Thanks for ur simplification. i created the job with ur sample code (using submit) . how to see the details of submitted jobs? or how to verify that job status whether its failed or succeeded?

[Saranya Jothiprakasam]

mmm I created the job it returns the pl/sql procedure successfully executed. but in the user_jobs table details is not there

[Gumowy Kaczak]

1. did you commit? 2. on which user did you run that pl/sql? 3. do you have access to dba user? if yes try all_jobs view.

[Saranya Jothiprakasam]

oh great mmm, thanq u so much for ur guidance.. i didnt give commit at that time, now its done properly :)

[Gumowy Kaczak]

does it run without any failures? you may need to tune it a bit - interval 'sysdate+2' means that it would add 2 days to the time when the job ends (so the time of run will be constantly moving forward in time). Better definition is to define time by giving correct date 'to_date(2012-12-01 12:00:00,'YYYY-MM-DD HH24:MI:SS') +2' so you'd be sure that next run date is the one you specified.

[Saranya Jothiprakasam]

let us continue this discussion in chat