[PLSQL] pls-00231:function 'function-name' may not be used in SQL

原文：http://www.cnblogs.com/fangwenyu/archive/2010/05/05/1727699.html

比如说我有一个Package，它的specification如下，

CREATEORREPLACE  PACKAGE PACK_TEST AS 
  -- Return a list of the package name in current schema  function GET_PACKAGE_LIST return T_V30_TABLE PIPELINED;      -- Return the package specifiction (procedure/function) in the given package of current schema  function GET_PACKAGE_SPECIFICATION(V_PACKAGE_IN invarchar2) return T_V4000_TABLE PIPELINED;      -- The following two procedures are to ease calling from outside databae, like in .NET code.  procedure GET_PACKAGE_LIST(V_RESULT_IN_OUT in OUT SYS_REFCURSOR);   procedure GET_PACKAGE_SPECIFICATION(V_PACKAGE_IN invarchar2, V_RESULT_IN_OUT in OUT SYS_REFCURSOR);       -- Help function   -- (It must be pulbic to be used inside the SQL query inside function GET_PACKAGE_SPECIFICATION,   -- otherwise, PLS-00231 will arise!)  function ARG_TABLE_TO_STRING(V_ARG_TABLE in T_V100_TABLE) returnvarchar2;    END PACK_TEST;

 

 

现在我想通过执行一条SQL语句，得到这个Package里面包含哪些Function和Procedure， 类似于SQL*Plus的命令 DESC <package_name>

 

FUNCTION  GET_PACKAGE_LIST () RETURN T_V30_TABLE                                                                                                                        FUNCTION  ARG_TABLE_TO_STRING (V_ARG_TABLE IN T_V100_TABLE) RETURNVARCHAR2                                                                                                  FUNCTION  GET_PACKAGE_SPECIFICATION (V_PACKAGE_IN INVARCHAR2) RETURN T_V4000_TABLE                                                                     PROCEDURE  GET_PACKAGE_LIST (V_RESULT_IN_OUT IN/OUT REF CURSOR)                                                                                                                                                                               PROCEDURE  GET_PACKAGE_SPECIFICATION (V_PACKAGE_IN INVARCHAR2, V_RESULT_IN_OUT IN/OUT REF CURSOR)

 

 

其实思路很简单，只要从数据字典视图里面选择需要的数据，然后进行相应的格式化就OK了。下面就是GET_PACKAGE_SPECIFICATION的代码，

 

function GET_PACKAGE_SPECIFICATION(V_PACKAGE_IN invarchar2) return T_V4000_TABLE PIPELINED AS   begin       for REC in       (         select            T.SUBPROGRAM_TYPE ||''||            T.SUBPROGRAM_NAME ||''||            ARG_TABLE_TO_STRING(cast(collect(T.ARGUMENT) as T_V100_TABLE)) ||''||            t.return_caluse  as subprogram_spec         from         (         select              case FIRST_VALUE(POSITION)over(partition by SUBPROGRAM_ID orderby sequence) when0then'FUNCTION 'else'PROCEDURE 'endas SUBPROGRAM_TYPE,              OBJECT_NAMEas SUBPROGRAM_NAME,              casewhen ARGUMENT_NAME isnotnullthen ARGUMENT_NAME ||''|| IN_OUT ||''|| (case DATA_TYPE when'TABLE'then TYPE_NAME else DATA_TYPE end) endas ARGUMENT,              FIRST_VALUE(case POSITION when0then'RETURN '|| (case DATA_TYPE when'TABLE'then TYPE_NAME else DATA_TYPE end) end) over(partition by subprogram_id orderby sequence) as return_caluse         from  USER_ARGUMENTS         where PACKAGE_NAME = V_PACKAGE_IN         ) t         groupby          T.SUBPROGRAM_TYPE,          T.SUBPROGRAM_NAME,          T.RETURN_CALUSE      )      LOOP        pipe row (rec.subprogram_spec);      end LOOP;      return;   end GET_PACKAGE_SPECIFICATION;

 

我是从USER_ARGUMENTS里面进行数据查询，然后通过分析函数FIRST_VALUE, 10g新增加的collect聚集函数来进行“格式化”。

 

这里面遇到的一个问题是通过调用function ARG_TABLE_TO_STRING 来把collect函数返回的collection变成字符串。也就是ARG_TABLE_TO_STRING

 

function ARG_TABLE_TO_STRING(V_ARG_TABLE in T_V100_TABLE) returnvarchar2   is      V_RETURN varchar2(4000) :='(';          -- In case the string length is larger than 4000    STRING_BUFFER_TOO_SMALL exception;     pragma EXCEPTION_INIT(STRING_BUFFER_TOO_SMALL, -06502);   begin          for IDX in1..V_ARG_TABLE.count     LOOP       v_return := v_return || V_ARG_TABLE(IDX) ||', ';     end LOOP;
    v_return :=RTRIM(v_return, ', ') ||')';          return v_return;   EXCEPTION      when STRING_BUFFER_TOO_SMALL then          V_RETURN :=  SUBSTR(V_RETURN, 1, 4000-5) ||' ...)';          return v_return;   end ARG_TABLE_TO_STRING;

 

 

因为这个函数只是在包的内部调用，因此理应该把这个function私有化，但是这样的话在compile的时候会遇到PLS-00231 的问题。关于这个问题，在这里有很好的讨论解释。

造成这个问题的原因是因为我是在SQL语句里面调用这个PL/SQL函数的，因为SQL语句和PL/SQL函数是通过不同的engine（SQL  engine 和 PL/SQL engine) 来解析执行的，因此在SQL语句中执行的FUNCTION必须是public的，这样SQL engine才能访问的。 解决这个问题的方法要么就是将function 公有化，即放在package specification中，或者把function的调用改成PL/SQL代码，不是放在SQL语句中。所以要具体情况具体分析，这里我是选择了前者。

 

 

调用函数GET_PACKAGE_SPECIFICATION就很简单了，直接用一条SQL语句就ＯＫ了，

select*fromtable(PACK_TEST.GET_PACKAGE_SPECIFICATION('PACK_TEST'));

 

 

总结下：

　１）　USR_ARGUMENTS

   2)     Table function / Pipelined

   3)     COLLECT function

   4)     PLS-00231