Hi All,
I am have created a simple procedure that hits the table to retrieve details. I made use of sys_refcursor. However, I am not sure how to pass the result-set as OUT parameter, so that I can read the data in my other anonymous block.
I am using Oracle 11.2 and SQL Developer 17.2
Suggestions and answers are highly appreciated.
***Below is the procedure***
create or replace procedure pro_test_acc1(acc_cur out sys_refcursor)
is
begin
open acc_cur for
select seqno,lanid,name,source_system,login_date,login_status,login_type,logout_date from (select seqno,lanid,name,source_system,login_date,login_status,login_type,logout_date,
rank() over (partition by lanid order by login_date desc) rnk from access_log_test) where rnk=1 and lanid='admin';
end;
###############################################################
***Below is the block I am using to call the procedure, so that I can display the output***
Note: I am trying to display the output in separate block just to see if the cursor I am passing as OUT parameter in above procedure holds the data or not. So that I can call that procedure in my desktop application. As of now, please help me in reading the data through procedure and displaying the output through another block.
set serveroutput on;
DECLARE
acc_cur1 sys_refcursor;
v_seqno number(30); vlogin_status nvarchar2 (50); vlanid nvarchar2(50); vname nvarchar2(50); vlogin_type nvarchar2 (50);
vsource_system nvarchar2 (50); vlogin_date timestamp; vlogout_date timestamp;
begin
pro_test_acc1(acc_cur1);
dbms_output.put_line(acc_cur1.v_seqno||' '||acc_cur1.vlanid||' '||acc_cur1.vname||' '||acc_cur1.vsource_system||' '||acc_cur1.vlogin_date||' '||acc_cur1.vlogin_status||' '||acc_cur1.vlogin_type||' '||acc_cur1.vlogout_date);
END;
###############################################################
***I get below error when I compile or run the above block to call the procedure:***
Error report -
ORA-06550: line 12, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 12, column 1:
PL/SQL: Statement ignored
ORA-06550: line 13, column 31:
PLS-00487: Invalid reference to variable 'ACC_CUR1'
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
###############################################################