Oracle Dynamic Update

[partner1220]

Hi,

I'm looking to write a dynamic update statement that reads from a table which I will load through an ETL tool. I'm not too comfortable with dynamic SQL.

Here's what I'm thinking from the source table:

Table_Name, Primary_Key, Field_Name, New_ValueCustomer, 123, First_Name, Bob
Address, 837, Line_1, 123 Main St

For example, the data above would generate and execute the following update statements:
UPDATE Customer SET First_name = 'Bob' WHERE Address_ID = 123
UPDATE Address SET Line_1 = '123 Main St' WHERE Customer_ID = 123

Now, the primary key gets tricky. 99% of the time, it is <TableName>_ID. Can I dynamically ID the primary key on the table? Otherwise, I can create another table that holds table/primary_key.

 

[pradeepkooray]

Yes. You can generate dynamic ID by creating oracle sequence. You can google for more information regarding oracle sequence.
further more, i think you need to write trigger to do oracle dynamic update. In oracle trigger, there is keyword new and old. So you can use the terms to solve you problem.

 

[pradeepkooray]

Oh! i forget to tell you, once you create a oracle sequence, you cab trigger it when insert a record or you can use oracle sequence "nextVal" in you stored procedure.