Archive for the ‘bfile’ tag
The trick for making a BFILE read-write or at least read-delete
A while back, I demonstrated how you can read a file directory from SQL. Here’s how you can delete an external file referenced by a BFILE
column. There’s only one problem with this level of the architecture, there’s no rollback. However, it does let you delete the external file when you want to delete the column value.
A better widget for capturing the fully qualified external file name
I’ve updated the how to return a fully qualified BFILE
name from a function. The new function lets you select the fully qualified file name from a BFILE
column, like:
SELECT get_canonical_local_bfilename(item_photo) AS file_name FROM item WHERE item_id = 1021; |
In my test environment, it returns:
FILE_NAME ------------------------------------------ C:\JavaDev\BFileFramework\HarryPotter1.png |
A bold step to the fully qualified BFILE file name
I’m working on a framework for synchronizing BFILE
column values with the file system. It turns the read-only BFILE
datatype into a pseudo read-write datatype. The related page shows you how to get the information from the database catalog.
Staying with the concept of pages, you can find the code here …
A wrapper function to DBMS_LOB.FILEGETNAME procedure
It got tiresome writing the logic for getting a BFILE
file name. So, I wrote a wrapper function that lets you return the physical file name for any table. I did it by leveraging NDS against a dynamic anonymous PL/SQL block.
Oracle Database LOBs In-depth
Last month I presented the Oracle Database LOBs In-depth at the Utah Oracle User’s Group (UTOUG) Training Days. You can find the slides and sample programs here. The presentation illustrates how the transaction context works and how to access, maintain, and manage LOBs.