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.
Tuesday 19 March 2013
Saturday 16 March 2013
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:
to says not to export or the opposite one:
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_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.
Thursday 14 March 2013
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
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.
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.
Wednesday 13 March 2013
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.
After entering the image path and repeating the neccessary translation steps everything worked well.
Sunday 3 March 2013
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.
Subscribe to:
Posts (Atom)