CODEWITHSUNDEEP
oracle-databaseplsql
FrenkyB
FrenkyB

Reputation: 7197

ORACLE call stored procedure inside package with cursor type

I am really struggling with calling stored procedure below. I am using oracle sql developer, version 19.2. What I have tried is this answer but I am receiving errors (same errors as described in comments). Why is it so hard to call such a simple stored procedure from sql developer?

create or replace PACKAGE BODY "PCK_FIRST" IS
      PROCEDURE GET_GETNAMES(
        USER_ID IN NUMBER,    
        M_RESULT OUT TYPES.CURSOR_TYPE
      ) IS
      BEGIN
        OPEN M_RESULT FOR
        SELECT * FROM PER_NAMES WHERE ID = USER_ID;
      END;

END PCK_FIRST;

Upvotes: 0

Views: 2534

Answers (2)

MT0
MT0

Reputation: 167774

Declare a variable with the type TYPES.CURSOR_VALUE and then use that as the output from the cursor:

DECLARE
  cur    TYPES.CURSOR_TYPE;
  p_id   PER_NAMES.ID%TYPE;
  p_name PER_NAMES.NAME%TYPE;
BEGIN
  PCK_FIRST.get_getnames( 1, cur );
  LOOP
    FETCH cur INTO p_id, p_name;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( p_id || ': ' || p_name );
  END LOOP;
  CLOSE cur;
END;
/

db<>fiddle here

If you want to call it in SQL Developer then:

VARIABLE cur REFCURSOR;
EXECUTE pck_first.get_getnames(1, :cur);
PRINT cur;

And run it as a script using F5 (rather than as individual statements using Ctrl-Enter).

Upvotes: 1

Flavius Burghila
Flavius Burghila

Reputation: 36

As first I suggest you to run this then try again to exec your procedure

create or replace package types as
   type cursor_type is ref cursor;
end
/

otherwise try this changing TYPES.CURSOR_TYPE with sys_refcursor

--example
create table test_user (
   user_id number(19) not null,
   username varchar2(50)
);

insert into test_user values (1, 'user1');

insert into test_user values (2, 'user2');

create or replace procedure test_proc (
   p_user_id in number,
   p_out_rec out sys_refcursor
)
is
   l_out SYS_REFCURSOR;
begin
   open l_out for
      select * 
        from test_user
       where user_id = p_user_id;
end;
/

declare
   l_output sys_refcursor;
   l_userid number := 1;
begin
  test_proc(1, l_output);
end;

PL/SQL procedure successfully completed.

Upvotes: 1

Related Questions