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
Hello, I have been trying to install Windows 7 on my macbook pro (mid 2009, MBP53). I have successfully created a bootcamp partition using the assistant. When the computer tries to boot from the windows 7 install dvd, the screen is just black. The dv [More]
i have ceated on screen prgram... screen contains one text field of leth 40(type c)... if user enters some text in that and enters save button it will store in dbtable which is created by me(ztable)... but that text storing in big letters...even i en [More]
Forgive me if this has already been covered as i couldn't find any clear answers to do this with a lone ASA 5505 running sec plus.  How would i go about adding a second /28 CIDR range on anther segment given to us by our ISP which is on another segme [More]
Logic Pro 7 is really fantastic! Tommy BananaYeah sorry. I felt a bit square not having an alias and found out that you couldn't change so I had to make a new apple id. Having said that I do also think this user forum is fantastic too. BTW I use Logi [More]
Hello I am trying to implement Row Level security for some 10,000 users based on the Cost Centers which are about 30,000. I know how to implement the data level security using groups an dapplication roles and creating security filters on those groups [More]
Hi Gurus, I need to know the configuration nodes used for Development Plans in Talent management.I created the development form in Talent Assessment -->Define Forms for Talent Assessment. & assigned a scale to it. Now when I log in with the TMS id [More]
i have a textfield which contain accept string values. i need to validate the textfield to accept only numbers. how can i check for characters and symbols? I have search for documentation but can't find any appropriate methods. Please help.hello, all [More]
I have an older computor that is still running Windows ME. Is there a firefox that will load and run on my computor?The last version that worked on Windows ME was Firefox 2.0.0.20, you can get the English (US) version from https://ftp.mozilla.org/pub [More]
Is there a way, to change the configuration? Details: Host target was added manually, agent was deployed successfully, the host name is an IP address, dns is not working here. Now the IP address was changed, I can not add a new host using the new add [More]
I have as Muvoo N200 and i have beent rying to fix it for months . i got an email from the support ftaff out of europe on a new way to install the firware so i did and as it was downloading on my pc i got the windows operating system has occured the [More]