Pages

Tuesday, November 25, 2014

Data Convertion from a FILE(large txt,doc,xml,csv etc) to CLOB in ORACLE

paid surveys
Compile below procedure:

CREATE OR REPLACE Procedure Dpr_fileToClob(Fname in VARCHAR2, Fdir in VARCHAR2, Outclob out CLOB)
IS

fclob                   CLOB;
theBFile             BFILE;
num                    NUMBER :=0;
src_offset           NUMBER :=1;
dest_offset         NUMBER :=1;
lang_context      NUMBER :=1;

BEGIN

dbms_lob.createtemporary(fclob,FALSE,DBMS_LOB.SESSION);
theBFile := BFileName(Fdir,Fname);
dbms_lob.fileOpen(theBFile);

dbms_lob.loadClobFromFile(dest_lob =>fclob,
                                               src_bfile =>theBFile,
                                               amount =>dbms_lob.getLength(theBFile),
                                               dest_offset =>dest_offset,
                                               src_offset =>src_offset,
                                               bfile_csid =>0,
                                               lang_context =>lang_context,
                                               warning =>num);

dbms_lob.fileClose(theBFile);
Outclob := fclob;

end;
/

***Now execute below PLSQL block to show your desired output:

declare
 v_clob clob;
begin
  Dpr_fileToClob(Fname=>'File_Name.ext', Fdir=>'Database_Directory_Name', Outclob => v_clob);
  dbms_output.out_line(dbms_lob.getlength(v_clob));
end;

See the file length output from the above DBMS_OUTPUT.
 

paid surveys

No comments:

Post a Comment