I have a table with a column named column_names
and for each row it has a list of column names separated by a comma, for example:
table | column_names |
---|---|
some | DEPOSITS_COUNT,DEPOSITS |
I want to iterate over each named string in column names, here is what I have tried:
DECLARE
deposits VARCHAR(255);
BEGIN
FOR DEPO IN ( SELECT REGEXP_SUBSTR (
column_names, '[^,] ') "REGEXPR_SUBSTR" INTO deposits
FROM REFERENCE_TEST )
LOOP
DBMS_OUTPUT.PUT_LINE (DEPO.deposits);
END LOOP;
END;
I used DECLARE
because I kept getting an error that DEPO
was not declaring something.
The error I get:
Error report -
ORA-06550: line 8, column 34:
PLS-00302: component 'DEPOSITS' must be declared
ORA-06550: line 8, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
CodePudding user response:
Don't use SELECT ... INTO ...
inside a cursor:
BEGIN
FOR DEPO IN (
SELECT REGEXP_SUBSTR (column_names, '[^,] ') AS REGEXPR_SUBSTR
FROM REFERENCE_TEST
)
LOOP
DBMS_OUTPUT.PUT_LINE (DEPO.REGEXPR_SUBSTR);
END LOOP;
END;
/
Which will get you the first value in each row.
So, for the sample data:
CREATE TABLE REFERENCE_TEST ("TABLE", column_names) AS
SELECT 'some', 'DEPOSITS_COUNT,DEPOSITS' FROM DUAL UNION ALL
SELECT 'numbers', 'ONE,TWO,THREE' FROM DUAL;
It outputs:
DEPOSITS_COUNT
ONE
If you want all the values then you need to use a row-generator and split the string. You can do that using a recursive sub-query factoring clause and simple string functions (which is much faster than regular expressions):
BEGIN
FOR DEPO IN (
WITH bounds (column_names, spos, epos) AS (
SELECT column_names,
1,
INSTR(column_names, ',', 1)
FROM reference_test
UNION ALL
SELECT column_names,
epos 1,
INSTR(column_names, ',', epos 1)
FROM bounds
WHERE epos > 0
)
SEARCH DEPTH FIRST BY column_names SET order_id
SELECT CASE epos
WHEN 0
THEN SUBSTR(column_names, spos)
ELSE SUBSTR(column_names, spos, epos - spos)
END AS name
FROM bounds
)
LOOP
DBMS_OUTPUT.PUT_LINE (DEPO.name);
END LOOP;
END;
/
Which outputs:
DEPOSITS_COUNT
DEPOSITS
ONE
TWO
THREE