FW: Help

_____

From: Dinesh Pandey [mailto:dpandey@secf.com]
Sent: Thursday, March 31, 2005 2:45 PM
To: pgsql-bugs@postgresql.org
Subject: Help
Importance: High


Hi,

I have installed PostgreSQL 8.0.1 on Solaris 9.

I am porting my database from Oracle 9i to PostgreSQL. I am using PL/pgSQL
language.

In Oracle we can get error message from "SQLERRM" keyword and inserting it
into table.

How can I get error message/code in PostgreSQL after an EXCEPTION or RAISE
EXCEPTION occurs in EXCEPTION block??

Pls help me or send me some example.


Thanks
Dinesh Pandey



CREATE OR REPLACE FUNCTION DOES_NODE_HAVE_RULE
 (IN_SENTRYID_ID IN NUMBER
 ,IN_NODE_ID IN NUMBER
 ,IN_DEVICEID IN NUMBER
 ,IN_ACTION IN VARCHAR2
 )
 RETURN BOOLEAN
 IS


      does         NUMBER(2)       := 0;
      mesg   VARCHAR2(500) := 'Does rule exist failed for sentry:
'||in_sentryid_id||', node: '||in_node_id||'.';
      c_context     VARCHAR2(50)  := 'DOES NODE HAVE RULE';
      c_program     VARCHAR2(100)   := 'RULE_CONTROL.DOES_NODE_HAVE_RULE';
   v_sql   VARCHAR2(1000);
BEGIN

      v_sql := 'SELECT COUNT(*) FROM PORTAL_'||in_action||'_NODE_RULE WHERE
sentryid_id = '||in_sentryid_id||
            ' AND node_id = '||in_node_id||' AND device_id ='||in_deviceid;

   EXECUTE IMMEDIATE v_sql INTO does;

      IF does > 0 THEN
         RETURN TRUE;
      ELSIF does = 0 THEN
       RETURN FALSE;
      END IF;

   EXCEPTION
      WHEN OTHERS THEN
         DATAMAN.log_error(c_program, c_context, 1, 'PACKAGE', 'OTHERS:
'||mesg, SQLERRM);
         RAISE_APPLICATION_ERROR(-20000,SUBSTR(SQLERRM,1,250));

   END does_node_have_rule;
/
SHOW ERROR