Home » SQL & PL/SQL » SQL & PL/SQL » Bfile Error - Please help (Oracle 10g , XP)
Bfile Error - Please help [message #316581] Fri, 25 April 2008 22:25 Go to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
SQL> create table regis_tab
2 (id number primary key, blob_obj blob, clob_obj clob, bfile_obj bfile);

Table created.

SQL> create or replace directory your_bfile_dir
2 as 'D:\PICS';

Directory created.

SQL> insert into regis_tab(id, blob_obj, clob_obj, bfile_obj) values
2 (1, empty_blob(), empty_clob(), bfilename('your_bfile_dir', 'test.txt'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> insert into regis_tab(id, blob_obj, clob_obj, bfile_obj) values
2 (2, empty_blob(), 'I am invincible', bfilename('your_bfile_dir', 'test1.txt'));

1 row created.

SQL> commit;

Commit complete.

SQL> DECLARE
2 v_file BFILE;
3 v_blob BLOB;
4 n_position BINARY_INTEGER;
5 buff RAW(400);
6 BEGIN
7 --get the blob at position 1 which empty and we loc it.
8
9 SELECT blob_obj INTO v_blob FROM regis_tab
10 WHERE id = 1 FOR UPDATE;
11 -- we get the object we want to store in that blob column
12 -- note you can modify this to whatever you want to store
13 -- in that blob.
14 -- in my example it is file.
15
16 SELECT bfile_obj INTO v_file FROM regis_tab WHERE id=1;
17
18 DBMS_LOB.FILEOPEN(v_file,DBMS_LOB.FILE_READONLY);
19 n_position := DBMS_LOB.GETLENGTH(v_file);
20
21 DBMS_LOB.READ(v_file, n_position, 1, buff);
22 DBMS_LOB.WRITE(v_blob, n_position, 1, buff);
23 DBMS_LOB.FILECLOSE(v_file);
24 EXCEPTION
25 WHEN NO_DATA_FOUND THEN
26 BEGIN
27 DBMS_OUTPUT.PUT_LINE('End of file reached');
28 DBMS_LOB.FILECLOSE(v_file);
29 END;
30 END;
31
32
33 /
DECLARE
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 18

How will one open a file which is stored in the BFILE column of the table ?? I am getting this error when trying to open the file stored in the table, I don't want to hardcode filename values to open it, i want to just open the file stored in the BFILE column from the stored location. but this is the error i am getting any one can help on what is going wrong here

[Updated on: Fri, 25 April 2008 22:33]

Report message to a moderator

Re: Bfile Error - Please help [message #316582 is a reply to message #316581] Fri, 25 April 2008 23:03 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
SQL> DECLARE
2 v_file BFILE;
3 v_blob BLOB;
4 n_position BINARY_INTEGER;
5 buff RAW(400);
6 BEGIN
7 --get the blob at position 1 which empty and we loc it.
8
9 SELECT blob_obj INTO v_blob FROM regis_tab
10 WHERE id = 1 FOR UPDATE;
11 -- we get the object we want to store in that blob column
12 -- note you can modify this to whatever you want to store
13 -- in that blob.
14 -- in my example it is file.
15
16 SELECT bfile_obj INTO v_file FROM regis_tab WHERE id=2;
17 --- A image file is stored in the above location

18 DBMS_LOB.FILEOPEN(v_file,DBMS_LOB.FILE_READONLY);
19 n_position := DBMS_LOB.GETLENGTH(v_file);
20
21 DBMS_LOB.READ(v_file, n_position, 1, buff);
22 DBMS_LOB.WRITE(v_blob, n_position, 1, buff);
23 DBMS_LOB.FILECLOSE(v_file);
24 EXCEPTION
25 WHEN NO_DATA_FOUND THEN
26 BEGIN
27 DBMS_OUTPUT.PUT_LINE('End of file reached');
28 DBMS_LOB.FILECLOSE(v_file);
29 END;
30 END;
31 /
DECLARE
*
ERROR at line 1:
ORA-21560: argument 2 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 767
ORA-06512: at line 21


I got through the earlier error it was because i stored the file name and location in the table in lower case, when i stored it in upper case it got solved, but now i have another problem, here i am trying to read a image file here, the problem is in the 21st line with the parameter n_position which is a binary integer...What could be the solution for the above error, ORA-21560
Re: Bfile Error - Please help [message #316583 is a reply to message #316582] Fri, 25 April 2008 23:28 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Try using "dbms_output.put_line (n_position);" so that you can see what the value is, so you know whether it is null or too big in order to help determine what the problem is.
Previous Topic: Accented Vowels
Next Topic: How to ask user to enter the text
Goto Forum:
  


Current Time: Sun Dec 04 00:22:53 CST 2016

Total time taken to generate the page: 0.19592 seconds