Select List with Dynamic LOV and ORA-06502: PL/SQL: numeric or value error: character string buffer too small

If you need to create a dynamic SQL for a select list or any other type of a LOV, your query should not exceed 4000 characters. In case it does, you will get the "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" error. That is the internal limitation of the APEX table storing the item attribute information. In case you can't rewrite your SQL to satisfy this limitation, you may use the package I created for that purpose. It is quite simple and it does the following:

1. It will use the apex_collection package to parse the query and crate a collection

2. It will query the collection and use a pipelined function to return a simple LOV (SELECT d, r FROM table)

You can also use the get_long_dynamic_query function in the package to generate your dynamic SQL. The example at apex.oracle.com I provided extends the query with "dummy" conditions in order to make it long enough and prove the concept.

Formating and Exporting a Report Column

Sometimes you need to format a report column and if you need to do that in the report SQL, you will be faced with a problem while exporting the report - the formatted values will be exported 1:1 and you will see those ugly html tags among the exported data. In early days of APEX people recommended using two columns - one to display in a report and hide it while exporting and one hidden column used for exports only instead. You would then use a condition like this:

apex_application.g_excel_format = FALSE

to says not to export or the opposite one:

apex_application.g_excel_format = TRUE

to say to export.This solution may work with a standard report but it can't be used efficiently with Interactive Reports.

Just in case you didn't know, you can solve that problem easily. See this example at apex.oracle.com. There, I am using a function to format a string and use the value of the current request to see if the report is simply getting rendered on the page or exported. If the report is exported, I will not use the formatting tags and return the values as it was parsed.

APEX Collections and Joins

APEX Collections is one of the best features of APEX. There are situations where solutions wouldn't be possible if this feature would not be available. However, the collections have some limitations. One of the limitations is if you need to store number values and use those values to join a collection with other tables. The consequences may be that you receive something like

ORA-01722: invalid number

The strange thing with this error is that it may occur sporadic and you can't realy understand why it happens. If you operate on different systems (development, testing, production) you may receive this error on one of the instances and never on the other one. I asked Patrick Wolf about the reasons and he answered that this has to do with the way the cost based optimizer works. If it uses a different plan from the one you would expect, it may find non numeric values in the same column comming from a different collection. In that case the solution is to use the numeric columns in the collection (n0xx) for storing numeric values used in later joins.

Upgrade to APEX 4.2.1.00.08 - First Impressions

Today one of my customers upgraded their APEX 4.0 to 4.2.1.00.08. The whole process was completed without any issues. After the upgrade there was only one problem we could notice. Our translated applications didn't work for the other languages then the primary language. Trying to run any of the translated applications would result in an internal error. The reason was that the image path wasn't there for any of the tranlations:

After entering the image path and repeating the neccessary translation steps everything worked well.

Reading a file from an URL and storinig it as a BLOB

In this example at apex.oracle.com you can se how you can use an URL (which normaly provides a save/download dialog for saving or opening a document) to store the document directly in your own table as a BLOB. Unfortunatelly the ACL settings at apex.oracle.com do not allow to get this working there and the working example is just a fake, showing only how this should normaly work.