SQL & PL/SQL

I have a problem where i want the get the file name and path of a bfile stored in a table ... im creating a small app in vb.net , I found an answer on the net to solve the problem by creating a function to get the file name or path of a bfile .... but I can't get it to work im using Oracle 10xe here is the demo function I got from net can anyone explain how to use it .. I get errors when i try to create the function .. ive never used function before and im completely new to bfiles.


First you must create a function to extract the directory object for a BFILE column. Note that when the column is NULL, then NULL is returned.

SQL> CREATE FUNCTION get_dir_name (bf BFILE) RETURN VARCHAR2 IS
2 DIR_ALIAS VARCHAR2(255);
3 FILE_NAME VARCHAR2(255);
4 BEGIN
5 IF bf is NULL
6 THEN
7 RETURN NULL;
8 ELSE
9 DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
10 RETURN dir_alias;
11 END IF;
12 END;
13 /



Next create a function to extract the file name for the BFILE column.

SQL> CREATE FUNCTION get_file_name (bf BFILE) RETURN VARCHAR2 is
2 dir_alias VARCHAR2(255);
3 file_name VARCHAR2(255);
4 BEGIN
5 IF bf is NULL
6 THEN
7 RETURN NULL;
8 ELSE
9 DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
10 RETURN file_name;
11 END IF;
12 END;
13 /