APEX and temporary file table

Is saving files in www_flow_files  a good idea?

Where to store your temporary APEX files and www_flow_files issue

How many times did you have the need to store you files temporary in your APEX application? How many times have I heard try not to use www_flow_files table for storing your temporary files. 

There are two reasons: one being security thing and second one is files in local table are easier to manage. 

But does this mean that I have learned my lesson and listen to these tips? Of course not.

Situation: In the company we have an application that project managers use to retrieve some BI Publisher data.  How this can be achieved is a separate subject and I will not go into to many details. 

Bottom line was all of a sudden after two years in production only one combination of parameters for a single client was causing reports to fail. My first suspicion was that it must be data related. 

As everything led me to believe that is to be the only reason. Fortunately I always implement application that have a log capability and this one was no exception. This proved yet again to be crucial part in my analysis while I was trying to convince myself that it had to be data related error.

 

As odd as it may sound initially this unique constraint error didn't ring a bell but after a while it came to me that it must be related to famous www_flow_files view and ability to write data in there. 

Problem turned to be that some filenames and files we not being properly cleaned up in a run time and for this reason were left in this table which at the end caused my reporting procedure to fail due to unique constraint on FILENAME column.Thankfully this time workaround was easy. 

Log in as SYS and run this query:  

select * from  wwv_flow_file_objects$

As a result you will get all files that were left at some stage in temporary table but never were deleted. Once I deleted these there were no other problems with my reporting. 

Lesson to learn:

0. Always implement logging methods as they will save you heaps of time

1. Avoid temporary tables

2. If you have to use them - make sure you have mechanisms in place to properly manage deletion of the files at the run time

3. Make sure that you have a unique filenames no matter how many time reports are being called

 Hope this helps.

Cheers,

SL