1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Reading from file

Discussion in 'SQL PL/SQL' started by Radi, Jan 31, 2017.

  1. Radi

    Radi Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Jordan
    iHello,,
    I wanted to read from txt file..
    so I read a lot of threads all talked about clob..

    so first I go to my laptop locally I created my text file on D:\ drive

    I faced a lot of problems. like inserting D:\ directly in bfilename() function
    then I realized that I must use command

    Code (SQL):
    CREATE OR REPLACE DIRECTORY BFILE_DIR AS 'D:\';
    Code (SQL):
    GRANT READ ON DIRECTORY BFILE_DIR TO public;
    now I'm facing problem that I can't find a clear solution for it

    it gives me

    ORA-22288: file or LOB operation FILEOPEN failed
    The system cannot find the file specified.
    ORA-06512: at "SYS.DBMS_LOB", line 805
    ORA-06512: at line 12

    I wrote the statement

    Code (SQL):
    SELECT owner, directory_name, directory_path FROM all_directories;
    and I found my bath defined!
    SYS BFILE_DIR D:\

    what should I do I'm 100% percent of the file location and spelling [​IMG]

    Code (SQL):
    DECLARE
    l_clob CLOB;
    l_bfile bfile;
    l_filename varchar2(255);

    BEGIN
    INSERT INTO sh_census.loob VALUES (empty_clob())
    returning loob INTO l_clob;
    l_filename:='LOOB.txt';
    l_bfile := bfilename('BFILE_DIR',l_filename);
    --IF DBMS_LOB.FILEEXISTS (l_bfile) = 1 THEN
    dbms_lob.fileopen( l_bfile );
    --dbms_lob.loadfromfile( l_clob, l_bfile,
    --dbms_lob.getlength( l_bfile ) );
    --dbms_lob.fileclose( l_bfile );
    --end if;
    END;
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If the image you posted is the directory where your file is supposed to reside there is nothing for Oracle to open; I see no files of any type. Oracle does not lie so if it's reporting there is no file then:

    1) there is no file
    2) your user has no permissions to read/write files on D:\

    Why would you place a file at the root location of a mapped disk? You should be creating a directory for such files and creating the directory in the database to point to that actual directory.

    You need to post all of the steps you used and a screen shot of what is actually in D:\. I strongly suspect that Oracle is telling you the truth.
     
  3. David stephan

    David stephan Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Germany
    Hello

    I think you are going with a wrong oracle command.

    You can't enter the path directly to the file open command like this

    f := UTL_FILE.FOPEN('C:\Projects\','testdatabinary.txt','R');

    Instead of entering path directly, you have to create directory using the below sql query

    CREATE or replace DIRECTORY USER_DIR AS 'C:\PROJECTS\';

    Then enter the directory name to the file open command. It will be like this

    f := UTL_FILE.FOPEN('USER_DIR ','testdatabinary.txt','R');