MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘bfile’ tag

The trick for making a BFILE read-write or at least read-delete

without comments

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.

You can find the blog page here …

Written by maclochlainn

July 26th, 2008 at 11:22 pm

A better widget for capturing the fully qualified external file name

without comments

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

Written by maclochlainn

July 26th, 2008 at 10:36 pm

Posted in Oracle

Tagged with ,

A bold step to the fully qualified BFILE file name

without comments

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 …

Written by maclochlainn

July 23rd, 2008 at 6:33 am

A wrapper function to DBMS_LOB.FILEGETNAME procedure

without comments

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.

It’s here …

Written by maclochlainn

July 22nd, 2008 at 4:21 am

Oracle Database LOBs In-depth

without comments

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.

Written by maclochlainn

April 22nd, 2008 at 3:37 am