CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Data-Base
Data-Base

Reputation: 8608

PL/SQL check SQL excution if it went OK or not?

I have a few SQL (Select/Update/Insert) syntax that I will run inside PL/SQL one after another

is there any way to check if each syntax completed correctly and if there is some error it will not halt the whole PL/SQL, it will just return "OK" or "Not OK" to a variable so I can use it with IF?

UPDATE

I came up with this function, but it dose not seems to work, it returns 0 all time!

create or replace
FUNCTION EXECUTE_SQL(
      V_SQL IN VARCHAR2 )
    RETURN NUMBER
  AS
    V_RESULTS NUMBER := 1;
  BEGIN
    BEGIN
      EXECUTE IMMEDIATE V_SQL;
    EXCEPTION
    WHEN OTHERS THEN
      -- the following line is just for debugging!
      dbms_output.put_line(SQLERRM);
      V_RESULTS:= 0;
    END;
    RETURN V_RESULTS;
  END EXECUTE_SQL;

what is wrong wit it (if any)! cheers

Upvotes: 1

Views: 2094

Answers (3)

cyborg007
cyborg007

Reputation: 123

Write show errors

begin 
           update t set x = ... 
 exception 
    when DUP_VAL_ON_INDEX 
         -- primary key or unique key violation 
    when OTHERS 
         -- other kind of exception 
  end; 
/
show errors

It will show errors if any.

Upvotes: 0

René Nyffenegger
René Nyffenegger

Reputation: 40499

if sql%rowcount > 0 then
   -- insert or update statement affected sql%rowcount rows
end if;

As for the correct syntax: if the syntax is wrong, it won't even compile. If there's a data consistency error (such as divide by 0 error, or primary key violation) an exception will be thrown. Such exception can be caught in exception handlers

In the exception handler, you can then check sqlerrm for more details:

 begin
    update t set x = ...
 exception when others then
    dbms_output.put_line(SQLERRM);
 end;

There are also a few predefined exceptions that you can check on:

 begin
           update t set x = ...
 exception
    when DUP_VAL_ON_INDEX
         -- primary key or unique key violation
    when OTHERS
         -- other kind of exception
  end;

Upvotes: 4

Florin Ghita
Florin Ghita

Reputation: 17643

If the syntax is not correct the entire block will be invalid, so you'll not be able to run it.

If you want to run all statements, despite that one can raise an exception, you can:

BEGIN

  BEGIN
    statement1;
  EXCEPTION 
    when exception1 then 
      some commands or null;
    when exception2 then 
      some commands or null;
  END;

  BEGIN
    statement2;
  EXCEPTION 
    when exception3 then 
      some commands or null;
    when exception4 then 
      some commands or null;
  END;

  etc.
END;

Upvotes: 2

Related Questions