Create Column Names dynamically

Hello,

I have the following task that I am having problems with.

1. THIS IS DONE - Check for distinct MODEL_NAME value in TABLE 1 and see what MODEL_NAME value is missing in TABLE 2 (could be multiple).

Here is SQL - THIS WORKS

Code:

---

SELECT DISTINCT a.device_type
FROM logrelay.ra_device_type_hist@PRLDW a
WHERE a.device_type is not null
    MINUS
SELECT B.DEVICE_TYPE
FROM rkiss.device_models b

---

2. THIS IS DONE - Stored Prodecure that inserts new values into TABLE 2 with the following data, (id, dte, device_type) - this works as well.

3, HERE IS MY PROBLEM.

I have TABLE 3 where DEVICE_TYPE from TABLE 2 are column names. Currently I add column names manually, but would like to automate this process.

I would like to create a process that would check any new DEVICE_TYPE values in TABLE 2 that does not exist as column names in TABLE 3. If new DEVICE_TYPE value exists in TABLE 2, create new column name (as value of the new DEVICE_TYPE in TABLE 2) in TABLE 3.

I do not how to code this.

Any ideas?

Thanks

Roman

Try this:

Code:

---

Begin
  For X In (Select Device_Type
              From Rkiss.Device_Models
            Minus
            Select Column_Name
              From All_Tab_Columne
            Where Owner='RKISS'
              And Table_Name='TABLE3')
  Loop
    Execute Immediate 'Alter Table Table3 Add('
                  ||X.Device_Type||' Varchar2(10))';
  End Loop;
End;
/

---

;)

I will give it a try.

Thanks

Roman