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