I need to execute a dbms_job.submit procedure from inside a trigger. The problem is that this procedure has to have parameters from :new values. It looks like this
DBMS_JOB.submit(jobno, 'Pvehicle_tariff_schedule('':new.vehicle_class'') ;', :new.effective_dateTIME)
It doesn't interpret the parameter :new.vehicle_class the way it should, instead it leaves it as a string when schedules the job. If I look in in user_jobs the 'WHAT' field look like this:
WHAT
-----------
Pvehicle_tariff_schedule(':new.vehicle_class') ;
So what I did is I built a string containing the procedure name and parameters. It looks like this:
SQL:='DBMS_JOB.submit(jobno,''Pvehicle_tariff_schedule(''' || :new.vehicle_class || ''')'|| :new.effective_datetime || ' ;'
What can I do so after I build the string containing the name and parameters of the procedure to be able to run it?
