Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    8

    Question Unanswered: cursor based on dba_tab_columns

    i was trying to put together a stored procedure that makes use of a cursor on dba_tab_columns and ran into an odd problem. the following compiles w/ errors...

    -------------------------------------------------------------------
    create or replace procedure sp1 as

    cursor ptid_tables is select * from dba_tab_columns;

    begin
    return;
    end;
    -------------------------------------------------------------------

    but the following compiles fine...

    -------------------------------------------------------------------
    create or replace procedure sp1 as

    cursor ptid_tables is select * from mrpa99; --user table

    begin
    return;
    end;
    -------------------------------------------------------------------

    any ideas why i can't create a cursor on dba_tab_columns? to make things more strange, i can create a cursor on dba_tab_columns if it is not within a procedure. for example, the following executes w/out errors...

    -------------------------------------------------------------------
    declare cursor ptid_tables is select * from dba_tab_columns;

    begin
    return;
    end;
    -------------------------------------------------------------------

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Priviledges granted via a role is NOT available in a stored procedure. Simply stated, you do NOT have access to the view DBA_TAB_COLS. You could try ALL_TAB_COLS and see if it has the info you need.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2005
    Posts
    29
    Is the permission to access dba_tab_columns via a role or is it a direct select grant on dba_tab_columns to your user?

  4. #4
    Join Date
    Sep 2002
    Posts
    8
    perfect! all_tab_columns does the trick.

Posting Permissions

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