Skip site navigation (1) Skip section navigation (2)

Re: pgsql: Add PL/pgSQL SQLSTATE and SQLERRM support

From: Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Neil Conway <neilc(at)samurai(dot)com>,Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>,<pgsql-committers(at)postgresql(dot)org>
Subject: Re: pgsql: Add PL/pgSQL SQLSTATE and SQLERRM support
Date: 2005-05-26 15:35:12
Message-ID: Pine.LNX.4.44.0505260927270.29321-100000@kix.fsv.cvut.cz (view raw or flat)
Thread:
Lists: pgsql-committerspgsql-patches
> 	BEGIN
> 		-- do something perilous
> 	EXCEPTION
> 		WHEN OTHERS THEN -- nothing much
> 	END;
> 	IF SQLSTATE = '42000' THEN ...

I understand. My idea was detect local exception for local block, I can't 
to see exception's information outside block and I cant get exception's 
info from inner block. Your idea is easy for implementation, but oracle 

http://www.unix.org.ua/orelly/oracle/prog2/ch13_03.htm

In Oracle doc:

If no exception has been raised, SQLCODE returns zero and SQLERRM returns 
the message: ORA-0000: normal, successful completion.

If you reference SQLCODE outside of an exception section, it always 
returns 0, which means normal, successful completion.

I tested it on Oracle 10g

 return integer  as  
begin
  begin
    dbms_output.put_line('1: '||SQLCODE||' -> '||SQLERRM);
    raise_application_error(-20001, 'First exception');
  exception when others then
    dbms_output.put_line('2: '||SQLCODE||' -> '||SQLERRM);
    begin
      dbms_output.put_line('3: '||SQLCODE||' -> '||SQLERRM);
      raise_application_error(-20002, 'Second exception');
    exception when others then
      dbms_output.put_line('4: '||SQLCODE||' -> '||SQLERRM);
    end;
    dbms_output.put_line('5: '||SQLCODE||' -> '||SQLERRM);
  end;
  dbms_output.put_line('6: '||SQLCODE||' -> '||SQLERRM);
  return 1;
end;

select foo from dual

1: 0 -> ORA-0000: normal, successful completion
2: -20001 -> ORA-20001: First exception
3: -20001 -> ORA-20001: First exception
4: -20002 -> ORA-20002: Second exception
5: 0 -> ORA-0000: normal, successful completion
6: 0 -> ORA-0000: normal, successful completion

What it is mean?

So we can have only one procedure level scope variable, which is 
initialized on start of exception and zeroized on the end of exception 
block. This behavior is different from my patch, but is better for Oracle 
compatibility and I prefere its.

I'll change patch, I can simplify it, if there will be agreement.

Best regards
Pavel Stehule



In response to

Responses

pgsql-committers by date

Next:From: Tom LaneDate: 2005-05-26 15:40:51
Subject: Re: pgsql: Add PL/pgSQL SQLSTATE and SQLERRM support (Really Oracle behavior)
Previous:From: Bruce MomjianDate: 2005-05-26 15:26:00
Subject: pgsql: Back out: Display only 9 not 10 digits of precision for

pgsql-patches by date

Next:From: Tom LaneDate: 2005-05-26 15:40:51
Subject: Re: pgsql: Add PL/pgSQL SQLSTATE and SQLERRM support (Really Oracle behavior)
Previous:From: Bruce MomjianDate: 2005-05-26 15:25:19
Subject: Re: Regression failures: time, timetz, horology

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group