Unanswered: how to change refcursor to stored proc with cursor?
I am trying to modify a scipt in Oracle 9i to take the refcursor and update the script into a stored procedure to make things easier and more efficient. Below is the current script:
var myrcurs refcursor;
open :myrcurs for
,'Account' as TYPE
,count(*) as "No. Objects"
from sys.dba_tab_privs a
where A.GRANTEE = B.USERNAME
AND a.privilege in ('INSERT', 'UPDATE', 'DELETE')
AND NOT (A.OWNER IN ('SYSTEM', 'SYS'))
AND NOT (A.GRANTEE IN ('DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'DBA'))
(A.GRANTEE = 'SYSTEM' AND A.OWNER IN ('SYS', 'WMSYS'))
(A.GRANTEE = 'SYS' AND A.OWNER = 'SYSTEM')
(A.GRANTEE = 'MDSYS'
AND A.OWNER = 'SYS'
AND A.PRIVILEGE IN ('DELETE', 'INSERT')
AND A.TABLE_NAME IN
How can I rewrite this script to use a stored procedure with a explicit cursor in Oracle? Thanks guys