[SQL] Help - Oracle 9i to PostgreSQL SQL conversion

[Dinesh Pandey]

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.

 

Fuction attached below....

 

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