FW: Help

_____

From: Dinesh Pandey [mailto:dpandey(at)secf(dot)com]
Sent: Thursday, March 31, 2005 2:45 PM
To: pgsql-bugs(at)postgresql(dot)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