Oracle to Postgres conversion passing cursor as a parameter

Question

I"m converting an oracle stored procedure to Postgres. Oracle uses an out parameter

O_MTG_CURSOR OUT SYS_REFCURSOR

that is included as a parameter to call to another stored procedure:

 GET_MTG(O_MTG_CURSOR ,O_SQLCODE,O_SQLMSG );

When I try to call the same stored procedure in postgres, the cursor is null

CALL GET_MTG(O_MTG_CURSOR,O_SQLCODE,O_SQLMSG );

Is there any way to pass the cursor as a parameter and have it return the results from the called stored procedure? This Oracle procedure makes 10 different calls to other stored procedures and fills the output cursors with the results of those calls. SO I need to be able to repeat this in postgres.

Minimal code that shows the problem

CREATE OR REPLACE PROCEDURE get_mtg (
    i_mtg_id text,
    i_lookup_type text,
    i_agd text,
    i_timestamp timestamp without time zone,
    INOUT o_mtg_cursor  refcursor DEFAULT NULL::refcursor,
    INOUT o_dir_ocursor refcursor DEFAULT NULL::refcursor,  
    INOUT o_error_code integer DEFAULT 0,
    INOUT o_error_msg character varying DEFAULT 'SUCCESS'::character varying)
LANGUAGE 'plpgsql'

AS $BODY$
DECLARE
    lookup_type_missing CHARACTER VARYING := 'lookup_type_missing';
    mtg_id_missing CHARACTER VARYING := 'mtg_id_missing';
    mtg_not_found CHARACTER VARYING := 'mt_not_found';
    system_exception CHARACTER VARYING := 'system_exception';
    dir_not_found CHARACTER VARYING := 'dir_not_found';
    data_is_in_flux_state CHARACTER VARYING := 'data_is_in_flux_state';
    l_agd_nbr CHARACTER VARYING(20);

BEGIN
    RAISE NOTICE USING MESSAGE = '[GET_MTG] START';
    o_error_code := 0;
    o_error_msg := CONCAT_WS('', 'SUCESSFUL');

    IF i_lookup_type IS NULL THEN
        /* RAISE THE EXCEPTION THAT LOOK UP TYPE CANNOT BE NULL */
        RAISE USING detail = lookup_type_missing, hint = 1;
    ELSIF i_mtg_id IS NULL THEN
        /* RAISE THE EXCEPTION THAT MTG_ID CANNOT BE NULL. */
        RAISE USING detail = mtg_id_missing, hint = 1;
    ELSE

       RAISE NOTICE USING MESSAGE = '[GET_MTG] GET THE OUT PUT RESULT SET - START';
        CALL GET_MTG_DIR(o_dir_cursor,O_ERROR_CODE,O_ERROR_MSG );
        CALL GET_MTG_DTL(o_mtg_cursor,O_ERROR_CODE,O_ERROR_MSG );
    END IF;

    RAISE NOTICE USING MESSAGE = '[GET_MTG] END';
    EXCEPTION
        WHEN raise_exception THEN
            DECLARE
                exc$name CHARACTER VARYING;
                exc$code CHARACTER VARYING;
            BEGIN
                GET STACKED DIAGNOSTICS exc$name := pg_exception_detail,
                    exc$code := pg_exception_hint;

                IF exc$name = mtg_id_missing THEN
                    o_error_code := 101;
                    o_error_msg := 'ERROR 101.1 : I_MTG_ID CAN NOT BE NULL  : RESOLUTION : PASS ''AGD_NBR''';
                END IF;

                IF exc$name = mtg_not_found THEN
                    o_error_code := 101;
                    o_error_msg := CONCAT_WS('', 'ERROR 101.2 : MTG CANNOT BE FOUND : ', i_agd_nbr, ' : RESOLUTION : CHECK THE MTG TABLE');
                END IF;

                IF exc$name = dir_not_found THEN
                    o_error_code := 102;
                    o_error_msg := CONCAT_WS('', 'ERROR 102.1 : DIR IS EMPTY FOR PREF TYPE ''Z'' ON : ', i_mtg_id, ' & ', l_agd_nbr, ' : RESOLUTION : ESCALATE TO SYSADMIN');
                END IF;

                IF exc$name = data_is_in_flux_state THEN
                    o_error_code := 501;
                    o_error_msg := CONCAT_WS('', 'ERROR 501.1 : AGD/DIR/PREF are in flux state - ', i_mtg_id, ', ', l_agd_nbr, ' : RESOLUTION : ESCALATE TO SYSADMIN ');
                END IF;
                IF exc$name = system_exception THEN
                    o_error_code := SQLSTATE * - 1;
                    o_error_msg := CONCAT_WS('', '[GET_MTG] SYSTEM EXCEPTION OCCURED : ', substr(SQLERRM, 1, 200));
                END IF;
            END;
         WHEN no_data_found THEN
            o_error_code := 100;
            o_error_msg = 'Error: ' || SQLSTATE || ' - ' || SQLERRM;
        WHEN others THEN
            o_error_code = -1;
            o_error_msg = 'Error: ' || SQLSTATE || ' - ' || SQLERRM;    
END;
$BODY$;

Answer 1

Thanks for your input but I am not sure I understand your issue. Maybe my answer will help.

Here is an example how you can use refcursor with stored procedures.

Here is the source code:

create table t(x int, t text);
insert into t values(1, 'ONE');
insert into t values(2, 'TWO');
--
create or replace procedure prc3 (inout p_rc refcursor) 
as $$
declare
 l_rc refcursor;
begin
  open l_rc for select * from t;
  p_rc = l_rc;
end;
$$ language plpgsql;
--
\echo
--
create or replace procedure prc2 (inout p_rc refcursor) 
as $$
declare
 l_rc refcursor;
begin
 call prc3(l_rc);
 p_rc = l_rc;
end;
$$ language plpgsql;
--
\echo
--
create or replace procedure prc1()
as $$
declare
v refcursor;
vx int;
vy text;
begin
 call prc2(v);
 fetch next from v into vx, vy;
 raise notice 'vx=% vy=%', vx, vy;
end
$$ language plpgsql;
--
call prc1();

And here is an execution:

create table t(x int, t text);
CREATE TABLE
insert into t values(1, 'ONE');
INSERT 0 1
insert into t values(2, 'TWO');
INSERT 0 1
create or replace procedure prc3 (inout p_rc refcursor) 
as $$
declare
 l_rc refcursor;
begin
  open l_rc for select * from t;
  p_rc = l_rc;
end;
$$ language plpgsql;
CREATE PROCEDURE

create or replace procedure prc2 (inout p_rc refcursor) 
as $$
declare
 l_rc refcursor;
begin
 call prc3(l_rc);
 p_rc = l_rc;
end;
$$ language plpgsql;
CREATE PROCEDURE

create or replace procedure prc1()
as $$
declare
v refcursor;
vx int;
vy text;
begin
 call prc2(v);
 fetch next from v into vx, vy;
 raise notice 'vx=% vy=%', vx, vy;
end
$$ language plpgsql;
CREATE PROCEDURE
call prc1();
psql:trc.sql:44: NOTICE:  vx=1 vy=ONE
CALL