Sitemap

Amicuk Programming Answers

Submitting Job with parameterised procedure using DBMS_JOB

-0001-11-30   Views:0

Advertisement

Hi, I need to submit a job which makes a call to a procedure with parameters as shown below: PROCDURE SUB_JOB ( P_PAR1 VARCHAR2, P_PAR2 VARCHAR2) IS L_JOBID INTEGER; BEGIN DBMS_JOB.SUBMIT ( L_JOBID, ' BEGIN PROC1 ('''||P_PAR1||''','''||P_PAR2||''');

Hi,
I need to submit a job which makes a call to a procedure with parameters as shown below:
PROCDURE SUB_JOB ( P_PAR1 VARCHAR2, P_PAR2 VARCHAR2)
IS
L_JOBID INTEGER;
BEGIN
DBMS_JOB.SUBMIT ( L_JOBID, ' BEGIN PROC1 ('''||P_PAR1||''','''||P_PAR2||'''); END;' );
COMMIT;
END;
But when I execute the above procedure I get the error like below:
PLS-00103: Encountered the symbol ")" when expecting one of the following: ( - + case mod new not null others <an identifier> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe <an alternatively-quoted string literal with character set specification> <an alternatively-q
I checked many examples but all are with hard coded values. In this I will know the values of parameters to PROC1 only at run time.
Is there any other way of doing this. I'm using Oracle 10g. Please advise.
Thanks. Pat

The replay answer
Advertisement
Works for me...
SQL>create or replace procedure p(p_par1 number, p_par2 number)
  2  as
  3  begin
  4    null;
  5  end;
  6  /
Procedure created.
SQL>declare
  2      p_par1 number;
  3      p_par2 number;
  4      L_JOBID INTEGER;
  5  begin
  6      DBMS_JOB.SUBMIT ( L_JOBID, ' BEGIN P ('''||P_PAR1||''','''||P_PAR2||'''); END;');
  7  end;
  8  /
PL/SQL procedure successfully completed.Message was edited by:
SamB

Go to See the other 4 answers

Submitting Job with parameterised procedure using DBMS_JOB

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I need to submit a job which makes a call to a procedure with parameters as shown below: PROCDURE SUB_JOB ( P_PAR1 VARCHAR2, P_PAR2 VARCHAR2) IS L_JOBID INTEGER; BEGIN DBMS_JOB.SUBMIT ( L_JOBID, ' BEGIN PROC1 ('''||P_PAR1||''','''||P_PAR2||''');[More]

Scheduling stored procedure using DBMS_JOB package

Category:DefaultRelease time:2015-10-11Views:130

I have stored procedure called “extract_every_day”, I want this procedure to run automatically at 7:00 PM every day. I used DBMS_JOB.SUBMIT to achieve this, below is pl/sql block DECLARE v_jobno number ; BEGIN DBMS_JOB.SUBMIT (v_jobno, ' extract_ever[More]

Start one job after another complets using PL/SQL procedure and DBMS_JOB

Category:DefaultRelease time:2015-10-11Views:130

All, I am attempting to refresh a materialized view using DBMS_JOB and having a PL/SQL program loop through each materialized view name that resides in a table I created. We do the table because they have to be refreshed in a specific order and I uti[More]

Submitting job from a procedure

Category:DefaultRelease time:2015-10-11Views:130

Hi, I need to submit a job and call it frm a procedure. I need to run the job exactly at 12 in the midnight. How will i pass the start_time to the job? the problem i face here is the procedure can be called on any date. following is the procedure CRE[More]

Submitting Oracle job via OCCI using dbms_job package

Category:DefaultRelease time:2015-10-11Views:130

I am using 10g client to connect to a 9i Database on Redhat Linux AS 3.0. I am trying to submit a job via OCCI. I get back a jobId, but don't see the job in the user_jobs table or the result of the job being executed. I am using occi::Statement in th[More]

Dynamic pages submitting data to stored procedure

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I am developing an Oracle Portal application and I am facing a problem, if anyone could help me I'd be very grateful. I have the latest version of Portal 3.0.9, installed on a linux server. I'm using I.E. 6.0 to interface with Portal. I have a dy[More]

Need package to call procedure in DBMS_JOB

Category:DefaultRelease time:-0001-11-30Views:130

I have created a procedure that i what a DBMS_JOB to call at a schduelled time, but i don't know what DBMS package i can use to cat my procedure called ATREPORT.AT_DIV, I dnt knw the value for the what parameter DBMS_JOB.SUBMIT (job => :jobnumber ,wh[More]

How to schedule procedure in DBMS_JOBS?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, i have one stored procedure .. i want to schedule that procedure to run every 15 of the month at 2AM... plz helpOracle recommends (from 10.1 onwards?) that you use DBMS_SCHEDULER instead of DBMS_JOB. DBMS_SCHEDULER provides a much richer set of f[More]

Submitting QLTTRAMB from SQL Procedure

Category:DefaultRelease time:-0001-11-30Views:130

Hi, Could someone suggest the methodology to submit the Collection Import Manager (QLTTRAMB) from a stored procedure using fnd_request.submit_request? I would like to know what manadatory params need to be supplied with data for the request submissio[More]

Whether dbms_job accept procedure with argument

Category:DefaultRelease time:-0001-11-30Views:130

I am a new Oracle User. Following is the code i am trying to make run. create or replace trigger pp after insert on pipe for each row v_job number; p number; begin p:=:new.id; dbms_job.submit(v_job, 'sample(p);', null); end; Here p is the inserted va[More]

Problem in submitting job through Pl/sql

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have a procedure with parameters as shown below. Pkg1.Proc1(indate date, innum1 number, innum2 number) I am trying to execute this procedure by submitting jobs through another procedure and passing the parameters dynamically. I found the jobs a[More]

Hot
Does Crystal Reports support the "polygon" datatype found in Oracle 10g? I need to be able to output the coordinates found in this datatype in plain text (or plain numbers as it where) on a report using Crystal Reports. Thanks, TCHi TC, I don't [More]
I am new to National Instruments hardware and Veristand and I'm trying to use an instrument with Veristand to see if I can get readings from that instrument. I am using a PC with Windows Vista and I'm connected through a network to a PXI 8108 control [More]
Hi, We are trying to deploy an EAR file (created through NWDS) using the SDM. But while deploying we are getting the following exception: com.sap.sdm.util.sduread.IllFormattedSduFileException:Error during attribute reading: received exception: Error [More]
I've been trying to work on my art in photoshop after getting a tablet for my birthday, but for some reason, Photoshop keeps crashing after about an hour or so of being in it. I suspect it's the driver, but I'm not entirely sure. I'm downloading the [More]
Hi Experts, I am trying to use treeByNesting table column in webdynpro for ABAP. Can anyone please share me the code. I am not getting how to use the recursive node. I need to develop a tree structure whose leaf if determined at run time, so thought [More]
I just set up a linksys wrt54G router to my earthlink dsl modem. After some wrangling with the linksys people, I got my eMac to work through the ethernet wired connection to the router. However, I can't get my Powerbook (1.5 GHz PowerPC G4) to work o [More]
I recently updated to 20 and my spellcheck context menu has vanished. What if you aren't even given the check spelling or languages menu option yet the broken red line under mispelled words occurs and no spelling suggestions made when right clicking [More]
Hi All,           There is an incoming Payment in which there are two invoices which are of value Rs 10000/- and Rs 20000/-. When we add the incoming payment JE effect is like this: Bank....................Dr (Rs 30000/-) Customer.................... [More]
Hello, just as the tittle says, I have yet to receive this. Also I am a Best Buy Elite Member, along with GCU, how do I access the 24/7 line for Elite members?   Thanks for any help!<script src="safari-extension://com.ebay.safari.myebaymanager-QYH [More]
Hello All, I was looking for an Idoc that allow me to create an SD invoice (and automatically then an FI posting) based on an SD order (as VF01). I found some consultants talking about INVOIC, others about ACC_BILLING. But after somes tests, none of [More]