+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Oracle (https://0day.red/Forum-Oracle)
+---- Thread: Use Oracle PL/SQL For Loop to iterate through comma delimited string (/Thread-Use-Oracle-PL-SQL-For-Loop-to-iterate-through-comma-delimited-string)
Use Oracle PL/SQL For Loop to iterate through comma delimited string - emmyt - 07-31-2023
I am writing a piece of code that would need to iterate on the content of a string, each values being separated with a `,`.
e.g. I have my `elements`
v_list_pak_like varchar2(4000) := 'PEBO,PTGC,PTTL,PTOP,PTA';
How can I get it into an Array / Cursor to iterate on it in my loop?
---
for x in (elements)
loop
-- do my stuff
end loop;
I am looking for the very simple way, if possible avoiding to declare associative arrays.
**Would it be possible to create a function** that would return something usable as an input for a `for` loop (opposite to the while that could be used like in
[To see links please register here]
)?Many thanks in advance.
RE: Use Oracle PL/SQL For Loop to iterate through comma delimited string - psilocins890019 - 07-31-2023
You could do it easily in pure **SQL**. there are multiple ways of doing it, see **[Split comma delimited string into rows in Oracle][1]**
However, if you really want to do it in **PL/SQL**, then you could do it as:
SQL> set serveroutput on
SQL> DECLARE
2 str VARCHAR2(100) := 'PEBO,PTGC,PTTL,PTOP,PTA';
3 BEGIN
4 FOR i IN
5 (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) l
6 FROM dual
7 CONNECT BY LEVEL <= regexp_count(str, ',')+1
8 )
9 LOOP
10 dbms_output.put_line(i.l);
11 END LOOP;
12 END;
13 /
PEBO
PTGC
PTTL
PTOP
PTA
PL/SQL procedure successfully completed.
SQL>
[1]:
[To see links please register here]
RE: Use Oracle PL/SQL For Loop to iterate through comma delimited string - yeomanries151863 - 07-31-2023
Thanks to Lalit great instructions, I am able to create a function that I can call from my `for` loop:
Create a type and function
-----------
CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
FUNCTION comma_to_table(p_list IN VARCHAR2)
RETURN t_my_list
AS
l_string VARCHAR2(32767) := p_list || ',';
l_comma_index PLS_INTEGER;
l_index PLS_INTEGER := 1;
l_tab t_my_list := t_my_list();
BEGIN
LOOP
l_comma_index := INSTR(l_string, ',', l_index);
EXIT
WHEN l_comma_index = 0;
l_tab.EXTEND;
l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_comma_index - l_index));
l_index := l_comma_index + 1;
END LOOP;
RETURN l_tab;
END comma_to_table;
/
Then how to call it in my _for_ loop:
-----------
declare
v_list_pak_like varchar2(4000) := 'PEBO,PTGC,PTTL,PTOP,PTA';
begin
FOR x IN (select * from (table(comma_to_table(v_list_pak_like)) ) )
loop
dbms_output.put_line(x.COLUMN_VALUE);
end loop;
end;
/
Notice the default name `COLUMN_VALUE` given by Oracle that is necessary for the use I want to make of the result.
Result as expected:
---------
PEBO
PTGC
PTTL
PTOP
PTA
RE: Use Oracle PL/SQL For Loop to iterate through comma delimited string - earnestrvolaov - 07-31-2023
declare
type array_type is table of VARCHAR2(255) NOT NULL;
my_array array_type := array_type('aaa','bbb','ccc');
begin
for i in my_array.first..my_array.last loop
dbms_output.put_line( my_array(i) );
end loop;
end;
The first line defines a table of any type you want.
then declare a variable of that type and give its values with a constructor.
Then loop over the result from the first index to the last.
Edit - A solution that handles true string:
declare
str VARCHAR2(1024) := 'aaa,bbb,ccc';
type ARRAY_TYPE is table of VARCHAR2(255) NOT NULL;
my_array ARRAY_TYPE;
begin
SELECT REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) BULK COLLECT INTO my_array
FROM DUAL
CONNECT BY REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) IS NOT NULL;
for i in my_array.first..my_array.last loop
dbms_output.put_line( my_array(I));
end loop;
end;