Oracle过程 [英] Oracle Procedure

问题描述

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                                                       

当我在游标中单独执行select语句时，我没有收到此错误. 请帮助我解决此问题.

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

推荐答案

在诸如您要创建的定义者权限存储过程中，在解析对象名称时，仅考虑直接授予该过程所有者的特权.不考虑通过角色授予的特权.我敢打赌，您的过程的所有者已通过角色而非直接授予了访问DBA_TAB_COLUMNS视图的权限.您可能需要让DBA直接向拥有您的过程的用户授予对DBA_TAB_COLUMNS的访问权限.

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.

您可以快速测试这是否确实是您遇到的问题.在SQL * Plus中，输入命令

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

SQL> set role none;

，然后运行您的SELECT语句.如果您遇到相同的权限错误，则说明问题是您通过角色获得了授权.禁用角色意味着您的交互式会话将以与存储过程相同的特权运行.

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过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！