4 Replies Latest reply: Jan 29, 2013 1:35 AM by Marwim RSS

    Storing an Excel File in the Oracle Table through Oracle Form (10g)


      We have the below requirement in the Oracle Forms (10g).

      Database: 11g
      Application: R12

      We need to provide an upload functionality to the user, so that he can upload an excel file into the form (from the local system), this is achived.

      Now what we need to do is that we need to capture the path of the document (in the local system) and we need to store the document in a Oracle table.

      Next we need to validate the records that are being loaded and the error records should be written into one more file and that file also we need to store in an Oracle table, this is because, i need to query this error file from the table later and need to send to the user as an e-mail.

      Basically i started off with the below code just for the POC. but iam facing the error. Please help. I did not put this code in my form.

      Create Table Email_Attachments(Id_Pk Integer Primary Key,Fname Varchar2(50),Image Blob);

      Create Or Replace Directory Temp As 'C:\';

      Create Or Replace Procedure Load_File(Pi_Id In Integer, Pfname In Varchar2) Is
      Src_File Bfile;
      Dst_File Blob;
      Lgh_File Binary_Integer;
      Src_File := Bfilename('TEMP', Pfname);
      Insert Into Email_Attachments (Id_Pk,Fname,Image)
      Values (Pi_Id,Pfname, Empty_Blob())
      Returning Image Into Dst_File;
      Dbms_Lob.Open(Src_File, Dbms_Lob.File_Readonly);
      Lgh_File := Dbms_Lob.Getlength(Src_File);
      Dbms_Lob.Loadfromfile(Dst_File, Src_File, Lgh_File);


      ORA-22288: file or LOB operation FILEOPEN failed
      No Such file or directory
      ORA-06512: at "SYS.DBMS_LOB", line 1014
      ORA-06512: at "SYS.LOAD_FILE", line 10
      ORA-06512: at line 2

      How to capture the Path from which he has loaded the file?
      How to Write the error records it to the file and store it in an Oracle table?

      Will the below table be of use to me?


      Please share your ideas as to how to acheive this.

      Thanks and Regards