From: Dinesh Pandey <dpandey@secf.com>
To: pgsql-sql@postgresql.org
Subject: Help - Oracle 9i to PostgreSQL SQL conversion
Date: Thu, 31 Mar 2005 16:35:16 +0530
Message-ID: <20050331110717.BDC4E53950@svr1.postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dpandey@secf.com
Subject: Re: Help - Oracle 9i to PostgreSQL SQL conversion
In-Reply-To: <20050331110717.BDC4E53950@svr1.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox