Results 1 to 2 of 2

Thread: Bfiles

  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Unhappy Unanswered: 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 für 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.

    Max

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Sorry, can't read German... yet

    However, it looks like your problem is here:

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •