start packaged procedure with input parameters using job

2621671

Hi all,

My need is to modify trigger in order to read updated value in columns COL1, COL2 from table scheme1.table1 and pass them into packaged procedure scheme1.PKG_A.prepare1 when one-time job starts.

How do I perform it?

declare

vJobNumber binary_integer;

BEGIN

        dbms_job.submit(

        job => vJobNumber,

        next_date => SYSDATE,

        interval => NULL,

        what => 'begin scheme1.PKG_A.prepare1; end;'

        );

        commit;

END;

/

BluShadow

What trigger?

if that's supposed to be the code for a trigger, I wouldn't recommend it at all.

a) you shouldn't commit in triggers

b) it's possible for a trigger to fire more than once in the background, so doing anything in a trigger that is going to run 'independently' or seperately from the trigger could have the side effect of being run more than once.  So, running scheduled jobs, or sending emails, or writing out to files etc. is generally not a good idea from within triggers.

Aside from that, inside your trigger you can use the :NEW.column_name syntax to get the updates values of the columns you want, and then you can just call your procedure, passing those values as parameters (assuming your procedure accepts parameters - if it doesn't how do you expect it to work?)

GregV

Hi,

Why do you need a job for that? DBMS_JOB.SUBMIT requires commit, and as pointed out by BlueShadow, you can't commit in a trigger.

Anyway, if you want to pass the values to a program called through dbms_job, then pass them in the what parameter:

what => 'scheme1.PKG_A.prepare1(' || param1 || ',' || param2 || ');'

There's no need for BEGIN and END.

Mike Kutz

SQL Injection detected.

The technique I've used to avoid SQL Injection involves a table that holds the parameters.

(I found it on AskTom)

eg:

create table parameter_table ( job_id int, param1 number, param2 varchar2(20) );

The procedure that DBMS_JOB actually calls is a 'wrapper' that does the lookup then calls the actual function.

procedure wrapper( p_job_id in int )
as
  l_param1 parameter_table.param1%TYPE;
  l_param2 parameter_table.param2%TYPE;
begin
  select param1,param2 into l_param1,l_param2
  from parameter_table
  where job_id = p_job_id;
  actual_procedure( l_param1, l_param2 );
end;

The setup becomes something like this:

dbms_job.submit( job => vJobNumber, what => 'wrapper( JOB );'  .... );  -- JOB is defined as the Job ID within this context.
insert into parameter_table (job_id, param1,param2) values (vJobNumber, .... );

As others have said, you want to rethink your use of Triggers.

MK

GregV

Hi Mike,

Well spotted. That's why I use only Scheduler now. Arguments are properly handled through dedicated procedures.

Mike Kutz

GregV

Scheduler came to mind as I was typing that out as well.

Personally, I use DBMS_JOB when it is part of a transaction (ie i need to back it out on an error) and Scheduler more for 'autonomous transaction' type stuff...(eg send e-mail, print something)

Another option the OP has would be to use Advance Queuing.

Same concept:  foreground job places parameters in a table while the background job picking it up to process.

Depending on the Business Requirements, I'd probably recommend that.

MK