CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION

<p><font face="Verdana" size="2">========================================================</font><br /><b><font
color="#0000FF"face="Verdana" size="2">CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION</font></b><br /><br /><p><font
face="Verdana"size="2">========================================================</font><br /><font face="Verdana"
size="2">--Oracle</font><br/><font face="Verdana"
size="2">========================================================</font><br/><font face="Verdana" size="2">CREATE OR
REPLACEFUNCTION MYCURSOR (</font><br />        <font face="Verdana" size="2">VARINA IN VARCHAR2</font><br /><font
face="Verdana"size="2">)</font><br /><font face="Verdana" size="2">RETURN VARCHAR2</font><br /><font face="Verdana"
size="2"> IS</font><br/><font face="Verdana" size="2"> v_sql                  VARCHAR2(2000);</font><br /><font
face="Verdana"size="2"> alert_mesg             VARCHAR2(32767);</font><br /><font face="Verdana" size="2"> IN_VAR1
      VARCHAR2(10);</font><br /><font face="Verdana" size="2"> IN_VAR2        VARCHAR2(10);</font><br /><font
face="Verdana"size="2"> V_COUNT        NUMBER;</font><br /><font face="Verdana" size="2"> v_cursorid    
NUMBER;</font><br/><font face="Verdana" size="2"> v_dummy        INTEGER;</font><br /><font face="Verdana"
size="2"> v_source      VARCHAR2(100);</font><br /><font face="Verdana" size="2">BEGIN</font><br /><font face="Verdana"
size="2"> v_cursorid:= DBMS_SQL.OPEN_CURSOR;</font><p><font face="Verdana" size="2"> v_sql := 'SELECT A1, A2, count(*)
FROMA group by A1,A2';</font><br /><p><font face="Verdana" size="2"> --Parse the query.</font><br /><font
face="Verdana"size="2"> DBMS_SQL.PARSE(v_cursorid, v_sql, DBMS_SQL.V7);</font><p><font face="Verdana"
size="2"> --Defineoutput columns</font><br /><font face="Verdana" size="2"> DBMS_SQL.DEFINE_COLUMN(v_cursorid, 1,
IN_VAR1,10);</font><br /><font face="Verdana" size="2"> DBMS_SQL.DEFINE_COLUMN(v_cursorid, 2, IN_VAR2, 10);</font><br
/><fontface="Verdana" size="2"> DBMS_SQL.DEFINE_COLUMN(v_cursorid, 3, V_COUNT);</font><p><font face="Verdana"
size="2"> --Executedynamic sql</font><br /><font face="Verdana" size="2"> v_dummy :=
DBMS_SQL.EXECUTE(v_cursorid);</font><p><fontface="Verdana" size="2"> LOOP</font><br /><font face="Verdana" size="2"> 
IFDBMS_SQL.FETCH_ROWS(v_cursorid) = 0 then</font><br /><font face="Verdana" size="2">       exit;</font><br /><font
face="Verdana"size="2">  END IF;</font><p><font face="Verdana" size="2"> 
DBMS_SQL.COLUMN_VALUE(v_cursorid,1,IN_VAR1);</font><br/><font face="Verdana" size="2"> 
DBMS_SQL.COLUMN_VALUE(v_cursorid,2,IN_VAR2);</font><p><fontface="Verdana" size="2">  --Build output string</font><br
/><fontface="Verdana" size="2">  alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);</font><p><font
face="Verdana"size="2"> END LOOP;</font><p><font face="Verdana"
size="2"> DBMS_SQL.CLOSE_CURSOR(v_cursorid);</font><p><fontface="Verdana" size="2"> RETURN alert_mesg;</font><p><font
face="Verdana"size="2">   EXCEPTION</font><br /><font face="Verdana" size="2">      WHEN OTHERS THEN</font><br
/>        <fontface="Verdana" size="2"> DBMS_SQL.CLOSE_CURSOR(v_cursorid);</font><br /><font face="Verdana"
size="2">        RETURN 'No troubleshooting information at this time.'|| SQLERRM;</font><p><font face="Verdana"
size="2">ENDMYCURSOR;</font><br /><font face="Verdana" size="2">/</font><br /><font face="Verdana" size="2">SHOW
ERROR</font><br/><br /><br /><p><font face="Verdana"
size="2">=========================================================</font><br/><font face="Verdana"
size="2">--Oracle</font><br/><font face="Verdana"
size="2">========================================================</font><br/><font face="Verdana" size="2">CREATE OR
REPLACEFUNCTION MYCURSOR (</font><br />        <font face="Verdana" size="2">VARINA IN VARCHAR2</font><br /><font
face="Verdana"size="2">)</font><br /><font face="Verdana" size="2">RETURN VARCHAR2</font><br /><font face="Verdana"
size="2"> IS</font><br/><font face="Verdana" size="2"> alert_mesg             VARCHAR2(32767);</font><br /><font
face="Verdana"size="2"> IN_VAR1        VARCHAR2(10);</font><br /><font face="Verdana" size="2"> IN_VAR2       
VARCHAR2(10);</font><br/><font face="Verdana" size="2"> CURSOR MYCUR IS SELECT A1, A2, count(*) FROM A group by
A1,A2;</font><br/><font face="Verdana" size="2"> </font><br /><font face="Verdana" size="2">BEGIN</font><p><font
face="Verdana"size="2">  FOR rec IN MYCUR LOOP</font><br />        <font face="Verdana" size="2">  IN_VAR1 :=
rec.A1;</font><br/>        <font face="Verdana" size="2">  IN_VAR2 := rec.A2;</font><br />        <br />        <font
face="Verdana"size="2">  --Build output string</font><br />        <font face="Verdana" size="2">  alert_mesg :=
alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);</font><br/><font face="Verdana" size="2"> END LOOP;</font><p><font
face="Verdana"size="2"> RETURN alert_mesg;</font><p><font face="Verdana" size="2">   EXCEPTION</font><br /><font
face="Verdana"size="2">      WHEN OTHERS THEN</font><br /><font face="Verdana" size="2">         RETURN 'No
troubleshootinginformation at this time.'|| SQLERRM;</font><p><font face="Verdana" size="2">END MYCURSOR;</font><br
/><fontface="Verdana" size="2">/</font><br /><font face="Verdana" size="2">SHOW ERROR</font><br /><br /><p><font
face="Verdana"size="2">========================================================</font><br /><font face="Verdana"
size="2">--PostgreSQL</font><br/><font face="Verdana"
size="2">========================================================</font><p><fontface="Verdana" size="2">CREATE OR
REPLACEFUNCTION MYCURSOR (</font><br />        <font face="Verdana" size="2">VARINA VARCHAR</font><br /><font
face="Verdana"size="2">)</font><br /><font face="Verdana" size="2">RETURNS VARCHAR</font><br /><font face="Verdana"
size="2"> AS</font><br/><font face="Verdana" size="2">$$</font><br /><font face="Verdana" size="2"> DECLARE</font><br
/><fontface="Verdana" size="2"> _record        RECORD;</font><br /><font face="Verdana" size="2"> alert_mesg    
VARCHAR(2000);</font><br/><font face="Verdana" size="2"> IN_VAR1        VARCHAR(10);</font><br /><font face="Verdana"
size="2"> IN_VAR2       VARCHAR(10);</font><p><font face="Verdana" size="2">BEGIN</font><br /><font face="Verdana"
size="2"> alert_mesg:= '';</font><p><font face="Verdana" size="2"> --Define output columns</font><br /><font
face="Verdana"size="2"> FOR _record IN SELECT A1, A2, count(*) FROM A group by A1,A2 </font><br /><font face="Verdana"
size="2"> LOOP</font><br/><font face="Verdana" size="2">  IN_VAR1 := _record.A1;</font><br /><font face="Verdana"
size="2"> IN_VAR2 := _record.A2;</font><p><font face="Verdana" size="2">  --Build output string</font><br /><font
face="Verdana"size="2">  alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);</font><br /><font face="Verdana"
size="2"> ENDLOOP;</font><p><font face="Verdana" size="2"> RETURN alert_mesg;</font><p><font face="Verdana" size="2">  
--EXCEPTION</font><br/><font face="Verdana" size="2">   --   WHEN OTHERS THEN</font><br />        <font face="Verdana"
size="2">--RETURN 'No troubleshooting information at this time.';</font><p><font face="Verdana" size="2">END;</font><br
/><fontface="Verdana" size="2">$$ LANGUAGE plpgsql;</font><br /><br /><br /><p><font face="Verdana"
size="2">========================================================</font><br/><font face="Verdana"
size="2">--PostgreSQL</font><br/><font face="Verdana"
size="2">========================================================</font><br/><font face="Verdana" size="2">CREATE OR
REPLACEFUNCTION MYCURSOR (VARINA VARCHAR) RETURNS VARCHAR AS $$</font><br /><font face="Verdana"
size="2">declare</font><br/><font face="Verdana" size="2">     --cur1 cursor is select A1, A2 from A;</font><br /><font
face="Verdana"size="2">      cur1 refcursor;</font><br /><font face="Verdana" size="2">     cid integer;</font><br
/><fontface="Verdana" size="2">     _A1 varchar (10) ;</font><br /><font face="Verdana" size="2">     _A2 varchar (10)
;</font><br/><font face="Verdana" size="2">     alert_mesg VARCHAR(2000) := '';</font><br /><font face="Verdana"
size="2">BEGIN</font><br/><font face="Verdana" size="2">    --open cur1;</font><br /><font face="Verdana"
size="2"> OPENcur1 FOR execute('select * from A');</font><br /><font face="Verdana" size="2">   loop </font><br /><font
face="Verdana"size="2">    fetch cur1 into _A1, _A2;</font><p><font face="Verdana" size="2">    if not found
then</font><br/><font face="Verdana" size="2">       exit ;</font><br /><font face="Verdana" size="2">    end
if;</font><br/><font face="Verdana" size="2">    </font><br /><font face="Verdana" size="2">    alert_mesg :=
alert_mesg||rpad(_A1,20)||rpad(_A2,20);</font><br/><font face="Verdana" size="2">   end loop;</font><br /><font
face="Verdana"size="2">close cur1;</font><br /><font face="Verdana" size="2">return alert_mesg;</font><br /><font
face="Verdana"size="2">END;</font><br /><font face="Verdana" size="2">$$ LANGUAGE plpgsql</font><br /><br /><p><b><font
color="#808080"face="Times New Roman"
size="2">----------------------------------------------------------------------------------</font></b><br
/><b></b><b><fontcolor="#808080" face="Verdana" size="2">Dinesh Pandey</font></b><font face="Verdana"
size="2"> </font><fontcolor="#808080" face="Verdana" size="2"></font><br /><font color="#808080" face="Verdana"
size="2">Sr.Software Engineer<br /><br /></font>