Re: Trapping Errors in SQL*Plus

For much of the application this approach would be fine. However there are several places where we do a 'create table ... as select ...' (for performance reasons) and therefore can't use PL/SQL.

There is also a section of DDL at the beginning of our process where tables are created with quite large initial extents. These tables have sometimes failed to create (due to lack of space) resulting in long, wasted runs. It would be great to have a simple, generic way of trapping such errors. Unfortunately Oracle don't hold out much hope (they say this functionality has never been provided and is never likely to be!).

It looks rather like we'll have to do our own process specific checking (e.g. making sure a table we've just tried to create exists in user_tables etc.).

Regards,

Mark Sims

Michel Cadot wrote:

> I know you don't want to use PL/SQL, but if you want to trap
> the reason of the failure you can enclose each statement in
> an anonymous PL/SQL block:
>
> whenever sqlerror exit [rollback|commit];
>
> begin
> <your first statement>
> exception
> when others then
> <what you want, for instance dbms_output.put_line(sqlerrm);>
> raise;
> end;
> /
> ...
> and then for the others statements.
>
Received on Mon Nov 08 1999 - 10:42:28 CST