CODEWITHSUNDEEP
oracle-databaseplsqlregexp-substr
Matin
Matin

Reputation: 117

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

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.

Upvotes: 2

Views: 2813

Answers (2)

MT0
MT0

Reputation: 167981

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;

Upvotes: 3

Gary_W
Gary_W

Reputation: 10360

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;
/

Upvotes: 1

Related Questions