I keep forgetting this method when I try to debug a procedure after some interval, so I am blogging it for my own reference.
Procedure:
1
2
3
4
| PROCEDURE GET_ROLES_FOR_USER_LOGIN (
i_user_domain IN IEMS_USERS.DOMAIN%TYPE,
i_user_login IN IEMS_USERS.LOGIN%TYPE,
o_roles OUT SYS_REFCURSOR);
|
Generally, we can right click on the procedure name on the schema browser and select Execute package, which gives the skeleton for executing your procedure. Once we have the code, we just add a refcursor variable at the top, assign its value from the output of the procedure and then print it at the end.
Displaying Output:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| variable v_roles refcursor;
DECLARE
I_USER_DOMAIN NVARCHAR2(100);
I_USER_LOGIN NVARCHAR2(100);
O_ROLES SYS_REFCURSOR;
BEGIN
I_USER_DOMAIN := 'External';
I_USER_LOGIN := 'Vijay';
O_ROLES := NULL;
IEMS.PKG_USER.GET_ROLES_FOR_USER_LOGIN ( I_USER_DOMAIN, I_USER_LOGIN, O_ROLES );
COMMIT;
:v_roles := O_ROLES;
END;
Print v_roles;
|
Happy coding!