列表以绑定SQL Developer中的变量 [英] list to bind variable in SQL Developer

问题描述

我有一个函数，该函数获取元素列表并将其放入表中，这样可以将列表用作绑定变量.一切正常，除非当我作为列表USER中的一个元素给出时，似乎Sql Developer进行了一些解析并导致了某些ORA-20001: comma-separated list invalid near T,USE错误.您是否知道是否可以为此添加解决方法，以便该功能也可以与USER元素一起使用?

I have a function that gets a list of elements and puts them into a table, in this way a list can be used as a bind variable. Everything works OK, except when I give as an element in the list USER, it seems that Sql Developer does some parsing and leads to some ORA-20001: comma-separated list invalid near T,USE error. Do you know if it's possible to add a workaround for this so the function will work also with 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;

这是要对其进行测试的查询:

And here is the query to test it:

select * from table(ui_util.comma_to_table(:myList))

如果为myList我放了TEST,SUPPORT,USERT，它就可以正常工作.如果将其更改为TEST,SUPPORT,USER，则会收到上述错误.有什么建议吗?

if for myList I put TEST,SUPPORT,USERT it works perfecly. If I change it to TEST,SUPPORT,USER I get the error mentioned above. Any suggestion?

推荐答案

问题是此AskTom文章通过基础 name_tokenize程序:

The problem is that the dbms_utility.comma_to_table procedure requires the elements of the list to be valid Oracle identifiers, though that isn't made clear in the docs really. This AskTom article refers to it though, via the underlying name_tokenize procedure:

请注意，您不必使用REAL对象名称(这些表和 程序不必存在)，但必须使用VALID对象 身份标识.如果您没有使用有效的对象标识符， NAME_TOKENIZE将引发错误.

Note that you do not have to use REAL object names (these tables and procedures do not have to exist) but you must use VALID object identifiers. If you do not use a valid object identifier, NAME_TOKENIZE will raise an error.

这与绑定或SQL Developer无关，这是数据库的限制.

It isn't to do with the binding or SQL Developer, it's a database restriction.

如果直接调用dbms_utility.comma_to_table过程，则会看到相同类型的错误:

You can see the same kind of error if you call the dbms_utility.comma_to_table procedure directly:

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"

如果您用逗号分隔的值为

You can't use this if your comma-separated values are reserved words or aren't allowed as identifiers for some other reason; starting with a number, for example. You'd get the same issue if the list contained TABLE or 42TAB. This isn't really what it is intended for, as Tom mentions.

您可以通过强制所有元素双引号来部分地克服限制，这可以使用replace来完成.然后允许使用这些示例中的任何一个:

You can partially get around the restrictions by forcing all the elements to be double-quoted, which you could do with a replace. and then any of those examples are allowed:

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时对其进行修改，然后从每个返回的值中删除双引号:

So for your code, modify iv_raw as you pass it across, and then remove the double-quotes from each returned value:

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个字符，因为这是对连引号的标识符的限制.

But you're still restricted to each element being 30 characters or less, since that is a restriction on even quoted identifiers.

这篇关于列表以绑定SQL Developer中的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！