Trying to Load and Display Contents of a Bfile
I created a table with a key_value and Bfile column. Create a directory alias. Insert a row using the BFILENAME procedure. I said 1 row added. Tried to execute the PL/SQL code below. I get an "nonexistent directory or file error on Open". What is wrong and how can I determine I insert a record there something in the BFILE column?
DECLARe
FILE_LOC BFILE;
BUFFER RAW(1024);
AMOUNT BINARY_INTEGER := 25000;
POSITION INTEGER := 1;
BEGIN
/*SELECT THE LOB */
SELECT picture_LOb INTO FILE_LOC
FROM LOB_PICTURES_TABLE
WHERE KEY_VAULE = 1;
/* OPENING THE BFILE */
DBMS_LOB.OPEN (FILE_LOC, DBMS_LOB.LOB_READONLY);
LOOP
DBMS_LOB.READ(FILE_LOC, AMOUNT, POSITION, BUFFER);
/* DISPLAY THE BUFFER CONTENTS */
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(BUFFER));
POSITION := POSITION + AMOUNT;
END LOOP;
/* CLOSE BFILE */
DBMs_LOB.CLOSE (FILE_LOC);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('END OF DATA');
End;
Software/Hardware used:
Software/Hardware used:
ASKED:
June 26, 2006 2:37 PM
UPDATED:
June 27, 2006 11:16 AM
Answer Wiki:
The problem is with the insert statement. The directory name is stored in UPPER case in the database.
CREATE OR REPLACE DIRECTORY my_dir AS 'd:my_pictures';
-- my_dir is stored as MY_DIR, therefore the insert becomes
INSERT INTO LOB_PICTURES_TABLE VALUES (1,BFILENAME('MY_DIR','oracle.jpg'));
If you then get the error:
ORA-21560: argument 2 is null, invalid, or out of range
reduce the value of Amount to 1000.
Regards,
Peter Wilkinson
Tested on OracleXE for windows. See Oracle Note:1080814.6
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
