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 CF0EA53702 for ; Wed, 6 Apr 2005 09:41:05 +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 70496-08 for ; Wed, 6 Apr 2005 08:41:04 +0000 (GMT) Received: from secf.com (209-128-104-244.bayarea.net [209.128.104.244]) by svr1.postgresql.org (Postfix) with ESMTP id 8008253762 for ; Wed, 6 Apr 2005 09:41:02 +0100 (BST) Received: from dinesh by secf.com (MDaemon.PRO.v7.2.2.R) with ESMTP id md50000584588.msg for ; Wed, 06 Apr 2005 01:41:22 -0700 Reply-To: From: "Dinesh Pandey" To: "'PostgreSQL'" Subject: CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION Date: Wed, 6 Apr 2005 14:08:42 +0530 Organization: Second Foundation India P Ltd. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0051_01C53AB2.256A7290" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Thread-Index: AcU6hAmXd429D/b1Rpunmr4mvCOfwg== X-Authenticated-Sender: dpandey@secf.com X-Spam-Processed: mail.secf.com, Wed, 06 Apr 2005 01:41:22 -0700 (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, Wed, 06 Apr 2005 01:41:24 -0700 Message-Id: <20050406084102.8008253762@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.059 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_60_70, HTML_MESSAGE, MSGID_FROM_MTA_ID, SUBJ_ALL_CAPS, UPPERCASE_50_75 X-Spam-Level: ** X-Archive-Number: 200504/53 X-Sequence-Number: 20946 This is a multi-part message in MIME format. ------=_NextPart_000_0051_01C53AB2.256A7290 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit ======================================================== CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION ======================================================== --Oracle ======================================================== CREATE OR REPLACE FUNCTION MYCURSOR ( VARINA IN VARCHAR2 ) RETURN VARCHAR2 IS v_sql VARCHAR2(2000); alert_mesg VARCHAR2(32767); IN_VAR1 VARCHAR2(10); IN_VAR2 VARCHAR2(10); V_COUNT NUMBER; v_cursorid NUMBER; v_dummy INTEGER; v_source VARCHAR2(100); BEGIN v_cursorid := DBMS_SQL.OPEN_CURSOR; v_sql := 'SELECT A1, A2, count(*) FROM A group by A1,A2'; --Parse the query. DBMS_SQL.PARSE(v_cursorid, v_sql, DBMS_SQL.V7); --Define output columns DBMS_SQL.DEFINE_COLUMN(v_cursorid, 1, IN_VAR1, 10); DBMS_SQL.DEFINE_COLUMN(v_cursorid, 2, IN_VAR2, 10); DBMS_SQL.DEFINE_COLUMN(v_cursorid, 3, V_COUNT); --Execute dynamic sql v_dummy := DBMS_SQL.EXECUTE(v_cursorid); LOOP IF DBMS_SQL.FETCH_ROWS(v_cursorid) = 0 then exit; END IF; DBMS_SQL.COLUMN_VALUE(v_cursorid,1,IN_VAR1); DBMS_SQL.COLUMN_VALUE(v_cursorid,2,IN_VAR2); --Build output string alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20); END LOOP; DBMS_SQL.CLOSE_CURSOR(v_cursorid); RETURN alert_mesg; EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(v_cursorid); RETURN 'No troubleshooting information at this time.'|| SQLERRM; END MYCURSOR; / SHOW ERROR ========================================================= --Oracle ======================================================== CREATE OR REPLACE FUNCTION MYCURSOR ( VARINA IN VARCHAR2 ) RETURN VARCHAR2 IS alert_mesg VARCHAR2(32767); IN_VAR1 VARCHAR2(10); IN_VAR2 VARCHAR2(10); CURSOR MYCUR IS SELECT A1, A2, count(*) FROM A group by A1,A2; BEGIN FOR rec IN MYCUR LOOP IN_VAR1 := rec.A1; IN_VAR2 := rec.A2; --Build output string alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20); END LOOP; RETURN alert_mesg; EXCEPTION WHEN OTHERS THEN RETURN 'No troubleshooting information at this time.'|| SQLERRM; END MYCURSOR; / SHOW ERROR ======================================================== --PostgreSQL ======================================================== CREATE OR REPLACE FUNCTION MYCURSOR ( VARINA VARCHAR ) RETURNS VARCHAR AS $$ DECLARE _record RECORD; alert_mesg VARCHAR(2000); IN_VAR1 VARCHAR(10); IN_VAR2 VARCHAR(10); BEGIN alert_mesg := ''; --Define output columns FOR _record IN SELECT A1, A2, count(*) FROM A group by A1,A2 LOOP IN_VAR1 := _record.A1; IN_VAR2 := _record.A2; --Build output string alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20); END LOOP; RETURN alert_mesg; --EXCEPTION -- WHEN OTHERS THEN -- RETURN 'No troubleshooting information at this time.'; END; $$ LANGUAGE plpgsql; ======================================================== --PostgreSQL ======================================================== CREATE OR REPLACE FUNCTION MYCURSOR (VARINA VARCHAR) RETURNS VARCHAR AS $$ declare --cur1 cursor is select A1, A2 from A; cur1 refcursor; cid integer; _A1 varchar (10) ; _A2 varchar (10) ; alert_mesg VARCHAR(2000) := ''; BEGIN --open cur1; OPEN cur1 FOR execute('select * from A'); loop fetch cur1 into _A1, _A2; if not found then exit ; end if; alert_mesg := alert_mesg||rpad(_A1,20)||rpad(_A2,20); end loop; close cur1; return alert_mesg; END; $$ LANGUAGE plpgsql ---------------------------------------------------------------------------- ------ Dinesh Pandey Sr. Software Engineer ------=_NextPart_000_0051_01C53AB2.256A7290 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
CURSOR EXAMPLE = - ORACLE TO POSTGRES CONVERSION



