Forum Stats

  • 3,816,034 Users
  • 2,259,132 Discussions
  • 7,893,366 Comments

Discussions

Sys Tables In Stored Procedure

Ahamed Rafeeque
Ahamed Rafeeque Member Posts: 154 Bronze Badge
edited Jan 31, 2010 8:17AM in SQL & PL/SQL
How can I use Sys Table in Stored Procedure.

I want to use DBA_TAB_COLOUMNS, in stored procedure.

When I used this table in stored procedure I got an error

ORA - 00942 Table or View Does not exists.

Thanks,
Ahamed
Tagged:

Answers

  • 558383
    558383 Member Posts: 6,961 Silver Crown
    From http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#i1006224

    >
    Note:
    To create without errors (to compile the procedure or package successfully) requires the following additional privileges:

    *

    The owner of the procedure or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.
    *

    The owner cannot obtain required privileges through roles.

    >

    You need to run
    grant select on dba_tab_columns to <owner of procedure>;
  • Ahamed Rafeeque
    Ahamed Rafeeque Member Posts: 154 Bronze Badge
    Thanks Forstmann, I overcome that problem with the help of your reply.

    But the think that confused me is that I granted DBA to that user.

    Is it require SYSDBA, to access SYS table?

    Thanks,
    Ahamed
  • 558383
    558383 Member Posts: 6,961 Silver Crown
    DBA is a role and roles are not enabled by default in stored procedures: you need to give the direct grant on the objects.
  • Ahamed Rafeeque
    Ahamed Rafeeque Member Posts: 154 Bronze Badge
    I really misunderstood about DBA .

    Forstmann thanks for helping me.
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    This is the worst possible solution you could possibly have used. Unless you wish to totally compromise the system's security revoke DBA and grant the very specific, and very limited, SELECT on the view.
This discussion has been closed.