SQL Developer and BLOBs

SQL Developer is a very complementary tool to Apex.
Although, you can do all basic SQL stuff via the SQL Workshop in Application Express, sometimes you need a more powerful tool like SQL Developer, especially when you really need to ‘develop’ in SQL and PL/SQL.

One of the SQL features I like about SQL Developer is the possibility to look at the content of a BLOB column.

This feature was very useful during my quest on the content of the flows-files table. And I don’t think other tools like TOAD, SQL Navigator, PL/SQL Developer has those possibilities …

Just by clicking on the blob-column in the result grid, a popup window is opened where you can specify that you want to see the content as text or image.

When this column contains plain text, there is no problem at all to have a look at the content. The following example opens a tab-delimited file.

SQL Developer can even visualize binary images (gif, jpeg, bmp). Very handy …

Apex: Housekeeping Your Flow Files

In my previous blog, I explained the problem we encountered with flows_files.wwv_flow_file_objects$ aka APEX_APPLICATION_FILES.

I did some further investigations on the content of that table to 1) detect the ‘big size spenders’ and to 2) see whether or not there exist some orphan entries. I also wanted to know if we could write a delete statement to remove those obsolete records.

My research was not that easy …and not really satisfying…



This table is really used for all kinds of uploads of files into an Apex environment.

Apex uses different types of repositories:

  • an export repository
  • an image repository
  • a css repository
  • a text data load repository (accessible via Utilities)
  • workspace import/export repository (via Internal)
  • SQL Workshop script repository

The main columns I concentrated on are:

By executing following query (as user FLOWS_020200), we can see which (uploaded) files exist and how much space they are taking.

SELECT o.security_group_id,
o.flow_id,
o.filename,
o.doc_size,
o.mime_type,
o.file_type,
o.content_type
FROM wwv_flow_file_objects$ o
ORDER BY 1,2

By sorting on DOC_SIZE DESC, we could easily isolate those files that took a lot of space in the database. Based on the type, the developer was asked to clean up his Apex repositories.

For a lot of entries, we could not that easy map to an existing repository.
We noticed very often that the value of FLOW_ID equals to 0.

At first we thought that we could remove all those records. When we uploaded a file via our custom application and did not delete it from the APEX_APPLICATION_FILES view, the entry remained in the files-table with flow_id = 0.

On the other hand when browsing the data in the table, we discovered based on the filename and mime type that workspace images and uploaded css-files are also stored that way.

And in some cases, not only the flow_id = 0, but also the security_group_id = 0.

Based on the filename and the content we could for almost 100% certainty say that all those entries were obsolete and could be deleted. We used the following statement:

DELETE FROM wwv_flow_file_objects$
WHERE security_group_id = 0
AND flow_id = 0
AND file_type IS NULL

The cleaning of other entries we did on a record-by-record base.

Lessons learned

  • when uploading a document/an image into your custom application ALWAYS delete the files in the apex_application_files when you have transferred it to your own table

  • check on a regular base your oracle alert file
  • clear the different repositories within Apex when you do not longer need that file !
  • when deleting manually records (check, double check and take a backup)

Apex: flows_files.wwv_flow_file_objects$ aka APEX_APPLICATION_FILES

End of last year, we had a strange problem in our test environment while testing an internal application for maintaining our library of IT-related books.

When adding a new book title, we got from time to time a “page not found error” with a reference to wwv_flow.accept in the URL. Strangely enough, sometimes it worked, sometimes it didn’t ! And this same page always worked in the development environment.

At first we thought it was a time-out problem of the http-server. We checked the Apache log files, but didn’t find any trace of an error.

The day after another colleague had the same problem when he tried to import an apex-application in another workspace.

So, the problem wasn’t application specific anymore, it happened also when doing standard apex-functionality.

We gradually understood that it had something to do with file-handling.

When processing the page that creates a book-entry in our library application, one of the steps is the upload of an image with the book-cover. We detected that when uploading a small image (e.g. an icon) the page was treated as it should, with big files it caused a problem.

So, we decided to run an audit of the database. Therefore we used Plato a free, handy tool for auditing and tuning an oracle database, developed by one of our Cronos colleagues — btw. you can donwnload the utility from dba-village –.

After running the script, we noticed the following warning in red !


So the problem was that the tablespace in which the schema FLOWS_FILES was created could not allocate extra space because the Maximum Size for auto extension (set to 200 Mb) was almost reached.

If we would have verified at day-1 the standard Oracle Alert file located on the database server, the problem would be clear immediately. Afterwards we noticed that for every attempt that caused load-error, there is an entry in that log file saying: “ORA-1691: unable to extend lobsegment flows_files.SYS_LOB0000053172C00017$$ in tablespace HTMLDB_FILES.”

To resolve the problem we could have decided to extent the tablespace, and problem would be fixed; but before doing that we wondered why that tablespace had reached those limits.

FLOWS_FILES is one of the 3 standard schema’s that are created when installing Apex. This schema contains only 1 table: WWV_FLOW_FILE_OBJECTS$. On top of this table are the views/synonyms HTMLDB_APPLICATION_FILES and APEX_APPLICATION_FILES created in THE FLOWS_020200 schema. This table contains ALL files that are uploaded in Apex, be it via a custom application, or be it via the Apex Development Environment.

We investigated the WWV_FLOW_FILE_OBJECTS$ table and we noticed that the table contained many and many entries that shouldn’t be there anymore.

The main reason was that some of the developers forgot to delete the (intermediate) files from APEX_APPLICATION_FILES once it was copied into their target table. So, we isolated the obsolete entries and deleted those records directly via SQL.

Another reason was that some people didn’t have the habitude to clean the Export Repository from time to time.

One of the coming days I will blog on the query we used to examine that table and on our findings related to the content of the table.