dbms_job question

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

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,

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?

>>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'') );'

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

select last_date, next_date, broken, failures from user_jobs;



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

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.

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.

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 .

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

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.

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.

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?

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.

>>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.

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?

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 #.

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?