Home » SQL & PL/SQL » SQL & PL/SQL » Run dbms_scheduler.create_job with arguments (Oracle 11.2.0.1.0)
Run dbms_scheduler.create_job with arguments [message #651249] Fri, 13 May 2016 09:54 Go to next message
dansrnt
Messages: 8
Registered: May 2016
Location: Florida,US
Junior Member
Has anyone used the dbms_scheduler.create_job with a procedure having parameters to it. Please help.
Re: Run dbms_scheduler.create_job with arguments [message #651250 is a reply to message #651249] Fri, 13 May 2016 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Many examples in the web.
You may start with the documentation.
If not sufficient, use Google.

[Updated on: Fri, 13 May 2016 10:14]

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #651251 is a reply to message #651249] Fri, 13 May 2016 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/search?q=dbms_scheduler.create_job+with+a+procedure+having+parametersurceid=chrome&ie=UTF-8
Re: Run dbms_scheduler.create_job with arguments [message #651252 is a reply to message #651250] Fri, 13 May 2016 10:01 Go to previous messageGo to next message
dansrnt
Messages: 8
Registered: May 2016
Location: Florida,US
Junior Member
Thank you Michel. I have successfully created a job using dbms_scheduler.create_program and DEFINE_PROGRAM_ARGUMENT but i don't see any good data dictionary views for them to view the status of the job run and also to know which parameters i passed to. It would be difficult to debug based on the requests. Please suggest.
Re: Run dbms_scheduler.create_job with arguments [message #651253 is a reply to message #651252] Fri, 13 May 2016 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's the list of USER views about scheduler.
VIEW_NAME
------------------------------
USER_SCHEDULER_CHAINS
USER_SCHEDULER_CHAIN_RULES
USER_SCHEDULER_CHAIN_STEPS
USER_SCHEDULER_CREDENTIALS
USER_SCHEDULER_DB_DESTS
USER_SCHEDULER_DESTS
USER_SCHEDULER_FILE_WATCHERS
USER_SCHEDULER_GROUPS
USER_SCHEDULER_GROUP_MEMBERS
USER_SCHEDULER_JOBS
USER_SCHEDULER_JOB_ARGS
USER_SCHEDULER_JOB_DESTS
USER_SCHEDULER_JOB_LOG
USER_SCHEDULER_JOB_RUN_DETAILS
USER_SCHEDULER_NOTIFICATIONS
USER_SCHEDULER_PROGRAMS
USER_SCHEDULER_PROGRAM_ARGS
USER_SCHEDULER_REMOTE_JOBSTATE
USER_SCHEDULER_RUNNING_CHAINS
USER_SCHEDULER_RUNNING_JOBS
USER_SCHEDULER_SCHEDULES

I think this one is the one you are searching for... for the next execution:
SQL> desc USER_SCHEDULER_JOB_ARGS
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 JOB_NAME                                  VARCHAR2(30)
 ARGUMENT_NAME                             VARCHAR2(30)
 ARGUMENT_POSITION                         NUMBER
 ARGUMENT_TYPE                             VARCHAR2(61)
 VALUE                                     VARCHAR2(4000)
 ANYDATA_VALUE                             SYS.ANYDATA
 OUT_ARGUMENT                              VARCHAR2(5)

But there is no view, as far I as I know, for what has been executed.

[Updated on: Fri, 13 May 2016 10:09]

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #651254 is a reply to message #651253] Fri, 13 May 2016 10:10 Go to previous messageGo to next message
dansrnt
Messages: 8
Registered: May 2016
Location: Florida,US
Junior Member
Can this be done without using create program ? And only with create job ?
Re: Run dbms_scheduler.create_job with arguments [message #651255 is a reply to message #651254] Fri, 13 May 2016 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know, what does the documentation say?

Re: Run dbms_scheduler.create_job with arguments [message #651257 is a reply to message #651255] Fri, 13 May 2016 11:37 Go to previous messageGo to next message
dansrnt
Messages: 8
Registered: May 2016
Location: Florida,US
Junior Member
I tried passing the parameters dynamically to the procedure using the dbms_scheduler.create_job and it worked fine. But my only concern is what if due to any reason, the job fails and it is difficult to find in the data dictionary tables which request got failed and for what parameters as it is not a scheduled program and i want to run only once per request ? Any suggestions?
Re: Run dbms_scheduler.create_job with arguments [message #651258 is a reply to message #651257] Fri, 13 May 2016 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the job itself record what you do in a log table: job start time, parameters, error it encounters, end time...

Re: Run dbms_scheduler.create_job with arguments [message #651539 is a reply to message #651258] Thu, 19 May 2016 20:35 Go to previous messageGo to next message
dansrnt
Messages: 8
Registered: May 2016
Location: Florida,US
Junior Member
For better debugging purpose, i am creating the Job names as a concatenation of Job and a reference number for identification.Thanks.
Re: Run dbms_scheduler.create_job with arguments [message #672521 is a reply to message #651539] Wed, 17 October 2018 06:57 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
Hi all,
I have created a job using following code. when i execute manually it works fine but does not execute automatically.
Please help..
CREATE TABLE RND1(A VARCHAR2(100));

BEGIN 
dbms_scheduler.create_job(
job_name=>'UNIT_RATE_FWRD_NXT_MNTH',
job_type=>'PLSQL_BLOCK', 
job_action=>'BEGIN insert into rnd1 values(''testing'');commit;  END;',
number_of_arguments=>0,
start_date=> '17-OCT-2018 04:30:00 PM',
repeat_interval=> 'trunc(systimestamp, ''MI'') + interval ''2'' minute',--'freq=menutely; interval=2; bysecond=0;',
end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', 
enabled=>TRUE, 
auto_drop=>TRUE,
comments=>'TEST'
);
END; 
/
Re: Run dbms_scheduler.create_job with arguments [message #672522 is a reply to message #672521] Wed, 17 October 2018 07:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
See a picture of my car that does not work correctly.
Tell me how to make my car go.
Above provides NO actionable detail.
How can we reproduce what you report?
Re: Run dbms_scheduler.create_job with arguments [message #672524 is a reply to message #672522] Wed, 17 October 2018 07:21 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have a look at user_scheduler_jobs - it'll show if it's running, when it next runs, how many times it's failed, etc.
Re: Run dbms_scheduler.create_job with arguments [message #672525 is a reply to message #672524] Wed, 17 October 2018 07:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>CREATE TABLE RND1(A VARCHAR2(100));
why does paramter "A" exist above?
Re: Run dbms_scheduler.create_job with arguments [message #672526 is a reply to message #672524] Wed, 17 October 2018 07:44 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
Yes,

I checked user_scheduler_jobs table, i found one record that has following values. But after 10 minutes i check RND1 table it is empty still.
JOB_NAME
UNIT_RATE_FWRD_NXT_MNTH

NEXT_RUN_DATE
17-OCT-18 04.41.54.925476000 PM ASIA/CALCUTTA

JOB_TYPE
PLSQL_BLOCK

SCHEDULE_TYPE
PLSQL

START_DATE
17-OCT-18 04.30.00.000000000 PM ASIA/CALCUTTA

REPEAT_INTERVAL
trunc(systimestamp, 'MI') + interval '2' minute

ENABLED
TRUE

STATE
SCHEDULED

JOB_PRIORITY
3

FAIL_ON_SCRIPT_ERROR
FALSE

[Updated on: Wed, 17 October 2018 07:46]

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672528 is a reply to message #672526] Wed, 17 October 2018 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
what do the run_count and failure_count columns show?

Does the insert work if you run it directly?
Re: Run dbms_scheduler.create_job with arguments [message #672529 is a reply to message #672526] Wed, 17 October 2018 08:02 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
What does USER_SCHEDULER_JOB_LOG show?
Re: Run dbms_scheduler.create_job with arguments [message #672531 is a reply to message #672529] Wed, 17 October 2018 08:15 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
Please see attached document.
  • Attachment: Book1.csv
    (Size: 6.09KB, Downloaded 1267 times)
Re: Run dbms_scheduler.create_job with arguments [message #672532 is a reply to message #672525] Wed, 17 October 2018 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Wed, 17 October 2018 13:35
>CREATE TABLE RND1(A VARCHAR2(100));
why does paramter "A" exist above?
that's the column name
Re: Run dbms_scheduler.create_job with arguments [message #672534 is a reply to message #672532] Wed, 17 October 2018 08:27 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
Yes, its column name.
cookiemonster wrote on Wed, 17 October 2018 08:23
BlackSwan wrote on Wed, 17 October 2018 13:35
>CREATE TABLE RND1(A VARCHAR2(100));
why does paramter "A" exist above?
that's the column name
Re: Run dbms_scheduler.create_job with arguments [message #672535 is a reply to message #672532] Wed, 17 October 2018 08:29 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It works just fine in my DB.
What's your job_queue_processes parameter set to?
Re: Run dbms_scheduler.create_job with arguments [message #672537 is a reply to message #672534] Wed, 17 October 2018 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
shawaj wrote on Wed, 17 October 2018 06:27
Yes, its column name.
cookiemonster wrote on Wed, 17 October 2018 08:23
BlackSwan wrote on Wed, 17 October 2018 13:35
>CREATE TABLE RND1(A VARCHAR2(100));
why does paramter "A" exist above?
that's the column name
OK, but it is never used or referenced within the BEGIN ... END: block.
Did you start a new session to inspect the table content?
Oracle provides a read consistent view of data as existed when current "transaction" started
Re: Run dbms_scheduler.create_job with arguments [message #672539 is a reply to message #672535] Wed, 17 October 2018 08:40 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
show parameter JOB_QUEUE_PROCESSES;

NAME TYPE VALUE
------------------- ------- -----
job_queue_processes integer 0
Re: Run dbms_scheduler.create_job with arguments [message #672541 is a reply to message #672539] Wed, 17 October 2018 08:42 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's why it's not working - with that set to 0 no jobs can run.
Up it.
Re: Run dbms_scheduler.create_job with arguments [message #672543 is a reply to message #672541] Wed, 17 October 2018 08:48 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
Thank you to all. now its working.
Re: Run dbms_scheduler.create_job with arguments [message #672544 is a reply to message #672537] Wed, 17 October 2018 08:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Wed, 17 October 2018 14:35
shawaj wrote on Wed, 17 October 2018 06:27
Yes, its column name.
cookiemonster wrote on Wed, 17 October 2018 08:23
BlackSwan wrote on Wed, 17 October 2018 13:35
>CREATE TABLE RND1(A VARCHAR2(100));
why does paramter "A" exist above?
that's the column name
OK, but it is never used or referenced within the BEGIN ... END: block.
And it doesn't need to be - it's implicit. I would always say the columns should be specified but there's no reason why that wouldn't work.
BlackSwan wrote on Wed, 17 October 2018 14:35

Did you start a new session to inspect the table content?
Oracle provides a read consistent view of data as existed when current "transaction" started
Think you might need some coffee - oracle only does that when you set the isolation level to serializable.
Re: Run dbms_scheduler.create_job with arguments [message #672578 is a reply to message #651253] Thu, 18 October 2018 13:43 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Michel Cadot wrote on Fri, 13 May 2016 11:06

Here's the list of USER views about scheduler.
VIEW_NAME
------------------------------
USER_SCHEDULER_CHAINS
USER_SCHEDULER_CHAIN_RULES
USER_SCHEDULER_CHAIN_STEPS
USER_SCHEDULER_CREDENTIALS
USER_SCHEDULER_DB_DESTS
USER_SCHEDULER_DESTS
USER_SCHEDULER_FILE_WATCHERS
USER_SCHEDULER_GROUPS
USER_SCHEDULER_GROUP_MEMBERS
USER_SCHEDULER_JOBS
USER_SCHEDULER_JOB_ARGS
USER_SCHEDULER_JOB_DESTS
USER_SCHEDULER_JOB_LOG
USER_SCHEDULER_JOB_RUN_DETAILS
USER_SCHEDULER_NOTIFICATIONS
USER_SCHEDULER_PROGRAMS
USER_SCHEDULER_PROGRAM_ARGS
USER_SCHEDULER_REMOTE_JOBSTATE
USER_SCHEDULER_RUNNING_CHAINS
USER_SCHEDULER_RUNNING_JOBS
USER_SCHEDULER_SCHEDULES
I think this one is the one you are searching for... for the next execution:
SQL> desc USER_SCHEDULER_JOB_ARGS
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 JOB_NAME                                  VARCHAR2(30)
 ARGUMENT_NAME                             VARCHAR2(30)
 ARGUMENT_POSITION                         NUMBER
 ARGUMENT_TYPE                             VARCHAR2(61)
 VALUE                                     VARCHAR2(4000)
 ANYDATA_VALUE                             SYS.ANYDATA
 OUT_ARGUMENT                              VARCHAR2(5)
But there is no view, as far I as I know, for what has been executed.
see the view USER_SCHEDULER_JOB_LOG. detailed information is in USER_SCHEDULAR_JOB_RUN_DETAILS

[Updated on: Thu, 18 October 2018 13:45]

Report message to a moderator

Previous Topic: Written Assignment-Printing a Record Using an If Statement
Next Topic: STORE SQL PROCEDURES
Goto Forum:
  


Current Time: Thu Mar 28 11:41:27 CDT 2024