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
When I try to delete this message the website I am trying to reach closes and I revert back to my home pageSee: *http://kb.mozillazine.org/Firefox_crashes *https://support.mozilla.org/kb/Firefox+crashesRead other 2 answers [More]
Hello, When I open a pdf created outside photoshop, (this is a technical image created on engineering software) I get the "open" dialog box with various settings. Under "Image size / Resolution" the default setting always appears to be [More]
Hi all, DB- ORACLE 9.2.0.4 I am facing problem in one query it is taking more than one hour, so i have traced the query and aslo after 15 min i ahve to cancel the query.... select  MRH_MRN,DECODE(MRH_SEX,'M','MALE','FEMALE') AS SEX, trim((mrh_sur_nam [More]
This is ridiculous...I'm reading the help page and it's been absolutely no help whatsoever.From the iTunes help page searching for "Convert". " Saving a copy of a song in a new file format You can convert a song to a different file format ( [More]
Hello, I'm coverting my code from AS2 to AS3. I have public variables I've declared on my main ".as" that I've always used in my custom classes by saying _root.varname. Of course _root is gone, so I was wondering if the code I have below is the [More]
I am having trouble with my ipod it doesn't want to add any type of my videos a message keeps popping up saying the videos cannot be played on this ipod when i used to have videos on it before but all of the sudden it dosent want to now my computer r [More]
I have a program which has a GUI control that I designed. It consists of several check boxes inside a panel, the user can tick one or more of them. There are various methods to get all the selections, untick boxes in code etc. In one place in my soft [More]
I want to change this VB Script into Java for use in Dreamweaver <% If rsCalendarDetails("images") <> "" Then %> <img src="images/calendarphotos<%=(rsCalendarDetails.Fields.Item("images").Value)%>&qu [More]
Hi Experts, The follow problem  came in log file of SM35 ,while SO creation through BDC for some sales org.   Runtime error COMPUTE_BCD_OVERFLOW has occurred. could you please tell me what is problem. Thanks jaiHi It could be the following reasons, 1 [More]
Configuration: OC4J 9.0.2... Description: * My web application ceases to function after a while and throws a "Transaction expired (Committed)" exception * The following exception is logged (application.log): Error rolling back transaction java.l [More]