Oracle – calling stored procedure from anonymous block

oracleplsqlstored-procedures

I have problem reading result from plsql stored procedure written in sql developer on Oracle 11gr2 database on local machine.

This is my table:

create table MY_TEST_TABLE
(employee_id    NUMBER(6)
,first_name     VARCHAR2(20)
,last_name      VARCHAR2(25) 
,email          VARCHAR2(25) 
,phone_number   VARCHAR2(20));

This is procedure declaration:

create or replace PACKAGE TEST_PACKAGE AS 
procedure test_procedure (i_id in number,
                          o_data out sys_refcursor);
END TEST_PACKAGE;

This is body:

create or replace PACKAGE BODY TEST_PACKAGE AS
  procedure test_procedure (i_id in number,
                        o_data out sys_refcursor) AS
  BEGIN
    open o_data for
      select  employee_id,
          first_name,
          last_name,
          email,
          phone_number
      from my_test_table
      where EMPLOYEE_ID = i_id;     
    close o_data;
  END test_procedure;
END TEST_PACKAGE;

And this is anonymous block call:

SET serveroutput on
DECLARE
  in_id number; 
  my_cursor sys_refcursor;
  current_record my_test_table%ROWTYPE;
BEGIN
  in_id := 1;
  test_package.test_procedure(in_id, my_cursor);
  open my_cursor;
  LOOP
    FETCH my_cursor INTO current_record;
      EXIT WHEN my_cursor%NOTFOUND;
    dbms_output.put_line(' - out - ' || current_record.employee_id);
  END LOOP;
END;

I am getting error:

Error starting at line : 2 in command -
DECLARE
  in_id number; 
  my_cursor sys_refcursor;
  current_record my_test_table%ROWTYPE;
BEGIN
  in_id := 1;
  test_package.test_procedure(in_id, my_cursor);
  open my_cursor;
  LOOP
    FETCH my_cursor INTO current_record;
      EXIT WHEN my_cursor%NOTFOUND;
    dbms_output.put_line(' - out - ' || current_record.employee_id);
  END LOOP;
END;
Error report -
ORA-06550: line 8, column 5:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Can someone explain what is wrong?
Tnx!

Best Answer

The cursor is opened in the procedure, so you don't need to, and can't, open it directly in your anonymous block. Well, it should be open, but you're also closing it in the procedure. Remove the close from the procedure, and the open from the block:

create or replace PACKAGE BODY TEST_PACKAGE AS
  procedure test_procedure (i_id in number,
                        o_data out sys_refcursor) AS
  BEGIN
    open o_data for
      select  employee_id,
          first_name,
          last_name,
          email,
          phone_number
      from my_test_table
      where EMPLOYEE_ID = i_id;     
--    close o_data;
  END test_procedure;
END TEST_PACKAGE;
/

And:

DECLARE
  in_id number; 
  my_cursor sys_refcursor;
  current_record my_test_table%ROWTYPE;
BEGIN
  in_id := 1;
  test_package.test_procedure(in_id, my_cursor);
--  open my_cursor;
  LOOP
    FETCH my_cursor INTO current_record;
      EXIT WHEN my_cursor%NOTFOUND;
    dbms_output.put_line(' - out - ' || current_record.employee_id);
  END LOOP;
END;
/

SQL Fiddle - just add data...

Related Topic