我有一个获取元素列表并将其放入表中的函数,通过这种方式,列表可用作绑定变量。一切正常,除非当我在列表中作为元素提供时USER
,似乎Sql Developer进行了一些解析并导致了一些ORA-20001: comma-separated list invalid near T,USE
错误。您是否知道是否可以为此添加解决方法,以便该功能也可以与USER
element一起使用?
功能:
FUNCTION comma_to_table(iv_raw IN VARCHAR2)
RETURN bind_tab_typ
PIPELINED
IS
ltab_lname dbms_utility.lname_array;
ln_len BINARY_INTEGER;
BEGIN
dbms_utility.comma_to_table(list => iv_raw
,tablen => ln_len
,tab => ltab_lname);
FOR i IN 1 .. ln_len LOOP
PIPE ROW (ltab_lname(i));
END LOOP;
END comma_to_table;
这是测试它的查询:
select * from table(ui_util.comma_to_table(:myList))
如果对于myList我TEST,SUPPORT,USERT
说它可以正常工作。如果我将其更改为TEST,SUPPORT,USER
上面提到的错误。有什么建议吗?
问题在于该dbms_utility.comma_to_table
过程要求列表中的元素是有效的Oracle标识符,尽管在文档中并没有明确说明。不过,此AskTom文章通过基本name_tokenize
过程对其进行了引用:
请注意,您不必使用REAL对象名(这些表和过程不必存在),但是必须使用VALID对象标识符。如果您没有使用有效的对象标识符,则NAME_TOKENIZE将引发错误。
这与绑定或SQL Developer无关,这是数据库的限制。
如果dbms_utility.comma_to_table
直接调用该过程,则会看到相同类型的错误:
declare
arr dbms_utility.uncl_array;
len binary_integer;
begin
dbms_utility.comma_to_table('USER', len, arr);
end;
/
Error report -
ORA-20001: comma-separated list invalid near R
ORA-06512: at "SYS.DBMS_UTILITY", line 236
ORA-06512: at "SYS.DBMS_UTILITY", line 256
ORA-06512: at line 5
或dbms_utility.name_tokenize
直接致电:
declare
a varchar2(30);
b varchar2(30);
c varchar2(30);
d varchar2(30);
e binary_integer;
begin
dbms_utility.name_tokenize('USER', a, b, c, d, e);
end;
/
Error report -
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 167
ORA-06512: at line 8
00931. 00000 - "missing identifier"
如果您用逗号分隔的值是保留字或由于其他原因而不能用作标识符,则不能使用此选项;例如,以数字开头。如果列表包含TABLE
或,则会遇到相同的问题42TAB
。正如汤姆(Tom)所提到的,这并不是它的真正目的。
您可以通过将所有元素都强制双引号来部分地解决这些限制,这可以使用来完成replace
。然后允许使用这些示例中的任何一个:
declare
arr dbms_utility.uncl_array;
len binary_integer;
begin
dbms_utility.comma_to_table('"USER","TABLE","42TAB"', len, arr);
end;
/
anonymous block completed
因此,对于您的代码,请在进行iv_raw
传递时进行修改,然后从每个返回的值中删除双引号:
FUNCTION comma_to_table(iv_raw IN VARCHAR2)
RETURN bind_tab_typ
PIPELINED
IS
ltab_lname dbms_utility.lname_array;
ln_len BINARY_INTEGER;
BEGIN
dbms_utility.comma_to_table(list => '"' || replace(iv_raw, ',', '","') || '"'
,tablen => ln_len
,tab => ltab_lname);
FOR i IN 1 .. ln_len LOOP
PIPE ROW (replace(ltab_lname(i), '"'));
END LOOP;
END comma_to_table;
然后工作:
select * from table(ui_util.comma_to_table('USER,TABLE,42T'));
COLUMN_VALUE
--------------------
USER
TABLE
42T
但是您仍然被限制为每个元素不能超过30个字符,因为这是对连引号的标识符的限制。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句