Thursday, February 04, 2016

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.

Monday, December 22, 2014

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;

Wednesday, August 14, 2013

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

Oracle Application Express

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 and encounter this error.  I have searched multiple times on the Internet and could not find any references to this.

Friday, March 22, 2013

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!!

Wednesday, December 05, 2012

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

As of Oracle SQL Developer Data Modeler (SDDM) version 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.

Thursday, November 01, 2012

Microsoft really does not like Oracle

Well I decided to install the "Oracle Developer Day VM" on my work Microsoft Windows XP Pro laptop to play around with Oracle 11g and APEX and got the following message.  Since this was my work laptop I of course aborted the installation.  Microsoft wins this one.

Friday, October 05, 2012

If Oracle's Cost Based Optimizer were a GPS, where would it take you?

Similar to the "If the CBO were a car, what would be it's bumper sticker?"