Writing Code for Mutiple Columns
It's quick enough to cut and paste code for ten or even twenty columns, but when you are working with tables that can contain over a hundred columns it is better to automate it. I have found a good method for writing code that has to be applied to each column in a large table: I use Excel.
View the column in SqlDeveloper. Copy the first two columns from the table definition into Excel so that the first column contains the column name and the second the data type. You can add column titles above as in the example below.
| A | B | |
|---|---|---|
| 1 | Column Name | Data Type |
| 2 | PAY_PROPOSAL_ID | NUMBER(15,0) |
| 3 | OBJECT_VERSION_NUMBER | NUMBER(9,0) |
| 4 | ASSIGNMENT_ID | NUMBER(10,0) |
In cell C2 I added this formula because I needed to know the datatype without the length:
=IF(ISERROR(FIND("(",B2)),B2,LEFT(B2,FIND("(",B2)-1))
In cell D2 I created a second formula. I was needing a bit of code that would do an IF THEN test for each column and write come code which included the data type. Here is my formula:
=" if p_new." & A2 & " is not null then" & CHAR(10)
& " insert into my_column_log (table_log_id, column_name, datatype, NEW_" & C2 & ") " & CHAR(10)
& " values (lv_table_log_id, '" & A2 & "', '" & LEFT(B2,1) & "', p_new." & A2 & ");" & CHAR(10)
& " end if;"
Now you can simply copy cells C2 and D2 down the spreadsheet.
Finally, cut and paste the cells in the D column into your code editor. If you want to change your code (and search and replace won'd suffice) you can change your Excel formula and through the magic of cut and paste you can quickly re-engineer your code.