error handling [message #311908] |
Mon, 07 April 2008 06:22  |
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 #311940 is a reply to message #311908] |
Mon, 07 April 2008 08:20   |
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 #311945 is a reply to message #311908] |
Mon, 07 April 2008 08:30   |
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 #311953 is a reply to message #311948] |
Mon, 07 April 2008 08:55   |
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   |
flyboy
Messages: 1903 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   |
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

thanks again
[Updated on: Mon, 07 April 2008 09:06] Report message to a moderator
|
|
|
|
Re: error handling [message #312040 is a reply to message #311908] |
Mon, 07 April 2008 20:09  |
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
|
|
|