Sys Tables In Stored Procedure

Ahamed Rafeeque

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

558383

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

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

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

I really misunderstood about DBA .

Forstmann thanks for helping me.

damorgan

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.