Bfiles

Hello,

I just want to get some chars from a textfile to be shown via SQL+. My intention is to demonstrate the random access possibility to a LOB in Oracle.
Therefore I put a BFILE into a table and try to read and output a small part from the referenced textfile to the screen.

Here is my code:

CREATE TABLE PLSQL(
text_id NUMBER(5) PRIMARY KEY,
text_file BFILE)

CREATE OR REPLACE directory SAMPLES as 'C:\samples'

INSERT INTO PLSQL(text_id,text_file)
values(1,BFILENAME('samples','erle.txt'))

declare
locator_var BFILE:=BFILENAME('SAMPLES','erle.txt');
amount_var INTEGER;
offset_var INTEGER;
output_var VARCHAR2(10);
begin
amount_var:= 10;
offset_var:= 1;
select text_file into locator_var from PLSQL
where text_id = 1;
DBMS_LOB.OPEN(locator_var, DBMS_LOB.LOB_READONLY);
DBMS_LOB.READ(locator_var, amount_var, offset_var, output_var);
DBMS_OUTPUT.PUT_LINE('Begin of Erle: ' || output_var);
end;
/

COMMIT;

when I run the skript I get the following error message:
"SQL> start demo_plsql;
declare
*
FEHLER in Zeile 1:
ORA-22285: Verzeichnis oder Datei fr FILEOPEN-Vorgang ist nicht vorhanden
ORA-06512: in "SYS.DBMS_LOB", Zeile 672
ORA-06512: in Zeile 11"

The directory exists and the file is also there.
So where could be the problem?

In hope for any suggestions.

MaxSorry, can't read German... yet :)

However, it looks like your problem is here:

INSERT INTO PLSQL(text_id,text_file)
values(1,BFILENAME('samples','erle.txt'))

Input into BFILENAME first parameter needs to be upper case in order to match your specification in the CREATE DIRECTORY command.

JoeB