oracle - calling stored procedure from anonymous block -

i have problem reading result plsql stored procedure written in sql developer on oracle 11gr2 database on local machine.

this 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 procedure declaration:

create or replace package test_package  procedure test_procedure (i_id in number,                           o_data out sys_refcursor); end test_package; 

this body:

create or replace package body test_package   procedure test_procedure (i_id in number,                         o_data out sys_refcursor)   begin     open o_data       select  employee_id,           first_name,           last_name,           email,           phone_number       my_test_table       employee_id = i_id;          close o_data;   end test_procedure; end test_package; 

and 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 current_record;       exit when my_cursor%notfound;     dbms_output.put_line(' - out - ' || current_record.employee_id);   end loop; end; 

i getting error:

error starting @ 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 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 of wrong type ora-06550: line 8, column 5: pl/sql: sql statement ignored 06550. 00000 -  "line %s, column %s:\n%s" *cause:    pl/sql compilation error. *action: 

can explain wrong? tnx!

the cursor opened in procedure, don't need to, , can't, open directly in anonymous block. well, should open, you're closing in procedure. remove close procedure, , open block:

create or replace package body test_package   procedure test_procedure (i_id in number,                         o_data out sys_refcursor)   begin     open o_data       select  employee_id,           first_name,           last_name,           email,           phone_number       my_test_table       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 current_record;       exit when my_cursor%notfound;     dbms_output.put_line(' - out - ' || current_record.employee_id);   end loop; end; / 

sql fiddle - add data...