Returning into clause and post statement triggers

If you are using RETURNING INTO clauses with DML statements and you are also using (post statement) triggers then you should be aware of the following caveat where the values of the RETURNING INTO do not match the column values.

To show this behavior, set up a small test case first. A table t1 with one column c1 with an after insert statement trigger, modifying the c1 column.

SQL> CREATE TABLE t1(c1 VARCHAR2(10));

Table created.

SQL>
SQL> CREATE TRIGGER t_ais_t1 AFTER INSERT ON t1
2  BEGIN
3    UPDATE t1
4    SET c1 = 'TRIGGER';
5  END;
6  /

Trigger created.

Insert a row in t1 and return the new value of c1 into bind variable c1.

SQL> VARIABLE c1 VARCHAR2(10)
SQL>
SQL> INSERT INTO t1(c1)VALUES('SQLPLUS') RETURNING c1 INTO :c1
2  /

1 row created.

And inspect the contents of the table and the value of the bind variable.

SQL>
SQL> SELECT c1 "column",
2    :c1 "bind"
3  FROM t1
4  /

column     bind
---------- ----------
TRIGGER    SQLPLUS

1 row selected.

As expected the value of column c1 is TRIGGER. The value is the bind variable is SQLPLUS. This value is set before the after statement trigger starts.

Although this code is not production code, you might notice this behavior in production code as well: I discovered this behavior hidden in a mutating table workaround.

APEX reports and SQL Developer

Both Kris Rice and Dimitri Gieles blogged about new APEX reports in SQL Developer 1.1.3 . Minor detail: those reports are build for Application Express version 3.0.1 , which is not released yet.

I installed the new version of SQL Developer, and tried to run the APEX reports (location in the reports tree: All reports, Data dictionary Reports, Application Express). Surprisingly, I did not get the message Application Express 3.0.1 or higher required. In a few cases I received an error: ORA-00904 invalid identifier.

To find the SQL behind the reports a simple copy and paste of the report did not work i.e select the Application report, right click and select copy. Select the User Defined Reports, right click and paste from, nothing happened. Even export of the report(s) did not work.

After some searching I found the report SQL hidden in a file: oracle.sqldeveloper.report.jar (location sqldeveloper\extentions directory). This .jar file includes an apex30.xml, this file contains all the APEX reports in XML format (you can unzip the XML file with an unzip utility). The next step is to import the apex30.xml and then you will see all the reports in the User Defined Reports branche as well.

Changing the SQL is left as an exercise to the reader... ;-)