Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Philippe.

Asked: December 29, 2004 - 8:53 am UTC

Last updated: January 03, 2005 - 10:21 pm UTC

Version: 9.0.2

Viewed 1000+ times

You Asked

Hi Tom,


I want to know , if it is possible to display the value ofa cell of a bfile column.

I have this example

create table Test (Ident number (6) not null, Image bfile);

create directory DIR_IMAGE as 'c:\temp\image';

create directory DIR_VIDEO as 'c:\temp\video';

insert into test value (1, bfilename( 'DIR_IMAGE', 'IMAGE_01.gif' );
insert into test value (2, bfilename( 'DIR_VIDEO', 'VIDEO_01.mov' );


Now I want do a select of the table Test where the result is something like that

1 'DIR_IMAGE' 'IMAGE_01.gif'
2 'DIR_VIDEO' 'VIDEO_01.mov'

or

1 'c:\temp\image\IMAGE_01.gif'
2 'c:\temp\video\VIDEO_01.mov'

but I don't want to display the gif or the mov files

Thanks
Philippe


and Tom said...

two methods for you -- simple and more sophisticated (meaning more code :)


ops$tkyte@ORA9IR2> create or replace function get_filename( p_bfile in bfile ) return varchar2
2 as
3 l_dir varchar2(4000);
4 l_fname varchar2(4000);
5 begin
6 dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
7 return l_dir || ':' || l_fname;
8 end;
9 /

Function created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> column fname format a30
ops$tkyte@ORA9IR2> select ident, get_filename( image ) fname from test;

IDENT FNAME
---------- ------------------------------
1 DIR_IMAGE:IMAGE_01.gif
2 DIR_VIDEO:VIDEO_01.mov
3 DIR_IMAGE:IMAGE_02.gif
4 DIR_VIDEO:VIDEO_02.mov

there is it simply -- the directory and the name.... If you wanted the windows path -- you could


ops$tkyte@ORA9IR2> create or replace package bfile_pkg
2 as
3 function get_filename( p_bfile in bfile ) return varchar2;
4 end;
5 /

Package created.

ops$tkyte@ORA9IR2> 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.

I'm using the array to cache the directory/path -- instead of querying each time


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> column fname format a30
ops$tkyte@ORA9IR2> select ident, bfile_pkg.get_filename( image ) fname from test;

IDENT FNAME
---------- ------------------------------
1 c:\temp\image\IMAGE_01.gif
2 c:\temp\video\VIDEO_01.mov
3 c:\temp\image\IMAGE_02.gif
4 c:\temp\video\VIDEO_02.mov

ops$tkyte@ORA9IR2>




Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

raise no_data_found

Marcio, January 03, 2005 - 7:48 am UTC

It must be a silly question, but anyway.

Regarding your code below

<quote>
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;
</quote>

What statement is raising NO_DATA_FOUND ?
Thanks and regards,

Tom Kyte
January 03, 2005 - 9:09 am UTC

g_array(l_dir)

does, when g_array(l_dir) has "no data" in it. accessing a plsql index by table raises no data found.

NO_DATA_FOUND

Marcio, January 03, 2005 - 11:45 am UTC

Interesting, because when I put
exception
when others ...
it didn't raise anything as well as without exception clause.

I thought that every exception must give us a raise.
Is there either some parameter to force the exception be raised in this case?

Thanks,

Tom Kyte
January 03, 2005 - 10:21 pm UTC

not sure what you are trying to say?

when others is evil. unless followed by RAISE -- 99.9999999999999999% of the time.




Please ignore my last NO_DATA_FOUND review

Marcio, January 03, 2005 - 1:14 pm UTC

Sorry Tom, it is documentted.

I aggregated a little procedure p which raise a no_data_found.

25 procedure p
26 is
27 l_dir varchar2(4000);
28 l_fname varchar2(4000);
29 l_path varchar2(4000);
30 begin
31 for x in ( select * from test )
32 loop
33 dbms_lob.FILEGETNAME( x.image, l_dir, l_fname );
34 dbms_output.put_line( 'The value of garray is '|| g_array(l_dir) );
35 end loop;
36 end;
37
38 end;
39 /

Package body created.

ops$mportes@FCCUAT9I> show error
No errors.
ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> exec bfile_pkg.p
BEGIN bfile_pkg.p; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "OPS$MPORTES.BFILE_PKG", line 34
ORA-06512: at line 1

Thanks and rgds,


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here