Scheduling 2

[uparna]

Hi ,
i have to run a script daily at 6:30 in the morning. What i have to do is this:
at the sql prompt i give the following command:

@d:\oracle\ora81\bin\loader\update_project_details

Is it possible to schedule this activity ? If so , how? I would be blessed if someone could provide the necessary command for this.



Thanks in advance


Regards,
Jayaram.
oracguru@yahoo.com

 

[sem]

You may schedule this task with you OS scheduler, at on NT or cron on Unix, or run it by dbms_job in case it doesn't spool or produce any other sql*plus specific activity.

 

[uparna]

Thanks,
i need to use the dbms_job option ,
but am not able to get the right commands. could you please guide me ?

Thanks again,
Jayaram.

 

[sem]

Just describe dbms_job with sql*plus, the names and types are quite informative.
dbms_job is Oracle's package, so you may also find its full description in manuals or view its specification from all_source in sql*plus or in any other programming tool you use.

Briefly, you have to SUBMIT your job

submit( 
job OUT BINARY_INTEGER, -- out parameter to get job number
what IN  VARCHAR2, -- your code to run
next_date IN  DATE DEFAULT sysdate, -- first date to run
interval IN  VARCHAR2 DEFAULT 'null',--pattern for next time
no_parse  IN  BOOLEAN DEFAULT FALSE,--to reparce each time?
instance  IN  BINARY_INTEGER DEFAULT any_instance, --for OPS
force     IN  BOOLEAN DEFAULT FALSE --for OPS
);

Also check your job_queue_processes and job_queue_interval database parameters.

 

[uparna]

I have tried using the dbms_job.submit function , but it keeps on giving me errors. I am not able to give a suitable job number , and it pops up several other messages. would be heavily indebted to you if you can give the command that i have to use.


Thanks in anticipation,
Jayaram.

 

[sem]

You mustn't pass any value, but rahter variable. If you run this command from sql*plus, declare some bind varable and then pass it as a parameter:

var mjob number
exec dbms_job.submitmjob,...

You may print mjob value:

print mjob

So you've got a handler to your job to manage it later.

If you're still getting errors, please quote them in your message.

 

[uparna]

I am still getting errors. here is a sample :

SQL> var mjob number;
SQL> exec dbms_job.submitmjob,'@status',sysdate,sysdate+1,false,'ORA817',true);
BEGIN dbms_job.submitmjob,'@status',sysdate,sysdate+1,false,'ORA817',true); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1



Help !
By the above script , i am trying to run a sql called status in the bin directory.


Thanks in advance,
Jayaram.

 

[sem]

1. WHAT parameter means your PL/SQL CODE, not the name of your script. To avoid extra parsing you may create some stored procedure and call it as

what=>'begin your_procedure; end;'

2. INTERVAL is a pattern, so it must be quoted 'SYSDATE+1'

3. INSTANCE means the ID of your instance within Oracle Parallel Server, it can not be 'ORA817'. If you do not use OPS or if it does not matter on which instance your job runs ommit this parameter as well as the next one.

 

[uparna]

Hi ,
I have tried what you said , but it is still not working......... i guess i know where the problem lies. At the end of the message , i have shown the screen shot of the sql prompt. when i "print mjob" , it is showing a null character. why is this ? If this means that mjob is null , naturally , an ORA-06502 will pop up as we are trying to give a null job number . Am i right ?
Here is the screen shot :

SQL> var mjob number
SQL> exec dbms_job.submitmjob,'execute spest',sysdate,'SYSDATE+1',false,'ORA817',true);
BEGIN dbms_job.submitmjob,'execute spest',sysdate,'SYSDATE+1',false,'ORA817',true); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1


SQL> print mjob

MJOB
----------




Thanks for your patience ! I s-)

Regards,
Jayaram.

 

[carp]

I think part of the problem may be you are trying to run a script from within the database. My understanding has always been that dbms_job will execute a stored procedure for you. You might try putting whatever your script is doing into a procedure and try again.

 

[sem]

Once again, INSTANCE is a numeric identifier of your instance within OPS, not the SID. Try to follow recomendations more carefully. Pass 1 instead of 'ORA817' or don't pass it at all.
As for WHAT parameter, 'execute spest' is not a valid pl/sql command, it's rather sql*plus shortcut for 'begin sptest; end;'. If spest is your stored procedure your WHAT parameter should be 'begin sptest; end;'. If sptest is still you sql script stored somewhere else, create a stored procedure and follow the above.

 

[uparna]

Hi Sem,
You were right !
I did the things that you mentioned and was able to schedule the test job perfectly !

But the main thing remains unsolved. The point here is that the script that i am trying to run is very very complex , it cannot be boiled down to a simple procedure as it makes internal references to other scripts which inturn call other sqls..........i know that this sounds terrible, but this is what happens. Is there anyway through which i can make the dbms_job accept scripts ?

Thanks and Regards,
Jayaram.
|-0

 

[sem]

No, but you still may schedule running your sql*plus script by external (OS) scheduler. As you use backslash as a delimiter, you're probably on some Windows platform. If you're on NT/2000/(not shure ofr 95/98), you may use at:

at 00:00 /every:M,T,W,Th,F,S,Su "sqlplus user/password@db @d:\oracle\ora81\bin\loader\update_project_details"

This will run your script every midnight.

BTW you may add d:\oracle\ora81\bin\loader\ to sql_path variable to call your script by short name.

 

[uparna]

Thanks Sem ,
I guess that is only way left now !

Thanks a lot for the constant support |-0



Regards,
Jayaram.