=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--Oracle
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
CREATE OR REPLACE FUNCTION MYCURSOR = (
        VARINA IN VARCHAR2
)
RETURN VARCHAR2
 IS
 v_sql  =         =         VARCHAR2(2000);
 alert_mesg     =         VARCHAR2(32767);
 IN_VAR1 =        VARCHAR2(10);
 IN_VAR2 =        VARCHAR2(10);
 V_COUNT =        NUMBER;
 v_cursorid     NUMBER;
 v_dummy        = INTEGER;
 v_source       = VARCHAR2(100);
BEGIN
 v_cursorid :=3D = DBMS_SQL.OPEN_CURSOR;

 v_sql :=3D 'SELECT A1, A2, = count(*) FROM A group by A1,A2';


 --Parse the query.
 DBMS_SQL.PARSE(v_cursorid, = v_sql, DBMS_SQL.V7);

 --Define output columns
 DBMS_SQL.DEFINE_COLUMN(v_cursorid, 1, IN_VAR1, = 10);
 DBMS_SQL.DEFINE_COLUMN(v_cursorid, 2, IN_VAR2, = 10);
 DBMS_SQL.DEFINE_COLUMN(v_cursorid, 3, = V_COUNT);

 --Execute dynamic sql
 v_dummy :=3D = DBMS_SQL.EXECUTE(v_cursorid);

 LOOP
  IF = DBMS_SQL.FETCH_ROWS(v_cursorid) =3D 0 then
       = exit;
  END IF;

  = DBMS_SQL.COLUMN_VALUE(v_cursorid,1,IN_VAR1);
  = DBMS_SQL.COLUMN_VALUE(v_cursorid,2,IN_VAR2);

  --Build output string
  alert_mesg :=3D = alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);

 END LOOP;

 DBMS_SQL.CLOSE_CURSOR(v_cursorid);

 RETURN alert_mesg;

   EXCEPTION
      WHEN = OTHERS THEN
         DBMS_SQL.CLOSE_CURSOR(v_cursorid);
         RETURN = 'No troubleshooting information at this time.'|| SQLERRM;

END MYCURSOR;
/
SHOW ERROR




=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--Oracle
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
CREATE OR REPLACE FUNCTION MYCURSOR = (
        VARINA IN VARCHAR2
)
RETURN VARCHAR2
 IS
 alert_mesg     =         VARCHAR2(32767);
 IN_VAR1 =        VARCHAR2(10);
 IN_VAR2 =        VARCHAR2(10);
 CURSOR MYCUR IS SELECT A1, A2, = count(*) FROM A group by A1,A2;
 
BEGIN

  FOR rec IN MYCUR LOOP
          IN_VAR1 :=3D rec.A1;
          IN_VAR2 :=3D rec.A2;
       =20
          --Build output string
          alert_mesg :=3D = alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);
 END LOOP;

 RETURN alert_mesg;

   EXCEPTION
      WHEN = OTHERS THEN
         RETURN = 'No troubleshooting information at this time.'|| SQLERRM;

END MYCURSOR;
/
SHOW ERROR



=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--PostgreSQL
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

CREATE OR REPLACE FUNCTION MYCURSOR = (
        VARINA VARCHAR
)
RETURNS VARCHAR
 AS
$$
 DECLARE
 _record =        RECORD;
 alert_mesg     = VARCHAR(2000);
 IN_VAR1 =        VARCHAR(10);
 IN_VAR2 =        VARCHAR(10);

BEGIN
 alert_mesg :=3D '';

 --Define output columns
 FOR _record IN SELECT A1, A2, = count(*) FROM A group by A1,A2
 LOOP
  IN_VAR1 :=3D = _record.A1;
  IN_VAR2 :=3D = _record.A2;

  --Build output string
  alert_mesg :=3D = alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);
 END LOOP;

 RETURN alert_mesg;

   --EXCEPTION
   --   WHEN = OTHERS THEN
        -- RETURN 'No troubleshooting information at this = time.';

END;
$$ LANGUAGE plpgsql;




=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--PostgreSQL
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
CREATE OR REPLACE FUNCTION MYCURSOR = (VARINA VARCHAR) RETURNS VARCHAR AS $$
declare
     --cur1 = cursor is select A1, A2 from A;
      cur1 = refcursor;
     cid = integer;
     _A1 varchar = (10) ;
     _A2 varchar = (10) ;
     alert_mesg = VARCHAR(2000) :=3D '';
BEGIN
    --open = cur1;
 OPEN cur1 FOR execute('select = * from A');
   loop
    fetch cur1 into = _A1, _A2;

    if not found = then
       = exit ;
    end if;
   
    alert_mesg :=3D = alert_mesg||rpad(_A1,20)||rpad(_A2,20);
   end loop;
close cur1;
return alert_mesg;
END;
$$ LANGUAGE plpgsql



------------------------------------------------------------------= ----------------
Dinesh = Pandey 
Sr. Software = Engineer

------=_NextPart_000_0051_01C53AB2.256A7290--