Home » SQL & PL/SQL » SQL & PL/SQL » Oracle schedule job (Oracle 10.1.0.5)
Oracle schedule job [message #432776] Thu, 26 November 2009 04:28 Go to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Hi All,

I have created a oracle job through dbms_scheduler.create job and it works fine. This job invokes a procedure in 5 minutes interval. I want to trap any error (not exception) that is thrown from the procedure invoked from the job.

For e.g.
Proc1 is invoked from oracle schedule job.
Proc1 is updating a table table1.
If table1 is dropped by any chance (by execution of some other code) then proc1 will throw error 'Table or View does not exist', but no exception will occur.

Can I trap this error at run time?

Any help in this regard will be highly appreciated.

Thanks in advance,
Kaushik.
Re: Oracle schedule job [message #432779 is a reply to message #432776] Thu, 26 November 2009 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
'Table or View does not exist' is an exception by oracles definition of exceptions, so I'm not sure what you think the difference is between error and exception.

What do you want to do once you've trapped the exception?
Re: Oracle schedule job [message #432780 is a reply to message #432776] Thu, 26 November 2009 04:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is the difference between and Error and an Exception in your system?
Re: Oracle schedule job [message #432784 is a reply to message #432780] Thu, 26 November 2009 04:54 Go to previous messageGo to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
I want to get an exception from the below mentioned code.
But I am encountering a compilation error always. (As table xa is non-existent).

Can you trap this error in the exception block?


-- Created on 25/11/2009 by C-KMITRA
declare
-- Local variables here
i integer;
begin
select 1 into i from xa;
dbms_output.put_line(i);
exception

when others then
dbms_output.put_line(sqlerrm);
end;


[Updated on: Thu, 26 November 2009 04:55]

Report message to a moderator

Re: Oracle schedule job [message #432787 is a reply to message #432784] Thu, 26 November 2009 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you trap this error in the exception block?

No as it is a compilation error and exception block is executed when the block is executed that is after compilation and so a prerequisite is that the block compiles.

And remove the stupid "when others..." you have.

Regards
Michel
Re: Oracle schedule job [message #432792 is a reply to message #432787] Thu, 26 November 2009 05:46 Go to previous messageGo to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Michel,

Please consider a situation where the code is given in a procedure and it is compiled as table xa is existent.

Now this proc is invoked from a oracle scheduled job.

If the table is removed after some days and the scheduled job has invoked this procedure after that, then how will you trap this error?



Re: Oracle schedule job [message #432794 is a reply to message #432776] Thu, 26 November 2009 05:57 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Unless you're using dynamic SQL (and you really don't want to) you can't get this error at run time.

As soon as the table is dropped the procedure will be marked as invalid and when the job tries to run it you'll get a different error that tells you the procedure is invalid.
Re: Oracle schedule job [message #432796 is a reply to message #432792] Thu, 26 November 2009 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

When you drop the table the code becomes invalid and when you try to execute it, Oracle tries to recompile it and can't and so does not execute your code.

The only way is to put the whole code inside a string and execute it as dynamic PL/SQL. Then you can trap the compilation error (which is in this an execution error of the "execute immediate" statement).

Regards
Michel
Re: Oracle schedule job [message #432798 is a reply to message #432794] Thu, 26 November 2009 06:05 Go to previous messageGo to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Hi,

I have to sync a text index through the command ' ctx_ddl.sync_index' from this procedure. When the context index is dropped the procedure has not become invalid but it is throwing the error when it is trying to invoke the command.

Re: Oracle schedule job [message #432806 is a reply to message #432798] Thu, 26 November 2009 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have been given hints on how to do it.
Now you are the only one to have the actual code you execute, so apply the hint on your code.

Regards
Michel
Re: Oracle schedule job [message #432808 is a reply to message #432806] Thu, 26 November 2009 06:46 Go to previous messageGo to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Michel Cadot wrote on Thu, 26 November 2009 18:08
You have been given hints on how to do it.
Now you are the only one to have the actual code you execute, so apply the hint on your code.

Regards
Michel



Need a technical answer rather than a diplomatic one.....

If the only way out is to use dynamic sql, then pls let me know how this kind of error can be trapped there!
Re: Oracle schedule job [message #432809 is a reply to message #432808] Thu, 26 November 2009 06:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
If the only way out is to use dynamic sql, then pls let me know how this kind of error can be trapped there!


It can't be trapped in the Pl/Sql that is executed, but you can catch the exception that is raised when you execute the statement.

You need to understand the difference between a compile error and a run-time error.

A run-time error happens when you execute a piece of code, and one of the statements in the code performs an operation that raises an exception (such as dividing by zero).
These exceptions can be caught by exception handlers.

A compile error happens (generally) when a piece of code is parsed for syntactical correctness prior to being executed. If the piece of code is an anonymous pl/sql block, then these errors are hard to distinguish from run-time errors except in that they CANNOT be caught by exception handlers inside the code.

When you execute an anonymous pl/sql block using dynamic sql, runtime errors from the block can be caught by exception handlers inside the block of code, but compile errors will cause the Execute Immediate statement to return an exception, which must be caught at that level.
Re: Oracle schedule job [message #432810 is a reply to message #432806] Thu, 26 November 2009 06:56 Go to previous messageGo to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Now I got the solution.

Thanks every one for your time.

The following code has worked for me.



declare

i integer;
x varchar2(4000);
begin
x:='select 1 into i from xa';
execute immediate x;
dbms_output.put_line(i);
exception
when others then
dbms_output.put_line(sqlerrm);
end;

[Updated on: Thu, 26 November 2009 06:58]

Report message to a moderator

Re: Oracle schedule job [message #432815 is a reply to message #432810] Thu, 26 November 2009 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
when others then dbms_output.put_line(sqlerrm);

I will give a not diplomatic answer:
THIS IS STUPID.

Regards
Michel
Re: Oracle schedule job [message #432840 is a reply to message #432810] Thu, 26 November 2009 10:24 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Bottom-line: Do not drop that table!
Why would you want to do that in the first place?
Previous Topic: Trigger Help
Next Topic: Tuning order by clause
Goto Forum:
  


Current Time: Fri Dec 09 06:14:11 CST 2016

Total time taken to generate the page: 0.07889 seconds