Oracle if updating column

Rated 3.93/5 based on 811 customer reviews

So, if you accidentally make a column invisible and correct this by changing it to visible, the column order changes.

Therefore, if the application uses “SELECT *” or “INSERT” without column names, they might break!

Tip: By default SQL*Plus DESCRIBE will not show the invisible columns in a table.

If you want to see the invisible columns in DESCRIBE, use SET COLINVISIBLE ON.

Once a table’s column is set to invisible, the following key statements will not work for the invisible column: Invisible columns will still available for indexing and such indexes can be used for cost-based optimizer purposes.

Don’t confuse an invisible index with an index on invisible column, as they are entirely different concepts. There are not many reasons why you would suddenly make a column invisible, but one situation that comes to mind is where you might want to test the waters before dropping the column from table – to figure out if something breaks or someone yells.

Oracle provides an option to mark a column as UNUSED before you DROP, and do ALTER TABLE … Once you mark a column as UNUSED, there is no going back to undo the action.

AFTER INSERT or UPDATE or DELETE: It specifies that the trigger will be fired after the INSERT or UPDATE or DELETE operation is executed.Indexing Invisible Columns: Indexing on invisible columns is similar to indexing on a generic visible column.Let’s check if Oracle is able to use the INDEX created above, named “HIDDENT_COL_TEST_INDX”.2) “conditional update” The instead-of trigger checks to see if the PK columns are being altered, and only includes them if appropriate Nothing crashed – but we totally corrupted the child table.This last example is actually also contained in the standard Oracle docs.

Leave a Reply