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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here