Trent's Tech Tips

If you are on a slow connection like me, you may find how much of a pain it is deploying apps that house some BI publisher report templates. It only takes a few templates before your app export becomes 10MB+ in size. Perhaps in a future release, report templates will be treated in the same way as application images, in that you can add them as a script to supporting objects so that you don't need to export the templates each and every deployment. If you haven't seen, apex_util has a procedure to download report queries, so by using that procedure coupled with storing your files in your own maintained table, you can avoid the issue of massive application exports. First create a table to store the templates: create table rtf_template( code varchar2(25) PRIMARY KEY , layout BLOB); / Then, add your template file to the table. We will need two application items (or 1 will do if you identify the layout with exactly the same name as the query). So I've created: ...

The first thing you need is a page with a file type item, and a submit button. When you submit the page, the selected file is uploaded into the apex_application_files (or wwv_flow_files) view. The actual blob contents of the file is uploaded into the column BLOB_CONTENT but since a CSV file is just plain text, it made sense to me to convert that file to a CLOB, so that you can perform regular string functions on the data. The first reference to converting a blob to a clob, was in the OTN forums  - confirmed by referring to the oracle documentation . After converting it to a clob, it is simply a matter of looping over each row of the data, and then populating into an apex collection (or some other temp table). This is an intermediary step just so you can review the data before committing the changes. Finally, you just move the data from the collection into the table. So the code for copying the blob into a clob and populating the collection can be seen below. declare --variabl...