Oracle SQL: select regexp_substr, "into clause is expected"

Question

I am trying to split a comma delimited string using regexp_substr, but get "into clause is expected" error:

 select regexp_substr(improv,'[^,]+', 1, level) from dual
 connect by regexp_substr(improv, '[^,]+', 1, level) is not null;

"improv" is a varchar(100) variable, and I am running the above statement inside a PLSQL block.

The error message is clear enough: you need to store the result of your query `INTO` some variable within a PL/SQL block. — Aleksej, Jan 18 '18 at 12:55
@Aleksej: Aha. I declared the table variable type and changed the code to `select regexp_substr(improv,'[^,]+', 1, level) from dual connect by regexp_substr(improv, '[^,]+', 1, level) is not null into my_table;` but received compilation error "sql command not properly ended" between "null" and "into" — Matin, Jan 18 '18 at 13:20
Use `select regexp_substr(improv,'[^,]+', 1, level) BULK COLLECT INTO my_table from dual ...` — Wernfried Domscheit, Jan 18 '18 at 13:21
The regex of the form `'[^,]+'` does not allow for NULL list elements and will return the wrong element if a NULL is encountered. See here for more info: https://stackoverflow.com/a/31464699/2543416. Please use this form instead (11g and later): `regexp_substr(improv,'(.*?)(,|$)', 1, level, NULL, 1)` — Gary_W, Jan 18 '18 at 14:44

MT0 · Accepted Answer · 2018-01-19T13:37:15.333

In PL/SQL you need to output the SQl query INTO a variable.

However, since this query is going to generate multiple rows you probably want to use BULK COLLECT INTO rather than just INTO and to put the output into a user-defined collection or a VARRAY (which SYS.ODCIVARCHAR2LIST is an example of. Note: you cannot use the MEMBER OF operator with a VARRAY):

DECLARE
  list_of_improvs SYS.ODCIVARCHAR2LIST;
BEGIN
  SELECT regexp_substr(improv,'[^,]+', 1, level)
  BULK COLLECT INTO list_of_improvs
  FROM   DUAL
  CONNECT BY regexp_substr(improv, '[^,]+', 1, level) is not null;
END;
/

Update:

In response to your comment - you can use it like this (although it is unclear what you are trying to achieve so I have just put your code into a snippet without trying to work out what you intend it to do):

DECLARE
  list_of_improvs SYS.ODCIVARCHAR2LIST;
BEGIN
  SELECT regexp_substr(improv,'[^,]+', 1, level)
  BULK COLLECT INTO list_of_improvs
  FROM   DUAL
  CONNECT BY regexp_substr(improv, '[^,]+', 1, level) is not null;

  FOR i IN 1 .. list_of_improvs.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( improvs(i) );
  END LOOP;

  update line_cap
    set   cap_up = list_of_improvs(1)
    where id IN ( SELECT Column_Value
                  FROM TABLE( list_of_improvs ) );
END;
/

You can't use IN directly with a collection or VARRAY and need to use a TABLE() collection expression in a nested query to get the values out.

If you are using a user-defined SQL collection - i.e. defined with a statement like this:

CREATE TYPE StringList IS TABLE OF VARCHAR2(4000);

Then you can use the MEMBER OF operator:

DECLARE
  list_of_improvs StringList;
BEGIN
  -- as above

  update line_cap
    set   cap_up = list_of_improvs(1)
    where id MEMBER OF list_of_improvs;
END;
/

But you cannot use the MEMBER OF operator with VARRAYs (like SYS.ODCIVARCHAR2LIST).

However, you don't need PL/SQL for that (and eliminate costly context switches between the PL/SQL and SQL execution scopes) and could just use a MERGE statement something like:

MERGE INTO line_cap dst
USING (
  SELECT MIN( value ) KEEP ( DENSE_RANK FIRST ORDER BY ROWNUM ) OVER () AS first_value,
         value
  FROM   (
    SELECT regexp_substr(improv,'[^,]+', 1, level) AS value
    FROM   DUAL
    CONNECT BY regexp_substr(improv, '[^,]+', 1, level) is not null
  )
) src
ON ( src.value = dst.id )
WHEN MATCHED THEN
  UPDATE
    SET cap_up = first_value;

@MTO: may I ask what will be the column name of the table declared as SYS.ODCIVARCHAR2LIST? — Matin, Jan 18 '18 at 13:39
@user1777530 `COLUMN_VALUE` is the automatically generated column name for all table collection expressions for a user-defined collections and `VARRAY`s (of primitive types). — MT0, Jan 18 '18 at 13:43
@MTO: how can I get numbers out of this? I tried ODCINUMBERLIST instead of ODCIVARCHAR2LIST, but still get the "inconsistent datatypes: expected NUMBER got SYS.ODCINUMBERLIST" error for list_of_improvs when I tried this update statement. "id" is a number: `update line_cap set cap_up = list_of_improvs(1) where id in list_of_improvs;` — Matin, Jan 18 '18 at 14:52

score 1 · Answer 2 · answered Jan 18 '18 at 15:51

I put this here instead of a comment to MT0's answer, as comments don't work for formatting. Anyway here's a complete example using MT0's answer to load an array and then looping through it to display the contents. This will show you how to access the contents of the list. Give the credit to MT0 for the answer to your original question.

DECLARE
  list_of_improvs SYS.ODCIVARCHAR2LIST;
  i number;
BEGIN
  SELECT regexp_substr('1,2,3,4,5','(.*?)(,|$)', 1, level, NULL, 1)
  BULK COLLECT INTO list_of_improvs
  FROM   dual
  CONNECT BY level <= regexp_count('1,2,3,4,5', ',') + 1;

  i := list_of_improvs.FIRST;  -- Get first element of array
  while i is not null LOOP
    DBMS_OUTPUT.PUT_LINE(list_of_improvs(i));
    i := list_of_improvs.NEXT(i);  -- Get next element of array
  END LOOP;
END;
/

`BULK COLLECT INTO` will always generate a collection (or VARRAY) with the first index at 1 and incrementing by 1 so you could simplify the loop to `FOR i IN 1 .. list_of_improvs.COUNT LOOP` and eliminate the `i` variable and the `FIRST` and `NEXT(i)` assignments. However, if the input is a PL/SQL associative array which could contain sparse (or non-numeric) indexes then using `FIRST` and `NEXT(i)` is definitely the method to use. — MT0, Jan 19 '18 at 13:07

Oracle SQL: select regexp_substr, "into clause is expected"

2 Answers2