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.
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;
COLUMN_VALUE is the automatically generated column name for all table collection expressions for a user-defined collections and VARRAYs (of primitive types).update line_cap set cap_up = list_of_improvs(1) where id in list_of_improvs;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.
INTOsome variable within a PL/SQL block.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"select regexp_substr(improv,'[^,]+', 1, level) BULK COLLECT INTO my_table from dual ...'[^,]+'does not allow for NULL list elements and will return the wrong element if a NULL is encountered. See here for more info: stackoverflow.com/a/31464699/2543416. Please use this form instead (11g and later):regexp_substr(improv,'(.*?)(,|$)', 1, level, NULL, 1)