X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 239E85395D for ; Thu, 31 Mar 2005 12:07:24 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90441-05 for ; Thu, 31 Mar 2005 11:07:19 +0000 (GMT) Received: from secf.com (209-128-104-244.bayarea.net [209.128.104.244]) by svr1.postgresql.org (Postfix) with ESMTP id BDC4E53950 for ; Thu, 31 Mar 2005 12:07:17 +0100 (BST) Received: from dinesh by secf.com (MDaemon.PRO.v7.2.2.R) with ESMTP id md50000561356.msg for ; Thu, 31 Mar 2005 03:07:56 -0800 Reply-To: From: "Dinesh Pandey" To: Subject: Help - Oracle 9i to PostgreSQL SQL conversion Date: Thu, 31 Mar 2005 16:35:16 +0530 Organization: Second Foundation India P Ltd. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00AD_01C5360F.A091DB90" X-Priority: 1 (Highest) X-MSMail-Priority: High X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Thread-Index: AcU10hUWVCCiEJ4vSF6iZJhujyZUCAAAJ5MAAAOi90A= Importance: High X-Authenticated-Sender: dpandey@secf.com X-Spam-Processed: mail.secf.com, Thu, 31 Mar 2005 03:07:56 -0800 (not processed: message from valid local sender) X-MDRemoteIP: 220.227.32.11 X-Return-Path: dpandey@secf.com X-MDaemon-Deliver-To: pgsql-sql@postgresql.org X-MDAV-Processed: mail.secf.com, Thu, 31 Mar 2005 03:07:57 -0800 Message-Id: <20050331110717.BDC4E53950@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.003 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_MESSAGE, MSGID_FROM_MTA_ID, UPPERCASE_25_50, X_MSMAIL_PRIORITY_HIGH, X_PRIORITY_HIGH X-Spam-Level: ** X-Archive-Number: 200503/410 X-Sequence-Number: 20889 This is a multi-part message in MIME format. ------=_NextPart_000_00AD_01C5360F.A091DB90 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_00AD_01C5360F.A091DB90 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

I = have installed=20 PostgreSQL 8.0.1 on Solaris 9.

I am = porting my=20 database from Oracle 9i to PostgreSQL. I am using PL/pgSQL=20 language.

In = Oracle we can=20 get error message from "SQLERRM" keyword and inserting it into = table.=20

How can I get=20 error message/code in PostgreSQL after an EXCEPTION or RAISE = EXCEPTION=20 occurs in EXCEPTION block??

Pls = help me or=20 send me some example.

Fuction attached = below....

Thanks

Dinesh=20 Pandey

CREATE OR REPLACE = FUNCTION=20 DOES_NODE_HAVE_RULE
(IN_SENTRYID_ID IN = NUMBER
,IN_NODE_ID IN=20 NUMBER
,IN_DEVICEID IN NUMBER
,IN_ACTION IN=20 VARCHAR2
)
RETURN=20 BOOLEAN
IS

     =20 does       =20 NUMBER(2)      :=3D=20 0;
      = mesg   VARCHAR2(500) :=3D=20 'Does rule exist failed for sentry: '||in_sentryid_id||', node:=20 '||in_node_id||'.';
     =20 c_context    VARCHAR2(50) :=3D 'DOES NODE = HAVE=20 RULE';
      c_program   =20 VARCHAR2(100) :=3D=20 'RULE_CONTROL.DOES_NODE_HAVE_RULE';
  =20 v_sql   VARCHAR2(1000);
BEGIN

v_sql :=3D 'SELECT COUNT(*) FROM=20 PORTAL_'||in_action||'_NODE_RULE WHERE sentryid_id =3D=20 '||in_sentryid_id||
=20 ' AND node_id =3D '||in_node_id||' AND = device_id=20 =3D'||in_deviceid;

   EXECUTE IMMEDIATE v_sql INTO does;
  =20
      IF does > 0=20 THEN
         RETURN=20 TRUE;
      ELSIF does =3D 0 = THEN
  =20     RETURN FALSE;
      END = IF;

   EXCEPTION
      WHEN = OTHERS=20 THEN
        =20 DATAMAN.log_error(c_program, c_context, 1, 'PACKAGE', 'OTHERS: = '||mesg,=20 SQLERRM);
    &nbs= p;   =20 RAISE_APPLICATION_ERROR(-20000,SUBSTR(SQLERRM,1,250));

END does_node_have_rule;
/
SHOW=20 ERROR

------=_NextPart_000_00AD_01C5360F.A091DB90--