Creating dynamic SQL queries in Oracle Trigger

Question

I am trying to write a trigger in Oracle 9i that will create some dynamic insert statements based on the values from the NEWROW that fired the trigger.

I need to pass the value of two fields from the new row to an existing function that returns a table of values (this function already exists and works fine). Basically the function splits the string into table rows.For example, if the two values I pass are 'Shirt,Sweater' and 'Red,Blue' I get back two tables. The first table has the values:

Shirt
Sweater

and the second table has the values

Red
Blue

Pretty simple. Once these two tables are returned I need to create some dynamic insert statements based on the values from these two table like so:

insert into mytable values ('Shirt','Red')
insert into mytable values ('Shirt','Blue')
insert into mytable values ('Sweater','Red')
insert into mytable values ('Sweater','Blue')

It is not necessary that I use the existing function. If the string can be split into arrays withing this trigger and have the same end results that also works.

Any help in this would be great appreciated. Thanks.

eaolson · Accepted Answer · 2011-05-27 03:23:30Z

3

You want all possible combinations of the array elements, in other words?

FOR i in 1 .. clothing.count LOOP
    FOR j in 1 .. colors.count LOOP
        INSERT INTO mytable VALUES ( clothing(i), colors(j) );
    END LOOP;
END LOOP;

answered May 27, 2011 at 3:23

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating dynamic SQL queries in Oracle Trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related