How to call a stored procedure in an Oracle Scheduler job

jobsoracleoracle-11g-r2plsqlstored-procedures

I want to call a stored procedure in an Oracle Scheduler job. My procedure and job are shown below:

Stored procedure

create or replace PROCEDURE emp_test                                              
IS
begin

delete from  emp;
insert into emp(empid,empname)
select empid1,empname1 from emp_temp;
end;

Job

BEGIN
dbms_scheduler.create_job (
job_name => 'emp_test_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
CALL emp_test();
END;',
start_date => SYSTIMESTAMP,
enabled => true,
repeat_interval => 'FREQ=DAILY');
END;

I am able to call procedure like CALL emp_test() with out using a job.

When I am using same thing in a job like above, the job was created successfully but the procedure is not running, rows are not inserted. I need run this job and rows should be inserted; can any one help me please?

Best Answer

You don't need CALL when creating a job (and parenthesis for a procedure that takes no arguments ). I'd also specify INTERVAL=1 explicitly.

BEGIN 
   dbms_scheduler.create_job ( 
    job_name => 'emp_test_JOB', 
    job_type => 'PLSQL_BLOCK', 
    job_action => 'emp_test;', 
    start_date => SYSTIMESTAMP, 
    enabled => true, 
    repeat_interval => 'FREQ=DAILY;INTERVAL=1'
   ); 
END;

Related Solutions

Oracle how to call procedure

If it does not take any parameters in or out then

BEGIN
DISABLE_TRIGGERS;
END;

Or when using the sqlplus interface EXEC DISABLE_TRIGGERS;

I have to hope that this is not production code as a procedure that disables all the triggers could have many consequences. This code as originally posted should be more comprehensive if used in production, particularly for a migration. Triggers do things, whether you agree with their use or not, they can enforce business logic and stitch together poorly implemented business logic.

Does the migration depend on any business logic in the triggers?
Will users be able to access the database during the migration? Hope not, any actions they take will not use the trigger logic.
does the success or failure of the migration depend on triggers being disabled?

You need to know when the triggers were disabled and re enabled and that all transactions stopped at the start of the migration and only resumed after the migration. Before, during and after the migration you need a log of all the actions that were taken so you can improve the process.

A better way to do this would be to add a parameter: 0 or 1 to disable or enable the triggers

something like

CREATE OR REPLACE PROCEDURE CHANGE_TRIGGERS( action_in IN NUMBER)
IS
  v_statement VARCHAR2(500);
  CURSOR trigger_cur
  IS
    SELECT trigger_name
     FROM   user_triggers;
v_action VARCHAR2(200);
BEGIN
ASSERT (action_in IN (0,1));  --where assert is part of your own error package
IF action_in = 0
THEN
  v_action := 'DISABLE';
ELSIF action_in = 1
THEN
  v_action := 'ENABLE';
END IF;
  FOR i in trigger_cur
  LOOP
    v_statement := 'ALTER TRIGGER '||i.trigger_name||' '||v_action;
    EXECUTE IMMEDIATE v_statement;
  END LOOP;
--add a statement like
--if debugging then DBMS_OUTPUT.PUT_LINE(v_action||' triggers action taken on '||CURRENT_TIMESTAMP); 
--ELSE
--log result to a table
--END IF;

END CHANGE_TRIGGERS;

MySQL stored procedure: loop through table, delete rows. Logic problem: won’t exit loop because of LIMIT option in query

There are two possible solutions:

set @z to null in the very beginning of your loop (prior to SET @sql_text2 = concat('....)

or instead of

SELECT id INTO @z FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;

use this:

SET @z = (SELECT id FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1);

Explanation:

When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.
see: https://dba.stackexchange.com/a/35207/12923

Best Answer

Related Solutions

Oracle how to call procedure

MySQL stored procedure: loop through table, delete rows. Logic problem: won’t exit loop because of LIMIT option in query

Related Question