3 Replies Latest reply: Sep 22, 2011 2:58 AM by 715920 RSS

    using DBMS_LOB.LOADCLOBFROMFILE

    715920
      Dear all,
      I got some difficulty in trying to use DBMS_LOB.LOADCLOBFROMFILE. Suppose I have this procedure that load an OS file into a CLOB and read, however I keep getting error saying that
      ERROR at line 1:
      ORA-22275: invalid LOB locator specified
      ORA-06512: at "SYS.DBMS_LOB", line 890
      ORA-06512: at "HR.SOME_PROC", line 14
      ORA-06512: at line 1
      This is the procedure:
      create or replace procedure some_proc is
      note_clob clob;
      note_bfile bfile;
      note_var varchar2(10000);
      warning int;
      dest_off int:=1;
      src_off int:=1;
      lang_ctx int:=0;
      amount int:=dbms_lob.lobmaxsize;
      begin
      note_clob:=empty_clob();
      note_bfile:=bfilename('TEMP_DIR','note.txt');
      dbms_lob.fileopen(note_bfile);
      dbms_lob.loadclobfromfile(note_clob,note_bfile,1000,dest_off,src_off,0,lang_ctx,warning);
      --dbms_lob.read(note_clob,amount,1,note_var);
      dbms_lob.fileclose(note_bfile);
      --dbms_output.put_line(note_var);
      end some_proc;
      /
      I comment out the part that reads and prints for debugging purpose. Could anybody point me out where did I do wrong on LOADCLOBFROMFILE?

      best regards,
      Val