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