title: Oracle Forms using OLE2 not saving Excel document on 3-tier setup
body: <p>System setup:</p>
  
  <pre><code>3-Tier environment
  Client Machine - doesn't matter
  Web-Tier - Not sure.  Probably Windows Server 2008 64 bit
         -Jdk 7u3
  App Server - Windows Server 2008 64 bit
         -Weblogic Server 10.3.6
         -Excel 2010
         -Jdk 7u3
  Database Server - Not sure. Probably Windows Server 2008 64 bit.
         -Oracle Database 11g
  
  Programming using Oracle Forms 11.1.1.6
  </code></pre>
  
  <p>Now, my problem is that when we had designed they system, everything but the database was on one PC.  I was able to read and write Excel documents no problem at all.  Then we moved everything to a tiered setup where we had the client, app server and database server.  Everything still worked great.  Finally they set up the 3-tiered system and that's where I ran into my problems.</p>
  
  <p>When I have Oracle Forms write to the Excel document the code appears to execute without any errors until I try to copy the file from the App Server using Webutil.</p>
  
  <pre><code>PROCEDURE Export_to_Excel IS
   -- Declare the OLE objects
   application OLE2.OBJ_TYPE;
   workbooks OLE2.OBJ_TYPE;
   workbook OLE2.OBJ_TYPE;
   worksheets OLE2.OBJ_TYPE;
   worksheet OLE2.OBJ_TYPE;
   --cell OLE2.OBJ_TYPE;
   range OLE2.OBJ_TYPE;
   range_col OLE2.OBJ_TYPE;
  
   -- Declare handles to OLE argument lists
   args OLE2.LIST_TYPE;
   p_filename VARCHAR(255);
  
   --p_file TOOL_RES.RFHANDLE;
   p_file Text_IO.File_Type;
   p_filename_client      VARCHAR2(500);
   v_filename                     VARCHAR2(500);
   v_error                            VARCHAR2(500);
   passed_filename            VARCHAR2(500);
  BEGIN
  
   -- Retrieve user specific directory to create new file in
   p_filename := Webutil_file_transfer.get_work_area;
  
   -- Start Excel 
   application:=OLE2.CREATE_OBJ('Excel.Application');
  
   -- Return object handle to the Workbooks collection 
   workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
  
   -- Add a new Workbook object to the Workbooks collection
   workbook:=OLE2.GET_OBJ_PROPERTY(workbooks,'Add');
  
   -- Return object handle to the Worksheets collection for the Workbook
   worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
  
   -- Set up the Header worksheet
   args:=OLE2.CREATE_ARGLIST; 
   OLE2.ADD_ARG(args, 1);
   worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,'Item',args);
   OLE2.DESTROY_ARGLIST(args);
   OLE2.set_property(worksheet,'Name','Header');
  
   -- Build header form
   populate_header(worksheet);
  
   -- Autofit columns
   range := OLE2.GET_OBJ_PROPERTY( worksheet,'UsedRange');
   range_col := OLE2.GET_OBJ_PROPERTY( range,'Columns');
   OLE2.INVOKE( range_col,'AutoFit' );
   OLE2.RELEASE_OBJ( range );
   OLE2.RELEASE_OBJ( range_col );
  
  
   -- Set up the Item worksheet
   args:=OLE2.CREATE_ARGLIST; 
   OLE2.ADD_ARG(args, 2);
   worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,'Item',args);
   OLE2.DESTROY_ARGLIST(args);
   OLE2.set_property(worksheet,'Name','Item List');
  
   -- Build Item sheet
   populate_item_sheet(worksheet);
  
   -- Delete the last worksheet that excel automatically creates
   args:=OLE2.CREATE_ARGLIST; 
   OLE2.ADD_ARG(args, 3);
   worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,'Item',args);
   OLE2.DESTROY_ARGLIST(args);
   OLE2.invoke(worksheet, 'Delete');
  
   -- Save as worksheet
   OLE2.SET_PROPERTY(application,'DisplayAlerts',FALSE);
   IF p_filename is not null THEN
          args := OLE2.CREATE_ARGLIST;
          OLE2.ADD_ARG( args, p_filename || :PARAMETER_B1.FILENAME || '.xlsx');
          OLE2.ADD_ARG( args, 56 );
          OLE2.INVOKE( workbook,'SaveAs',args );
          OLE2.DESTROY_ARGLIST( args );
   END IF;
  
   -- Close workbook
   OLE2.INVOKE( workbook ,'Close');
  
   -- Release the OLE objects
   OLE2.RELEASE_OBJ(worksheet);
   OLE2.RELEASE_OBJ(worksheets);
   OLE2.RELEASE_OBJ(workbook);
   OLE2.RELEASE_OBJ(workbooks);
   OLE2.INVOKE(application, 'Quit');
   OLE2.RELEASE_OBJ(application); 
  
   --Check if file was writen correctly
   p_file := text_io.fopen(p_filename || :PARAMETER_B1.FILENAME || '.xlsx','r');
   Text_IO.Fclose(p_file);
  
   --Added the following code
   passed_filename := :PARAMETER_B1.FILENAME || '.xlsx';
   v_filename := p_filename || passed_filename;
  
   -- Popup a dialog box to allow user to select the location to save the file
   p_filename_client := CLIENT_GET_FILE_NAME ( 'C:\', passed_filename, NULL, 'Select A Directory', SAVE_FILE, FALSE );
  
   if p_filename_client is null then
        message ('Creation of the spreadsheet has been canceled.');
          raise form_trigger_failure;
   end if;
  
   -- File Transfer to Client
   PROCESS_COMM_FILE_CLIENT.FILE_TRANSFER('O', p_filename_client, v_filename, null, v_error);
  
   EXCEPTION
        WHEN others THEN
          Text_IO.Fclose(p_file);
                  message( SQLERRM( SQLCODE ) ) ;
          if p_filename_client is not null then
            MESSAGE('An error occurred while creating file.');
        end if;
  END; 
  </code></pre>
  
  <p>The code fails at the FILE_TRANSFER because the form does not get created on the App Server like it is supposed to.</p>
  
  <p>A related problem that is occuring is that when I try to upload the excel document and read it in to oracle I get a ORA-305500 error.  I have tried to have the DBA uninstall and reinstall Excel and made sure all of the features/add-ons were included during the installation but the problem still hasn't been fixed.</p>
  
  <p>Could someone please give me some suggestions on what to do to fix this problem or continue to problem shoot this?</p>
  
  <p>Thanks,</p>
  
  <p>Bill</p>