Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: how to change refcursor to stored proc with cursor?

    Hi,

    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:

    <quote>
    var myrcurs refcursor;
    begin
    open :myrcurs for
    select a.grantee
    ,'Account' as TYPE
    ,a.owner
    ,a.privilege
    ,count(*) as "No. Objects"
    from sys.dba_tab_privs a
    ,sys.dba_users b
    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'))
    and NOT
    (A.GRANTEE = 'SYSTEM' AND A.OWNER IN ('SYS', 'WMSYS'))
    AND NOT
    (A.GRANTEE = 'SYS' AND A.OWNER = 'SYSTEM')
    AND NOT
    (A.GRANTEE = 'MDSYS'
    AND A.OWNER = 'SYS'
    AND A.PRIVILEGE IN ('DELETE', 'INSERT')
    AND A.TABLE_NAME IN
    ('EXPDEPACT$'
    ,'EXPDEPOBJ$'
    ,'EXPPKGACT$'
    ,'EXPPKGOBJ$'
    )
    )

    </quote>

    How can I rewrite this script to use a stored procedure with a explicit cursor in Oracle? Thanks guys

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    create or replace procedure myproc is
    cursor mycur is
    	select a.grantee,'Account' as TYPE, ...
    	from ...
    	where ...;
    begin
    for cur_rec in mycur loop
    	/* do something with the cursor */
    end loop;
    end;
    Would that be OK?

  3. #3
    Join Date
    May 2003
    Posts
    369

    thanks!

    Great thats exactly what I was looking for! Thanks again- just knew had to be able to map a refcursor to an explicit cursor- much easier for good programming style.

    B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •