User Tools

Site Tools


renaming_a_column

How To Rename a Column

Consider the case where you want to rename a column of table EMPLOYEE, i.e. rename column DEPENDENTS to DESCENDENTS. Using SQL*Plus (or any other tool of your choice) execute the following statement:

SQL>  ALTER TABLE employee RENAME column dependents TO descendents;

Now you can use Lure to export the updated table source. Lure creates a patch file with the following content:

Generated Patch File After Renaming a Table Column

Lure detects that column DEPENDENTS is not present in the table EMPLOYEE any more and that a new column with name DESCENDENTS was added. This generated patch file will succeed in the sense that the resulting table structure will be correct. However in a more important sense the patch file will fail since you will loose the data that was in the column originally. Lure is not able to determine that the change was achieved by renaming of a column as this information is not stored as part of the table meta data within Oracle. See Table Changes and Automation for a more detailed discussion of this aspect.

For this reason users always have to review and possibly correct generated patch files. In this case the user has to correct the patch file by deleting the two ALTER statements and replacing it with the COLUMN RENAME statement as follows:

Corrected Patch File For Renamed Column

Before committing this modified patch file to the version control system you have to test and approved it first. Use Lure's Patch Test feature to test the patch file. Select the table source file EMPLOYEE.table.lsql and from the context menu select “Patch Test”. This yields the following output:

Patch Test Output For Column Rename

Lure finds the patch file with the smallest revision for the table (revision 59) and retrieves that version of the table source code from the version control system. Lure installs this older version of the table source file and then commences with normal deploy processing during with all patch files are installed, i.e. first patch file 59, then 60 and then finally patch file 61.

After execution of every patch file Lure first retrieves the latest source code for the table from the database and then compares that with the previous versions of the table source code as contained in the version control system. Only if this latest source code from the database matches any particular version of the corresponding source code in the version control system will Lure install the corresponding patch file.

In other words:

At the point that Lure installed patch file 61, it did so only because the table source code (as extracted from the database) matched the source code of the file EMPLOYEE.table.lsql at revision 61.

At any point in this process, if the latest source code matches the desired source code as present on the file system, Lure stops processing patch files for this table and logs that the outcome is successful.

However, if the table source code does not match any of the versions of the table source code as contained in the version control system, then Lure stops processing patch files for this table and logs that the outcome is unsuccessful.

In this particular case Lure logs that the patch files successfully updated the table.

Approving the Patch File

Before adding this latest patch file to the version control repository it has to be approved first.

Within Lure Stage right click on the patch file and from the context menu select “Approve Patch”. This will remove the ”-pending” part from the file extension.

You can now commit these latest changes (including the new patch file) to the version control system.

renaming_a_column.txt · Last modified: 2013/07/30 11:17 by admin

Page Tools