Home » SQL & PL/SQL » SQL & PL/SQL » Extract the file name + path of Bfile
| Extract the file name + path of Bfile [message #163702] |
Sat, 18 March 2006 06:37  |
Unkle77
Messages: 4
Registered: March 2006
|
Junior Member |
|
|
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.
Any help would be great
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.
CREATE FUNCTION get_dir_name (bf BFILE) RETURN VARCHAR2 IS
DIR_ALIAS VARCHAR2(255);
FILE_NAME VARCHAR2(255);
BEGIN
IF bf is NULL
THEN
RETURN NULL;
ELSE
DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
RETURN dir_alias;
END IF;
END;
Next create a function to extract the file name for the BFILE column.
CREATE FUNCTION get_file_name (bf BFILE) RETURN VARCHAR2 is
dir_alias VARCHAR2(255);
file_name VARCHAR2(255);
BEGIN
IF bf is NULL
THEN
RETURN NULL;
ELSE
DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
RETURN file_name;
END IF;
END; |
|
|
|
|
|
|
|
| Re: Extract the file name + path of Bfile [message #163718 is a reply to message #163707] |
Sat, 18 March 2006 15:06  |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Apparently you already have an object by the same name as one of the functions that you are trying to create. So, you either need to drop that object, making sure that you do not need it first, or supply a different name for the new function. Or, if you have simply run the code multiple times, then just use "create or replace" to overwrite the old function with the new one. I have used the functions in the example below, plus an additional function by Tom Kyte to get the actual operating system path, not just the name of the Oracle directory object.
-- data for testing:
scott@ORA92> CREATE TABLE test_tab
2 (id NUMBER,
3 test_col BFILE)
4 /
Table created.
scott@ORA92> CREATE OR REPLACE DIRECTORY my_oracle AS 'c:\oracle'
2 /
Directory created.
scott@ORA92> CREATE OR REPLACE DIRECTORY my_temp AS 'c:\temp'
2 /
Directory created.
scott@ORA92> INSERT INTO test_tab VALUES (1, BFILENAME ('MY_ORACLE', 'master~1.pdf'))
2 /
1 row created.
scott@ORA92> INSERT INTO test_tab VALUES (2, BFILENAME ('MY_TEMP', 'test1.txt'))
2 /
1 row created.
-- functions you provided:
scott@ORA92> CREATE OR REPLACE 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 /
Function created.
scott@ORA92>
scott@ORA92> CREATE OR REPLACE 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 /
Function created.
-- packaged function by Tom Kyte:
scott@ORA92> create or replace package bfile_pkg
2 as
3 function get_filename( p_bfile in bfile ) return varchar2;
4 end;
5 /
Package created.
scott@ORA92> create or replace package body bfile_pkg
2 as
3 type array is table of varchar2(4000) index by varchar2(30);
4 g_array array;
5
6 function get_filename( p_bfile in bfile ) return varchar2
7 as
8 l_dir varchar2(4000);
9 l_fname varchar2(4000);
10 l_path varchar2(4000);
11 begin
12 dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
13 return g_array(l_dir) || '\' || l_fname;
14 exception
15 when no_data_found
16 then
17 select directory_path
18 into l_path
19 from all_directories
20 where directory_name = l_dir;
21
22 g_array(l_dir) := rtrim(l_path,'\');
23 return g_array(l_dir) || '\' || l_fname;
24 end;
25
26 end;
27 /
Package body created.
-- select statement that uses the functions:
scott@ORA92> COLUMN oracle_dir FORMAT A15
scott@ORA92> COLUMN file_name FORMAT A15
scott@ORA92> COLUMN full_path FORMAT A30
scott@ORA92> SELECT id,
2 get_dir_name (test_col) AS oracle_dir,
3 get_file_name (test_col) AS file_name,
4 bfile_pkg.get_filename (test_col) AS full_path
5 FROM test_tab
6 /
ID ORACLE_DIR FILE_NAME FULL_PATH
---------- --------------- --------------- ------------------------------
1 MY_ORACLE master~1.pdf c:\oracle\master~1.pdf
2 MY_TEMP test1.txt c:\temp\test1.txt
scott@ORA92>
|
|
|
|
|
|
| Re: Extract the file name + path of Bfile [message #163761 is a reply to message #163756] |
Sun, 19 March 2006 13:02 |
|
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Just add an if clause to Tom's function to check for null, as in the other two functions.
scott@ORA92> create or replace package body bfile_pkg
2 as
3 type array is table of varchar2(4000) index by varchar2(30);
4 g_array array;
5
6 function get_filename( p_bfile in bfile ) return varchar2
7 as
8 l_dir varchar2(4000);
9 l_fname varchar2(4000);
10 l_path varchar2(4000);
11 begin
12 IF p_bfile IS NULL
13 THEN RETURN NULL;
14 ELSE
15 dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
16 return g_array(l_dir) || '\' || l_fname;
17 END IF;
18 exception
19 when no_data_found
20 then
21 select directory_path
22 into l_path
23 from all_directories
24 where directory_name = l_dir;
25
26 g_array(l_dir) := rtrim(l_path,'\');
27 return g_array(l_dir) || '\' || l_fname;
28 end;
29
30 end;
31 /
Package body created.
scott@ORA92> INSERT INTO test_tab VALUES (3, null)
2 /
1 row created.
scott@ORA92> COLUMN oracle_dir FORMAT A15
scott@ORA92> COLUMN file_name FORMAT A15
scott@ORA92> COLUMN full_path FORMAT A30
scott@ORA92> SELECT id,
2 get_dir_name (test_col) AS oracle_dir,
3 get_file_name (test_col) AS file_name,
4 bfile_pkg.get_filename (test_col) AS full_path
5 FROM test_tab
6 /
ID ORACLE_DIR FILE_NAME FULL_PATH
---------- --------------- --------------- ------------------------------
1 MY_ORACLE master~1.pdf c:\oracle\master~1.pdf
2 MY_TEMP test1.txt c:\temp\test1.txt
3
scott@ORA92>
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 16 21:16:14 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
