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  |
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   |
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  |
 |
Barbara Boehmer
Messages: 9104 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.
|
|
|
Goto Forum:
Current Time: Thu Feb 13 16:44:17 CST 2025
|