CODEWITHSUNDEEP
oracle-databaset-sqlplsql
S. Guillaume
S. Guillaume

Reputation: 67

Error manage in pl/sql

i would like to convert a script t-SQL into pl/sql. But i don't find how to manage error like in t-sql : 

exec (bulk_cmd);
if @@error <> 0 goto lbl_end;

I found this : i've to create exception variable before make the exec. And do something like that : 

        When NO_DATA_FOUND Then
            /* no value found*/…
        When TIMEOUT_ON_RESOURCE Then
            /* time out */…
        When ZERO_DIVIDE Then
            /* divide by zero*/…
        When Others Then
           /* others errors */…

It's really heavy... Andi know in T-SQL @@error is reserve to catch the error (if it's exist) of the last instruction (here the exec). So i ask you if there is a method like this in pl/sql

Thank's !

Upvotes: 0

Views: 253

Answers (2)

Anton Zaviriukhin
Anton Zaviriukhin

Reputation: 741

You found everything you need. Just use either anonymous block BEGIN...END;

BEGIN
   bulk_cmd;
EXCEPTION
        When NO_DATA_FOUND Then
            /* no value found*/…
        When TIMEOUT_ON_RESOURCE Then
            /* time out */…
        When ZERO_DIVIDE Then
            /* divide by zero*/…
        When Others Then
           /* others errors */…   
END;

Or PROCEDURE/FUNCTION

CREATE OR REPLACE PROCEDURE my_proc as
BEGIN
   bulk_cmd;
EXCEPTION
        When NO_DATA_FOUND Then
            /* no value found*/…
        When TIMEOUT_ON_RESOURCE Then
            /* time out */…
        When ZERO_DIVIDE Then
            /* divide by zero*/…
        When Others Then
END;

Note that if you rise exception from handling block, it will "eat" your backtrace (backtrace will start not from place where exception occured but from rise function). So it is a good practice don't catch exception that you don't need, and log exception that you catch with backtrace.

Upvotes: 1

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

set serveroutput on;
declare 
 v number;
begin 

  begin 
    v := 1/0;
  exception when others then 
    dbms_output.put_line('I catch exception;');
    goto my_label;
  end;
  dbms_output.put_line('Something here');
  <<my_label>>
  dbms_output.put_line('After label');
end;

Upvotes: 0

Related Questions