ORACLE call stored procedure inside package with cursor type

0
votes

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;
2
oracleplsql
There are several different errors described in the comments of the answer you're linking to. What error are you getting specifically? And how exactly are you calling this procedure. In your comment to another answer, you indicate that this procedure is in a package. Is that correct? And I assume that is different from the types package where your cursor_type is defined? - Justin Cave
@JustinCave - yes, procedure is inside package. I've edited my code. Sorry, I don't understand the other part of your question about types? - FrenkyB
The declaration of your procedure says that m_result is of type types.cursor_type. That implies that you have a types package where cursor_type is defined. My guess is that it was defined as a simple weakly typed ref cursor (i.e. it is identical to sys_refcursor) but that's just a guess. What is the code that you're using to call this procedure? What is the error that you're getting? - Justin Cave
@JustinCave - where is this cursor supposed to be declared? For sure it's in the folder 'Types' - I've checked all the types. The error is: Bind Variable "c" is NOT DECLARED - FrenkyB
What is the declaration of cursor_type in the types package? Is it a simple weakly typed ref cursor? What is the code you're using to call the procedure? I'm assuming that's where the variable c is being used without being declared. - Justin Cave

2 Answers

1
votes

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).

1
votes

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.