CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION

========================================================
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