即使强制转换后，PlSQL数据类型仍然无效 [英] PlSQL Invalid data type even after casting why

问题描述

当我这样做

SQL> select cast( csv_convert.to_text( 'ABC,ACD,DE' ) as t_tbl_of_text ) from dual;
select cast( csv_convert.to_text( 'ABC,ACD,DE' ) as t_tbl_of_text ) from dual
             *
**ERROR at line 1:
ORA-00902: invalid datatype**


create or replace package csv_convert is
   type t_tbl_of_text is table of varchar2(32767);
    function to_text( p_str in varchar2 )
           return t_tbl_of_text;
end csv_convert;
/

Package created.


create or replace package body csv_convert as
    function to_text(p_str in varchar2 ) return t_tbl_of_text is
           l_data    t_tbl_of_text := t_tbl_of_text();
           l_str     varchar2(32767) default p_str || ',';
           l_n       number;
           begin
                   loop
                           l_n := instr( l_str, ',' );
                           exit when (nvl(l_n,0) = 0);
                           l_data.extend;
                           l_data( l_data.count ) := trim(substr(l_str,1,l_n-1));
                           l_str := substr( l_str, l_n+1 );
                   end loop;
           return l_data;
           end to_text;
end csv_convert;
  /

推荐答案

包中定义的PL/SQL类型对SQL语句不可见:它们是纯PLSQL构造，SQL语言无法直接访问它们.使用VARCHAR2可以观察到相同的现象:SQL可以定义/访问VARCHAR2最多4000字节，而PL/SQL可以处理最多32767字节的字符串.

PL/SQL types defined in package are invisible to SQL statements: they are pure PLSQL constructs and the SQL language can't access them directly. The same phenomenon can be observed with VARCHAR2: SQL can define/access VARCHAR2 up to 4000 bytes while PL/SQL will be able to deal with strings up to 32767 bytes.

解决方法是使用SQL类型，例如VARRAY的嵌套表:

The workaround is to use SQL types, such as nested tables of VARRAYs:

SQL> CREATE TYPE obj_tbl_of_text IS TABLE of varchar2(4000);
  2  /

Type created

SQL> CREATE OR REPLACE PACKAGE csv_convert IS
  2     FUNCTION to_text(p_str IN VARCHAR2) RETURN obj_tbl_of_text;
  3  END csv_convert;
  4  /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY csv_convert AS
  2     FUNCTION to_text(p_str IN VARCHAR2) RETURN obj_tbl_of_text IS
  3        l_data obj_tbl_of_text := obj_tbl_of_text();
  4        l_str  VARCHAR2(32767) DEFAULT p_str || ',';
  5        l_n    NUMBER;
  6     BEGIN
  7        LOOP
  8           l_n := instr(l_str, ',');
  9           EXIT WHEN(nvl(l_n, 0) = 0);
 10           l_data.EXTEND;
 11           l_data(l_data.COUNT) := TRIM(substr(l_str, 1, l_n - 1));
 12           l_str := substr(l_str, l_n + 1);
 13        END LOOP;
 14        RETURN l_data;
 15     END to_text;
 16  END csv_convert;
 17  /

Package body created

现在可以直接在SQL中调用pacakge(即使不使用CAST):

Calling the pacakge directly in SQL is now possible (even without CAST):

SQL> select csv_convert.to_text( 'ABC,ACD,DE' ) from dual;

CSV_CONVERT.TO_TEXT('ABC,ACD,DE')
--------------------------------------------------------------------------------
OBJ_TBL_OF_TEXT('ABC', 'ACD', 'DE')

这篇关于即使强制转换后，PlSQL数据类型仍然无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！