Getting "Comma-Separated List Near 'Xx.Yy' Invalid" with Dbms_Utility.Comma_To_Table

getting comma-separated list near 'xx.yy' invalid with dbms_utility.comma_to_table

See How to split comma delimited string into rows

1. REGEXP_SUBSTR approach

SQL> WITH DATA AS(
  2  SELECT 'ac_Abc.88,ac_Abc.99,ac_Abc.77' str FROM dual)
  3  SELECT regexp_substr(str,'[^,]+',1,level) str
  4  FROM DATA
  5    CONNECT BY regexp_substr(str, '[^,]+', 1, level) IS NOT NULL
  6  /

STR
-----------------------------
ac_Abc.88
ac_Abc.99
ac_Abc.77

SQL>

2. XML approach

SQL> SELECT EXTRACT (VALUE (d), '//row/text()').getstringval () str
  2  FROM
  3    (SELECT XMLTYPE ( '<rows><row>'
  4      || REPLACE ('ac_Abc.88,ac_Abc.99,ac_Abc.77', ',', '</row><row>')
  5      || '</row></rows>' ) AS xmlval
  6    FROM DUAL
  7    ) x,
  8    TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, '/rows/row'))) d
  9  /

STR
--------------------
ac_Abc.88
ac_Abc.99
ac_Abc.77

3. Table function

SQL> CREATE TYPE test_type
  2  AS
  3    TABLE OF VARCHAR2(100)
  4  /

Type created.

SQL>
SQL> CREATE OR REPLACE
  2  FUNCTION comma_to_table(
  3      p_list IN VARCHAR2)
  4    RETURN test_type
  5  AS
  6    l_string VARCHAR2(32767) := p_list || ',';
  7    l_comma_index PLS_INTEGER;
  8    l_index PLS_INTEGER := 1;
  9    l_tab test_type     := test_type();
 10  BEGIN
 11    LOOP
 12      l_comma_index := INSTR(l_string, ',', l_index);
 13      EXIT
 14    WHEN l_comma_index = 0;
 15      l_tab.EXTEND;
 16      l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
 17      l_index            := l_comma_index                           + 1;
 18    END LOOP;
 19    RETURN l_tab;
 20  END comma_to_table;
 21  /

Function created.

SQL> sho err
No errors.
SQL>
SQL> SELECT * FROM TABLE(comma_to_table('ac_Abc.88,ac_Abc.99,ac_Abc.77'))
  2  /

COLUMN_VALUE
--------------------------------------------------------------------------------
ac_Abc.88
ac_Abc.99
ac_Abc.77

SQL>

4. Pipelined Function

SQL> CREATE OR REPLACE
  2    FUNCTION comma_to_table(
  3        p_list IN VARCHAR2)
  4      RETURN test_type PIPELINED
  5    AS
  6      l_string LONG := p_list || ',';
  7      l_comma_index PLS_INTEGER;
  8      l_index PLS_INTEGER := 1;
  9    BEGIN
 10      LOOP
 11        l_comma_index := INSTR(l_string, ',', l_index);
 12        EXIT
 13      WHEN l_comma_index = 0;
 14        PIPE ROW ( SUBSTR(l_string, l_index, l_comma_index - l_index) );
 15        l_index := l_comma_index                           + 1;
 16      END LOOP;
 17      RETURN;
 18    END comma_to_table;
 19    /

Function created.

SQL> sho err
No errors.
SQL>
SQL> SELECT * FROM TABLE(comma_to_table('ac_Abc.88,ac_Abc.99,ac_Abc.77'))
  2  /

COLUMN_VALUE
--------------------------------------------------------------------------------
ac_Abc.88
ac_Abc.99
ac_Abc.77

comma separated list invalid near.. with comma_to_table

One way is to use CONNECT BY to effectively loop through the string elements. If you run just the query you'll see how this works. The regular expression allows for NULL list elements should they occur.

insert into TEST(col_a) 
select regexp_substr('522,33-23,125,658,25,12-500', '(.*?)(,|$)', 1, level, null, 1)
from dual
connect by level <= regexp_count('522,33-23,125,658,25,12-500', ',')+1

parse comma separated string in oracle stored procedure

You can use the following techniques :

REGEXP_SUBSTR approach

SQL> WITH DATA AS(
  2      SELECT q'["06b7930e-293f-6604-e053-2176a8c09440","06b7930e-293f-6604-e053-2176a8c09440"]' str
  3    FROM dual)
  4  SELECT regexp_substr(str,'[^,]+',1,level) str
  5    FROM DATA
  6  CONNECT BY regexp_substr(str, '[^,]+', 1, level) IS NOT NULL
  7  /

