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

Beware the UNION ALL - it can swallow rows...

Just for all you June CTPers as a warning.. I have a UNION ALL that, at random, decides to swallow row count....

Tried deleting and recreating task
Unable to Repro

Along with the buffer leaks and the looping errors.. bring on the NEXT CTP.. ! ! !

Giri,

Can you send me a package that reproduces this issue? We're unaware of any UnionAll issues with CTP15 and I'd love to fix these.

thanks