Forum Stats

  • 3,827,547 Users
  • 2,260,791 Discussions


start packaged procedure with input parameters using job

2621671 Member Posts: 53 Blue Ribbon
edited Apr 17, 2014 11:58AM in SQL & PL/SQL

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?


vJobNumber binary_integer;



        job => vJobNumber,

        next_date => SYSDATE,

        interval => NULL,

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






  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    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
    GregV Member Posts: 3,085 Gold Crown


    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
    Mike Kutz Member Posts: 6,195 Silver Crown

    SQL Injection detected.

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

    (I found it on AskTom)


    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 )
      l_param1 parameter_table.param1%TYPE;
      l_param2 parameter_table.param2%TYPE;
      select param1,param2 into l_param1,l_param2
      from parameter_table
      where job_id = p_job_id;
      actual_procedure( l_param1, l_param2 );

    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.


  • GregV
    GregV Member Posts: 3,085 Gold Crown

    Hi Mike,

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

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown


    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.


This discussion has been closed.