Oracle Procedure

Question

create or replace function gen.sample_func(owner varchar2) return varchar2 
    as 
     data_t varchar2(10); 
      cursor cur is   select  data_type  from  SYS.DBA_TAB_COLUMNS;
      begin  
        open cur;
          dbms_output.put_line('Done'); 
        close cur; 
        return data_t;  
    end sample_func;

On compiling the above function i get the following error

Warning: compiled but with compilation errors
Errors for FUNCTION sample_func

LINE/COL                                                                        
--------------------------------------------------------------------------------
ERROR                                                                           
--------------------------------------------------------------------------------
4/8                                                                             
PLS-00201: identifier 'DBA_TAB_COLUMNS' must be declared                        

4/8                                                                             
PL/SQL: Item ignored                                                            

7/15                                                                            
PLS-00320: the declaration of the type of this expression is incomplete or malfo
rmed                                                                            

7/8                                                                             
PL/SQL: Statement ignored

I'm not getting this error when i execute the select statement alone in the cursor. Please help me to resolve this issue.

score 9 · Answer 1 · answered Mar 30 '11 at 08:53

9

Your user needs to be granted SELECT ON DBA_TAB_COLUMNS.

Note that granting through a role will not work - it needs to be a direct grant to the user for you to create a function/procedure.

answered Mar 30 '11 at 08:53

cagcowboy

30,012
11
69
93

score 8 · Accepted Answer · answered Mar 30 '11 at 08:52

In a definer's rights stored procedure such as the one you are creating, only privileges that are granted directly to the owner of the procedure are considered when resolving object names. Privileges granted through roles are not considered. I would wager that the owner of your procedure has been granted access to the DBA_TAB_COLUMNS view via a role rather than via a direct grant. You would need to ask the DBA to grant access to DBA_TAB_COLUMNS directly to the user that owns your procedure.

You can quickly test whether this is actually the problem you're experiencing. In SQL*Plus, enter the command

SQL> set role none;

and then run your SELECT statement. If you get the same permissions error, then the rpoblem is that you have the grant via a role. Disabling roles means that your interactive session is running with the same privileges that your stored procedure would run with.

Oracle Procedure

2 Answers2

Linked