Displaying SYS_REFCURSOR / CURSOR results from Oracle in Toad

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:

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:

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!

comments powered by Disqus