| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: plsql and dba_constraints
On Tue, 06 Jul 1999 16:10:47 -0500, Kent Eilers <kent.eilers_at_pca.state.mn.us>
wrote:
>I can run the following fine from the command prompt:
>
> select 'alter table '||table_name||' ENABLE constraint
>'||constraint_name as sql_str
> from dba_constraints
> where r_constraint_name
> IN (select constraint_name from dba_constraints where
>lower(table_name) = as_table)
> and lower(table_name) <> as_table;
>
>but get the error: "sys.dba_constraints must be declared"
>when I put this into a procedure as a cursor:
>
> CURSOR lc_enable IS
> select 'alter table '||table_name||' ENABLE constraint
>'||constraint_name as sql_str
> from dba_constraints
> where r_constraint_name
> IN (select constraint_name from dba_constraints where
>lower(table_name) = as_table)
> and lower(table_name) <> as_table;
>
>Is it forbidden to access the dba tables within a procedure?
No, you can access them all you want, as long as you have the privilege to do so.
<quote>
roles are never enabled during the compilation of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privilege from a role and hence won't be able to do it in a procedure.
You probably have the privilege to do what you are trying to do in the procedure via a role. Grant the privilege directly to the owner of the procedure and it'll work.
grant select on component to <OWNER>
</quote>
hope this helps.
chris.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |