Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
start packaged procedure with input parameters using job

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;
/
Answers
-
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?)
-
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.
-
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
-
Hi Mike,
Well spotted. That's why I use only Scheduler now. Arguments are properly handled through dedicated procedures.
-
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