Trying to Load and Display Contents of a Bfile

0 pts.
Tags:
SQL
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;
ASKED: June 26, 2006  2:37 PM
UPDATED: June 27, 2006  11:16 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following