list to bind variable in SQL Developer

Question

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?

The function:

  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))

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?

what type is the return 'bind_tab_typ'?

José Molero
– José Molero

2016-10-06 10:31:52 +00:00
Commented Oct 6, 2016 at 10:31 — José Molero
– José Molero, Commented Oct 6, 2016 at 10:31

Alex Poole · Accepted Answer · 2014-04-01 14:11:18Z

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:

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.

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

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

Or by calling dbms_utility.name_tokenize directly:

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.

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

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;

Then this works:

select * from table(ui_util.comma_to_table('USER,TABLE,42T'));

COLUMN_VALUE
--------------------
USER
TABLE
42T

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

Collectives™ on Stack Overflow

list to bind variable in SQL Developer

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related