Hello All:

I have a new ETL process I need to develop. I think the way to solve this is a PL/SQL program. I have
some books I am using for reference, but I thought I would post to the board for some help.
I have 3 tables: Table1, Table2, and WorkTable.

a. The data in WorkTable is used to populate Table1 and Table2.
b. Table1 has 5 columns(col1-col5). Column col1 is populated with a sequence number(surrogate key).
c. Table2 has 10 columns (col1-col10). Column col1 is populated with a squence number(surrogate key).
d. Table1.col5 is populated with the data value from Table2.col1.
e. Table2.col10 is populated with the data value from Table1.col1.
f. All other columns in Table1 and Table2 are populated with data from the WorkTable.

I think I need to have a PL/SQL program that reads the data (cursor) from the WorkTable and
then insert a record in Table1 and save the value of Table1.col1 to a variable to be inserted
into Table2.col10 and insert a record into Table2 and save the value of Table2.col1 to a variable
to be inserted into Table1.col5.

That is, the data in the WorkTable updates Table1 and Table2, with the surrogate keys in Table1 and
Table2 "cross-referenced" back to each table.

Thanks in advance for any assistance and remember I am a novice at PL/SQL programming.

Dan

Oracle LazyDBA home page