CODEWITHSUNDEEP
sqloracle-databasetriggersdynamic-sql
SR8
SR8

Reputation: 53

Creating dynamic SQL queries in Oracle Trigger

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.

Upvotes: 1

Views: 782

Answers (1)

eaolson
eaolson

Reputation: 15094

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;

Upvotes: 3

Related Questions