Multiple repeat insert

[Elizabeth King]

I need to repeat an insert as below

insert into NEW_TABLE (COLUMN_A, COLUMN_B)
select COLUMN_1, COLUMN_2 from OLD_TABLE where COLUMN_3 like 'variable%';

COLUMN_3 is VARCHAR2 type.

I want to loop through the above SQL with a different variable each time.
How do I create a list of the variables and set a for or while loop to
go through it? The list of the variable could be something like: aa, bb, cc, dd, ...

[Jeanne Boyarsky]

Elizabeth,
You can add the values to a collection like an ArrayList. Then you can iterate through the list and call the insert each time. Note that it is likely to be more efficient to set a batch insert statement within the loop and then make only one call to the database.

[Elizabeth King]

I want to do it in a PL/SQL script, not in Java. I'm using Oracle 10g.

[Jeanne Boyarsky]

Elizabeth,
I didn't catch that from the original post.

Have you looked into PL SQL arrays? Are you stuck on a specific part?

[Purushoth Thambu]

Since you are in 10g you can make use of regular expression function and get away with loops. Something like below

...
l_search_value = '^(' || replace(input_variable,',','%|') || '%)';
insert into NEW_TABLE (COLUMN_A, COLUMN_B)
select COLUMN_1, COLUMN_2 from OLD_TABLE where REGEXP_LIKE(COLUMN_3,l_search_value);
...

This will work as long as comma separated input_variable doesn't have spaces between commas.
[ February 27, 2007: Message edited by: Purushothaman Thambu ]

[Elizabeth King]

Where do I set my string values: aa,bb,cc,dd? Do you mean this:

l_search_value = '^(' || replace(aa,bb,cc,dd,',','%|') || '%)';

insert into NEW_TABLE (COLUMN_A, COLUMN_B)
select COLUMN_1, COLUMN_2 from OLD_TABLE where REGEXP_LIKE(COLUMN_3,l_search_value);

[Purushoth Thambu]

Yes, but you forget to give string quotes. It must be

l_search_value = '^(' || replace('aa,bb,cc,dd',',','%|') || '%)';