Just testing to see if this gets posted on OraNA.

Please remove my blog from OraNA

This is a test blog post to see if it is getting aggregated to the OraNA news aggregator.  If it does, please post how to get a blog removed from OraNA.

Still wondering how to get my blog off of the OraNA news aggregator.  Anyone know?

Recover Modified Oracle APEX Page

I was working on a page in an Oracle APEX application and wanted to rollback all the changes I made in the last 30 minutes on one particular page without losing all the changes I made prior to then.  I had no export of the application, so I used a very cool feature of Oracle and APEX as documented in Peter Raganitsch's blog way back in 2011.

I basically did a page export as of 30 minutes ago.  Since APEX runs in the Oracle database and flashback query is enabled in the database, when the APEX Builder was querying the APEX application tables in the database for the page it was exporting, the Builder did a flashback query as of 30 minutes ago.  Once the page export was completed, I did an import of the page and the page was rolled back to 30 minutes ago.  Very nice.

Rows to String

Just a note about a powerful way to convert a column of values in a query into a coma separated list in a text string:

select 'LISTAGG' fx,
       length(listagg(table_name, ',')
             within group (order by table_name) ),
       listagg(table_name, ',') within group (order by table_name)
  from user_tables;

Old Oracle APEX "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" Error

Oracle Application Express 3.1.0.00.32

I added some display items to a large page and all of sudden I started getting "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" when I attempted to save changes.

Too make a long debug process short, I discovered that this was an error generated internally by APEX code not in any application code.  Because I had too many items on the page I assume that APEX is assembling an INSERT statement in a local PL/SQL variable somewhere internal to APEX and the INSERT text string is too large in length and cannot be assigned to the local variable.  If I cut the number of items down to 118 items the pages runs fine.

I also noticed a very small message at the bottom of the items region of the page definition that says, "Item count exceeds maximum of 100."  It looks informational and does not stop you in any way.  It would be nice if that were an actual alert that popped up and said don't do this or else you will get a runtime ORA-06502 error upon submitting the page.

I am posting this for my recollection and all others who are working in an old version of APEX like Application Express 3.1.0.00.32 and encounter this error.  I have searched multiple times on the Internet and could not find any references to this.

Oracle SQL Developer: Switching between tabs.

There is a nice feature of Oracle SQL Developer that allows once to assign numbers to tabs and then via a keystrokes one can switch between the tabs like Windows ALT-Tab does for applications in the Windows OS.

Assign a number like 1 to a tab by pressing SHIFT-ALT-1 while that is the active tab. You will see a miniature "1" at the beginning of the tab. Then whenever you want to go to that tab press ALT-1.  Nice!!

Using External Tables in a Reltional Model in Oracle SQL Developer Data Modeler

As of Oracle SQL Developer Data Modeler (SDDM) version 3.1.00.700 you cannot use external tables in Relational Models.  When you import external tables from the Oracle data dictionary into SDDM you get the external tables in the Physical Model, but you cannot use them in a Relational Model.

I have figured out a work around until this is supported in SDDM.  In SDDM create a view which SELECTs each column from the external table.  You can then use the view in Relational Models.  You can name the view in SDDM the same as the external table or differently.  In my scenario I created the view with the same name as the external table and will not create the view in the database.  I am just creating the view in SDDM to represent the external table in relation models.