STR
-----------------------------------------------------------------------------
"06b7930e-293f-6604-e053-2176a8c09440"
"06b7930e-293f-6604-e053-2176a8c09440"

SQL>

I have answered a similar question here getting "comma-separated list near 'xx.yy' invalid" with dbms_utility.comma_to_table using other approaches like XML, table function, pipelined function.

Remove duplicates from comma separated list with regexp

Based on this link to split a comma separated value into rows, I splitted the string into rows, kept the position of the first occurence, made a distinct a reaggregated the values

with test_string as ( 
select 1 as id,
 'contract, clause 1, Subsection 1.1, contract, clause 1, Subsection 1.2, paragraph (a), contract, clause 1, Subsection 1.2, paragraph (b), contract, clause 2' val 
from dual)
select id, listagg(word,', ') WITHIN GROUP (order by position) FROM (
select distinct id, first_value(position) over ( partition by word order by position ) position, word from (
select 
  distinct t.id,
  levels.column_value as position,
  trim(regexp_substr(t.val, '[^,]+', 1, levels.column_value))  as word
from 
  test_string t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.val, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
  )
) GROUP BY id

And if you are not interested in keeping the order

with test_string as ( 
select 1 as id,
 'contract, clause 1, Subsection 1.1, contract, clause 1, Subsection 1.2, paragraph (a), contract, clause 1, Subsection 1.2, paragraph (b), contract, clause 2' val 
from dual)
select id, listagg(word,', ') WITHIN GROUP (order by 1) FROM (
select 
  distinct t.id,
  trim(regexp_substr(t.val, '[^,]+', 1, levels.column_value))  as word
from 
  test_string t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.val, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
) GROUP BY id

Pass value stored in a PL/SQL variable into an IN clause

Another way is to make use of Nested tables in conjunction with TABLE operator

create type nt_vr_arr_list is table of number;

DECLARE
  vr_arr_list  nt_vr_arr_list := nt_vr_arr_list(100, 200, 330);
BEGIN
  FOR cx IN (SELECT id, name
               FROM tbl_demo
              WHERE id IN (SELECT COLUMN_VALUE FROM TABLE(vr_arr_list))) LOOP
    DBMS_OUTPUT.put_line('ID: ' || cx.id || ' Name: ' || cx.name);
  END LOOP;
END;

How to get all combinations (ordered sampling without replacement) in regex

I wouldn't use Regex for this, as e.g. the requirements "have to be unique" and "have to be in ascending order" can't really be expressed with a regular expression (at least I can't think of a way to do that).

As you also need to have an expression that is identical in Postgres and Oracle, I would create a function that checks such a list and then hide the DBMS specific implementation in that function.

For Postgres I would use its array handling features to implement that function:

create or replace function is_valid(p_input text)
  returns boolean
as
$$
  select coalesce(array_agg(x order by x) = string_to_array(p_input, ','), false)
  from (
    select distinct x
    from unnest(string_to_array(p_input,',')) as t(x)
    where x ~ '^[0-9]+$' -- only numbers
  ) t
  where x::int between 1 and 4 -- the cast is safe as the inner query only returns valid numbers
$$
language sql;

The inner query returns all (distinct) elements from the input list as individual numbers. The outer query then aggregates that back for values in the desired range and numeric order. If that result isn't the same as the input, the input isn't valid.

Then with the following sample data:

with sample_data (input) as (
  values 
    ('1'),
    ('1,2'),
    ('1,3'), 
    ('1,4'), 
    ('1,2,3'), 
    ('1,2,4'),
    ('foo'),
    ('1aa,1234'),
    ('1,,2,33,444,')
)
select input, is_valid(input)
from sample_data;

It will return:

input        | is_valid
-------------+---------
1            | true    
1,2          | true    
1,3          | true    
1,4          | true    
1,2,3        | true    
1,2,4        | true    
foo          | false   
1aa,1234     | false   
1,,2,33,444, | false   

If you want to use the same function in Postgres and Oracle you probably need to use returns integer in Postgres as Oracle still doesn't support a boolean data type in SQL

---

Oracle's string processing functions are less powerful than Postgres' functions (e.g. no string_to_array or unnest), but you can probably implement a similar logic in PL/SQL as well (albeit more complicated)

Redirect parent page from AJAX loaded tab

Your question isn't totally clear in regards to what you mean by "tab"

Maybe it would work for you to use

top.location.href='$url'

---

Related Topics