DML In A Select

It has long been a sort-of golden rule of Oracle database programming that Data Manipulation (Insert/Update/Delete) cannot be done by a SELECT statement. Note that I am not referring to DML statements that include a query to determine rows to process. Oracle Table Functions combined with Autonomous Transactions allow you to violate this rule as … Read more

EBR – Fix The Old Data

EBR is great for implementing application and data structures upgraded with no downtime. But what about the data itself? There are two main areas where data requires fixing: New Data From The Old Edition This is the data created in the old edition (ORA$BASE in these blog entries) that is not aware of the data … Read more

EBR – Fix The New Data

All of the previous blog entries in this series have focused on getting the application and data structures upgraded with no downtime. Now its time to look at the data itself. There are two main areas where data requires fixing: New Data From The Old Edition This is the data created in the old edition … Read more

EBR – Moving To The New Edition

There are a few ways to switch between editions. In this post I’ll present two of them. After Logon Triggers This option creates a trigger in the applicable schemas (or the whole database) that specifies the edition to choose. Here is an example: CREATE OR REPLACE TRIGGER SET_EDITION AFTER LOGON ON SCHEMA BEGIN EXECUTE IMMEDIATE … Read more

EBR – New Edition

In a previous post I introduced a situation where a column was added to a table and view and then a few seconds later a procedure that inserted records into the view was recreated. It all sounds harmless but during that “few seconds” anyone running the procedure would get an error. In this post I’ll … Read more

EBR – Data Dictionary

Now I’d like to cover some aspects of the Oracle Data Dictionary to explain some details about EBR. First lets check¬†what edition the current session is working in. SQL> EXEC DBMS_OUTPUT.PUT_LINE(SYS_CONTEXT(‘USERENV’, ‘SESSION_EDITION_NAME’)); ORA$BASE Next lets look at the objects we created in the last post. SQL> SELECT object_name, 2 object_type, 3 editionable, 4 edition_name 5 … Read more

EBR – Setup

This post introduces 2 key concepts of Edition Based Redefinition (EBR). Editions These are versions of database code and table structures. They allow multiple changes such as a new column in a table and all of the code that references it to be made available all at once to an application. Thus the application will … Read more

Edition Based Redefinition

This post is a simple example of the problem that can be solved by Oracle’s Edition Based Redefinition (EBR) feature. This example uses a table, a view, a stored procedure and an Oracle job. First up I’ll create the table and the view. CREATE TABLE demo ( ts timestamp NOT NULL ); CREATE OR REPLACE … Read more

Transaction Backout Does Not Do JSON Part 2

I did some digging on this issue and found the problem was caused when Transaction Backout executed the following statement which failed: SQL> INSERT INTO json 2 VALUES(EMPTY_CLOB()); INSERT INTO json * ERROR at line 1: ORA-02290: check constraint (DRH.KEEP_IT_REAL) violated At this point I wondered where the EMPTY_CLOB insert was coming from so I … Read more