dbms_job question

sam15
sam15 used Ask the Experts™
on
does anyone know how you should schedule a job fo a proceduer that uses two variables. It is catching the variable values being passed.

procedure manage_order
(p_a   in varchar2 default null,
p_b   in varchar2 default null)
begin
dbsm_job.submit(job=>i,
what=>'process_order(''||p_a||','||p_b);'
next_date=>sysdate,
interval=>null);
I tried diff combination of the quotes and still getting an error.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
Commented:
>>what=>'process_order(''||p_a||','||p_b);'

Try:

what=>'process_order('''||p_a||''','''||p_b||'''');'
Top Expert 2009

Commented:
A quote inside 2 quotes must be escaped with a quite so to quote a  single quote by itself would be


''''

Start quote, escape quote, actual quote, end quote

Author

Commented:
This one worked - 3 single quotes at the end.

what=>'process_order('''||p_a||''','''||p_b||''');'

I still do ntio understand the logic of 3 single quotes. is not the same as one single quote or you are trying to insert a quote witihin a quoted string.

One small issue is the P_B is actually a date "01-JUL-09". it is getting passed as a string into the procedure and job.

I tried to do
to_char(to_date(p_b)) ----> and it is giving me 01-JUL-0009 instead od 01-JUlL-2009.

How can you specify the century as 2000.

thanks,
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
another strange thing that i cant explain is that i submitted the job about 10 minutes ago and still has not run. should not it start immediately?
Top Expert 2009

Commented:
>>tried to do to_char(to_date(p_b)) ----> and it is giving me 01-JUL-0009 instead od 01-JUlL-2009.

Try:

to_date(p_b, 'dd-mon-yyyy')

So in escape sense you could do:


what=>'process_order('''||p_a||''', to_date('''||p_b||''', ''dd-mon-yyyy'') );'

Author

Commented:
everything works now expcept job does not run. any ideas why.

it is still sitting in USER_JOBS. I thought it would run immediately and send an email to user after 5 minutes. Is there a way to ensure it runs immediately.

Munther
Top Expert 2009

Commented:
select last_date, next_date, broken, failures from user_jobs;



See if it is broken or has failures. Check the alert log too.

Author

Commented:
It is not broke or have failures. I do not think i have access to alert log on server. is that on oeprating system. You think it is a bug in oacle 9i.

shall i do exec dbms_job.run(job_id) in the code to force it?

SQL> select last_date,next_Date,broken,failures from user_jobs
  2  ;

LAST_DATE            NEXT_DATE            B   FAILURES
-------------------- -------------------- - ----------
                     17-jul-2009 13:48:50 N

1 row selected.

SQL> select sysdate from dual;

SYSDATE
--------------------
17-jul-2009 14:00:14

1 row selected.
Top Expert 2009

Commented:
Add a statement to the plsql that does an insert into a test log table to verify it is running. You might have some exception happening.

Author

Commented:
I already have a log table in the process that gets called and there is nothing there.
but if it hit an expcetption would not the user_jobs say it ran .

Author

Commented:
I tried to run it manually and here is the log

SQL> exec dbms_job.run(510);
BEGIN dbms_job.run(510); END;

*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1

Author

Commented:
SQL> exec dbms_job.run(510);
BEGIN dbms_job.run(510); END;

*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1


SQL> execute print_table('select * from user_jobs');
JOB                           : 510
LOG_USER                      : SADMIN
PRIV_USER                     : SADMIN
SCHEMA_USER                   : SADMIN
LAST_DATE                     : 17-jul-2009 14:22:17
LAST_SEC                      : 14:22:17
THIS_DATE                     :
THIS_SEC                      :
NEXT_DATE                     : 17-jul-2009 14:26:46
NEXT_SEC                      : 14:26:46
TOTAL_TIME                    : 29
BROKEN                        : N
INTERVAL                      : null
FAILURES                      : 2
WHAT                          : Maint_II.Run_process('admin','01-JUL-2009');
NLS_ENV                       : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA'
MISC_ENV                      : 0102000000000000
INSTANCE                      : 0
-----------------

PL/SQL procedure successfully completed.

Author

Commented:
I did find a pl/sql error when i tried to execute the procedure manually.
I do not understand why the job did not fire and failed.

I also do not understand why oracle does not run jobs immediately. There is no way to tell the user he will get his result in 5 minutes since you have no idea when oracle will run it.


Top Expert 2009

Commented:
Hmm possibly the db is not configured properly.


show parameter job_queue_processes


I recall older 9i releases had bugs related to the job scheduler. What version exactly?

Author

Commented:
9iR2 - 9.2.0.4
You are right about the job queue processes. I think it that is set to ZERO it will never run.
Server is down now. I will verify later.

Does it run immedately on your version?

Does it make sense to add "exec dbms_job.run(l_job);" in the same procedure that submits it.
Top Expert 2009

Commented:
>>exec dbms_job.run(l_job);

Hmm. I guess it does not hurt, that is up to you. If it runs once for you and it works it cannot hurt since the submit is only a one time thing anyway.

Author

Commented:
so in theory the job scheduler should not delay any job runs. if i submit using SYSDATE it should start right away. is that correct?
Top Expert 2009

Commented:
right. if it works correctly it will run. 9i did have a bug too so if you correct job_queue_processes and it still fails check metalink for the bug #.

Author

Commented:
i found that job_queue_processes was set to 0.
I changed it to 5 and all jobs ran.

Now i scheduled a new job that simply inserts a record into TEMP table and still is in the queue for 20 minutes. is that due to the bug? would it hurt to use dbms_job.run(l_job) in any way?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial