PLS-00565的错误解决

今天被个小问题折腾了10分钟，哎
sys@MYORACLE> CREATE OR REPLACE TYPE NUMTABLETYPE is table of number
2 /
CREATE OR REPLACE TYPE NUMTABLETYPE is table of number
*
ERROR at line 1:
ORA-06545: PL/SQL: compilation error – compilation aborted
ORA-06550: line 0, column 0:
PLS-00565: NUMTABLETYPE must be completed as a potential REF target (object type)

反复的报错说PLS-00565的错误。

最后终于找到原因，create or replace并不是100%都能直接修改原有的type定义。

在这里我有两个type：
VARTABLETYPE就可以。
但是 NUMTABLETYPE就是不可以。 就因为正好遇到了两种相反的现象，才折腾了我10多分钟。

最终还是drop掉， 重新create就通过了。

sys@MYORACLE> drop type numtabletype;

Type dropped.

sys@MYORACLE>
sys@MYORACLE>
sys@MYORACLE> CREATE OR REPLACE TYPE NUMTABLETYPE is table of number;
2 /

Type created.

sys@MYORACLE> create or replace function str2numList( p_string in varchar2 ) return numTableType
2 as
3 v_str long default p_string || ‘,’;
4 v_n number;
begin
v_data numTableType := numTableType();
6 begin
loop
8 v_n := to_number(instr( v_str, ‘,’ ));
9 exit when (nvl(v_n, 0) = 0);
10
11 v_data.extend;
12 v_data( v_data.count ) := ltrim(rtrim(substr(v_str, 1, v_n-1)));
13 v_str := substr( v_str, v_n+1 );
14 end loop;
15
16 return v_data;
17 end;
18 /

Function created.
sys@MYORACLE> select a.column_value from table(str2numlist(’12,23′)) a;

COLUMN_VALUE
————
12
23
到此结束。

另外附上support.oracle.com的说明：
Error: PLS-565
Text: %s must be completed as a potential REF target (object type)
—————————————————————————
Cause: Incomplete library units that are potential targets of REF
dependencies must be completed so that they continue to
remain potential targets of REF dependencies (since there might
be library units with REF dependencies on this library unit).
Potential REF targets include complete and incomplete
object types. This error happened because an attempt was made to
complete a potential REF target as something other than a
potential REF target.
Action: Use another name for this library unit, or drop the original
incomplete library unit.

来源：如龙的博客