OraFAQ Forum: SQL & PL/SQL » Run dbms_scheduler.create

Home » SQL & PL/SQL » SQL & PL/SQL » Run dbms_scheduler.create_job with arguments (Oracle 11.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Run dbms_scheduler.create_job with arguments [message #651249]

Fri, 13 May 2016 09:54

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.

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #651250 is a reply to message #651249]

Fri, 13 May 2016 09:57

Michel Cadot
Messages: 68447
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

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

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #651252 is a reply to message #651250]

Fri, 13 May 2016 10:01

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.

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #651253 is a reply to message #651252]

Fri, 13 May 2016 10:06

Michel Cadot
Messages: 68447
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

dansrnt
Messages: 8
Registered: May 2016
Location: Florida,US

Junior Member

Can this be done without using create program ? And only with create job ?

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #651255 is a reply to message #651254]

Fri, 13 May 2016 10:12

Michel Cadot
Messages: 68447
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #651257 is a reply to message #651255]

Fri, 13 May 2016 11:37

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?

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #651258 is a reply to message #651257]

Fri, 13 May 2016 11:43

Michel Cadot
Messages: 68447
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...

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #651539 is a reply to message #651258]

Thu, 19 May 2016 20:35

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.

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672521 is a reply to message #651539]

Wed, 17 October 2018 06:57

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; 
/

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672522 is a reply to message #672521]

Wed, 17 October 2018 07:11

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?

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672524 is a reply to message #672522]

Wed, 17 October 2018 07:21

cookiemonster
Messages: 13904
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.

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672525 is a reply to message #672524]

Wed, 17 October 2018 07:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>CREATE TABLE RND1(A VARCHAR2(100));
why does paramter "A" exist above?

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672526 is a reply to message #672524]

Wed, 17 October 2018 07:44

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

cookiemonster
Messages: 13904
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?

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672529 is a reply to message #672526]

Wed, 17 October 2018 08:02

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

What does USER_SCHEDULER_JOB_LOG show?

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672531 is a reply to message #672529]

Wed, 17 October 2018 08:15

shawaj
Messages: 89
Registered: January 2016

Member

Please see attached document.

Attachment: Book1.csv
(Size: 6.09KB, Downloaded 433 times)

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672532 is a reply to message #672525]

Wed, 17 October 2018 08:23

cookiemonster
Messages: 13904
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

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672534 is a reply to message #672532]

Wed, 17 October 2018 08:27

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

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672535 is a reply to message #672532]

Wed, 17 October 2018 08:29

cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester

Senior Member

It works just fine in my DB.
What's your job_queue_processes parameter set to?

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672537 is a reply to message #672534]

Wed, 17 October 2018 08:35

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

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672539 is a reply to message #672535]

Wed, 17 October 2018 08:40

shawaj
Messages: 89
Registered: January 2016

Member

show parameter JOB_QUEUE_PROCESSES;

NAME TYPE VALUE
------------------- ------- -----
job_queue_processes integer 0

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672541 is a reply to message #672539]

Wed, 17 October 2018 08:42

cookiemonster
Messages: 13904
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.

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672543 is a reply to message #672541]

Wed, 17 October 2018 08:48

shawaj
Messages: 89
Registered: January 2016

Member

Thank you to all. now its working.

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672544 is a reply to message #672537]

Wed, 17 October 2018 08:49

cookiemonster
Messages: 13904
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.

Report message to a moderator

Re: Run dbms_scheduler.create_job with arguments [message #672578 is a reply to message #651253]

Thu, 18 October 2018 13:43

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 28 17:15:33 CDT 2023