Home » SQL & PL/SQL » SQL & PL/SQL » error handling (64bit linux, oracle 10.2.0.1.0)
error handling [message #311908] Mon, 07 April 2008 06:22 Go to next message
fozerol
Messages: 17
Registered: March 2007
Junior Member
i have a simple script to transfer data from production to dataware house and table specific procedures like transfer_orders, transfer_client etc.

below is the transfer all procedure

begin
transfer_orders;
transfer_clients;
...
...
...
..
end;
if any of the transfer procedures become invalid transfer all procedures give statement or syntax error or whatever you call it when parsing,

i want to handle this error with exception handling to email that there is an error
(exception
when others than
etc ...)

any suggestion

thanks alot.

Re: error handling [message #311930 is a reply to message #311908] Mon, 07 April 2008 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So do it.

Regards
Michel
Re: error handling [message #311940 is a reply to message #311908] Mon, 07 April 2008 08:20 Go to previous messageGo to next message
fozerol
Messages: 17
Registered: March 2007
Junior Member
sorry for my bad explaination

error raises parsing phase (it is parsing error) so there is no way to error handling at exception block
is there any other way to do this


Re: error handling [message #311942 is a reply to message #311940] Mon, 07 April 2008 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix the error.
If it is a syntax error then it is easy to fix.

Regards
Michel
Re: error handling [message #311943 is a reply to message #311908] Mon, 07 April 2008 08:24 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>error raises parsing phase (it is parsing error)
error? what error? I don't see any error.
Re: error handling [message #311945 is a reply to message #311908] Mon, 07 April 2008 08:30 Go to previous messageGo to next message
fozerol
Messages: 17
Registered: March 2007
Junior Member
if one of the table structure changes for instance orders table,
hence transfer_orders procedure becomes invalid and so transfer_all procedure becomes invalid this caues parsing error;

ORA-XXXXX --> there is no such procedure transfer_orders

is it enough clear ?



michel yes, it is very easy but i want to know immediately wheather any error raised or not so if i can handle this error i can send sms to my mobil phone, if one of the software developer changes table structure, dataware house becomes out of date

[Updated on: Mon, 07 April 2008 08:35]

Report message to a moderator

Re: error handling [message #311948 is a reply to message #311945] Mon, 07 April 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you are saying is that you changes the table without changing the dependent objects and you put that in production?
Otherwise I don't see any problem to fix the code.

Regards
Michel
Re: error handling [message #311953 is a reply to message #311948] Mon, 07 April 2008 08:55 Go to previous messageGo to next message
fozerol
Messages: 17
Registered: March 2007
Junior Member
no, software developers changes table structure on production without changing its dependencies, at dataware house
anyway
i have overcome the problem by calling transfer procedures with

execute immeditate 'transfer_orders';

thanks for your help
Re: error handling [message #311955 is a reply to message #311908] Mon, 07 April 2008 08:58 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I am afraid he is changing database tables on the fly in the application.

If so, the best way is stop doing this. Data model (table structures) shall not be "dynamic"; it is almost always sign of bad design. The only dynamic thing in database is data.
When maintaining the application, you may need to change the table structure. You shall do it when the application (script) is not running and re-validate all objects after maintenance is over.

Yes, you may call all DML on changed tables using dynamic SQL, but this is just making the code almost unmaintable.
Also 11g should invalidate the code less (eg. not after adding column to a table).
Re: error handling [message #311957 is a reply to message #311955] Mon, 07 April 2008 09:04 Go to previous messageGo to next message
fozerol
Messages: 17
Registered: March 2007
Junior Member
i have exactly understand what you mean and i agree with you, but works not go on in my company like this, there is not any software requirement here as you know,
requirements changes by out bosses here day by day, week by week, month by month
Very Happy

thanks again

[Updated on: Mon, 07 April 2008 09:06]

Report message to a moderator

Re: error handling [message #312034 is a reply to message #311908] Mon, 07 April 2008 19:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You may be able to do what you want if you are willing to use packages.

The following test case, if correct, shows us something interesting about how oracle works. One explanation might be that Oracle does not check to see if all hard dependencies are valid before it begins running. This would mean that Oracle does not compile invalid referenced code until it is actually called, when packages are used, but instead attempts to recompile on the fly if it finds an invalid body during execution. This in turn means it is possible to trap the error as a standard pl/sql exception.

Is there a real expert out there that knows for sure?

Here is the code sample. Run it to see what I am talking about.

create or replace package p1 is procedure p1; end;
/
create or replace package p2 is procedure p2; end;
/
create or replace package p3 is procedure p3; end;
/

create or replace package body p1 is procedure p1 is begin null; end; end;
/
create or replace package body p2 is procedure p2 is begin null; end; end;
/
create or replace package body p3 is procedure p3 is begin null; end; end;
/

begin
   p1.p1;
   p2.p2;
   p3.p3;
end;
/

create or replace package body p2 is procedure p2 is begin end; end;
/

begin
   p1.p1;
   p2.p2;
   p3.p3;
end;
/

begin
   p1.p1;
   begin
      p2.p2;
   exception when others then null;
   end;
   p3.p3;
end;
/

However, there just seems something wrong about trying to trap for code compilation errors unless you are writing some kind of code management program.

good luck, Kevin
Re: error handling [message #312040 is a reply to message #311908] Mon, 07 April 2008 20:09 Go to previous message
bibsdash
Messages: 47
Registered: April 2008
Member
Here is what you should do:
begin
-- main begin
begin
transfer_orders;
exception
when others then
-- show sqlerrcode and sqlerrmessage
end;
begin
transfer_clients;
exception
when others then
-- show sqlerrcode and sqlerrmessage
end;
exception
-- for main begin
when others then
-- show sqlerrcode and sqlerrmessage
end;

[Updated on: Mon, 07 April 2008 23:01]

Report message to a moderator

Previous Topic: UTL_HTTP and TLS support
Next Topic: Sql Query
Goto Forum:
  


Current Time: Mon Dec 05 04:45:25 CST 2016

Total time taken to generate the page: 0.09913 seconds