This discussion is archived
6 Replies Latest reply: Mar 1, 2012 12:42 AM by 893105 RSS

Saving the file as CLOB in the table

893105 Newbie
Currently Being Moderated
I have one .csv file in my local directory ( ex. C:\abc.csv )

I want save this abc.csv as CLOB in one of the CLOB column of the Database Table.

Now I'm doing this in JAVA,but I'm reading the file line by line and writing into the CLOB.

But, for the better performance its not good..

So. Is there any posibility for saving the "abc.csv" file directly into the CLOB column using PL/SQL Procedure ( without reading the file line by line ).


same thing , I have to do in vice versa also. ( i.e. direct conversion CLOB to .csv file with out reading the CLOB line by line)

Please let me know if u have any idea..

Thanks in Advance...

This is my first POST in OTN.

Edited by: Bhaskar on Feb 29, 2012 3:55 AM

Edited by: Bhaskar on Feb 29, 2012 5:29 AM
  • 1. Re: Saving the file as CLOB in the table
    AndreyN Pro
    Currently Being Moderated
    Hi, welcome to forum.

    You could do it with help of sqlloader utility.
    http://www.oracle-base.com/articles/10g/LoadLobDataUsingSqlLoader.php
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:627223659651
  • 2. Re: Saving the file as CLOB in the table
    802709 Journeyer
    Currently Being Moderated
    if you want to insert whole file to a clob, then you should use DBMS_LOB package. this package has a procedure called LOADCLOBFROMFILE. so you can load it and it would be faster.

    @user6806750, Bhaskar doesnt want to load data as rows to a table, does want to load as one row as clob.

    PS: @Bashkar, there is also a LOADFROMFILE procedure but if your database must have single byte english charset to use it, otherwise it will be pain. use LOADCLOBFROMFILE.

    Edited by: Mustafa KALAYCI on 29.Şub.2012 14:11
  • 3. Re: Saving the file as CLOB in the table
    AndreyN Pro
    Currently Being Moderated
    Mustafa KALAYCI wrote:
    if you want to insert whole file to a clob, then you should use DBMS_LOB package. this package has a procedure called LOADCLOBFROMFILE. so you can load it and it would be faster.

    @user6806750, Bhaskar doesnt want to load data as rows to a table, does want to load as one row as clob.

    PS: @Bashkar, there is also a LOADFROMFILE procedure but if your database must have single byte english charset to use it, otherwise it will be pain. use LOADCLOBFROMFILE.

    Edited by: Mustafa KALAYCI on 29.Şub.2012 14:11
    Have you seen the information on the links I gave?
    And as for your advice, DBMS_LOB package can be used only for files located at the database side not for files in local directory (except cases when the DB is installed on your PC).
  • 4. Re: Saving the file as CLOB in the table
    802709 Journeyer
    Currently Being Moderated
    sorry for misunderstanding. I thought that you adviced line by line insert via sqlloader. your solution is so good also.

    and yes you are right, file must be on db server side or at least on a shared network area.
  • 5. Re: Saving the file as CLOB in the table
    893105 Newbie
    Currently Being Moderated
    CREATE OR REPLACE PROCEDURE load_file_to_blob (p_filename IN VARCHAR2) IS
       out_blob    BLOB;
       in_file     BFILE;
       blob_length INTEGER;
       vErrMsg     VARCHAR2(2000);
     
    BEGIN
       -- set the in_file
       in_file := BFILENAME('IN_FILE_LOC',p_filename);
     
       -- Get the size of the file
       dbms_lob.fileopen(in_file, dbms_lob.file_readonly);
       blob_length := dbms_lob.getlength(in_file);
       dbms_lob.fileclose(in_file);
     
       -- Insert a new Record into the tabel containing the
       -- filename specified in P_FILENAME and a LOB_LOCATOR.
       -- Return the LOB_LOCATOR and assign it to out_blob.
       INSERT INTO lob_table (filename, blobdata)
          VALUES (p_filename, EMPTY_BLOB())
          RETURNING blobdata INTO out_blob;
     
       -- Load the file into the database as a blob.
       dbms_lob.open(in_file, dbms_lob.lob_readonly);
       dbms_lob.open(out_blob, dbms_lob.lob_readwrite);
       dbms_lob.loadfromfile(out_blob, in_file, blob_length);
     
       -- Close handles to blob and file
       dbms_lob.close(out_blob);
       dbms_lob.close(in_file);
     
       commit;
     
       -- Confirm insert by querying the database
       -- for Lob Length information and output results
       blob_length := 0;
     
       BEGIN
          SELECT dbms_lob.getlength(blobdata) into blob_length
            FROM lob_table
           WHERE filename = p_filename;
       EXCEPTION WHEN OTHERS THEN
          vErrMsg := 'No data Found';
       END;
       vErrMsg := 'Successfully inserted BLOB '''||p_filename||''' of size '||blob_length||' bytes.';
       dbms_output.put_line(vErrMsg);
    END;
    *@ Mustafa KALAYCI      and @ user6806750 :*
    I used this procedure but it is not taking the Local directory files.
  • 6. Re: Saving the file as CLOB in the table
    893105 Newbie
    Currently Being Moderated
    LOAD DATA 
    INFILE 'C:\abc.csv'
       INTO TABLE lob_tab
       FIELDS TERMINATED BY ','
       (number_content    CHAR(10),
        varchar2_content  CHAR(100),
        date_content      DATE "DD-MON-YYYY" ":date_content",
        clob_filename     FILLER CHAR(100),
        clob_content      LOBFILE(clob_filename) TERMINATED BY EOF,
        blob_filename     FILLER CHAR(100),
        blob_content      LOBFILE(blob_filename) TERMINATED BY EOF)
    *@user6806750      : Thanks 4 the reply. :)*

    where i have to Define the SQLLoader controlfile. I executed the above code but i'm getting error like ORA-00900: invalid sql stmt.*

    in the place of file name, we should give the entire path or only file name?
    whether i can use it to get the files for local drives ( not from oracle server ).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points