[BUGS] FW: Help

[Dinesh Pandey]

 

---

From: Dinesh Pandey [mailto:[EMAIL PROTECTED]
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