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?"

Monday, September 17, 2012

What would you add to Oracle? response

Lewis Cummingham posed the question, "What would you add to Oracle?" in his blog.  He purposed the ability to support multiple languages within the database.  Thinking completely out of the box, I would enlarge that concept to include support of other database vendors' environments within an Oracle database.  Instead of just allowing users to hop from Oracle over to another database vendor's database via a "gateway", why not just put that other vendor's database inside of an Oracle database.  Oracle could experiment with MySql first because they own it.

I know that this is "Pie in the sky" type of stuff, but you never know.