Upload a csv or excel file from clients machine in Oracle Table with oracle Forms

WebUtil: How to Read an Excel file into an Oracle Form

let’s get down to business and create a form that reads data from Excel into a Forms Data Block.

Do before proceed.

  1.  Table PLANETS :- script to create the table used by this sample code

DROP TABLE PLANETS;
CREATE TABLE PLANETS (
 PID                     NUMBER(4),
 NAME             VARCHAR2(15),
 FEATURES         VARCHAR2(15));
/

  1. planets.xls – Excel (version 2003) sample spreadsheet
1 Mercury Hot!
2 Venus Bright!
3 Earth Life!
4 Mars Water!
5 Jupiter Big!
6 Saturn Rings!
eod    

Setup steps:

  1. Log into your database and run the script
  2. Ensure your environment is properly configured to run WebUtil. These steps does not cover this configuration – there is plenty of information available in the OTN forums as well on the Internet that covers configuring WebUtil so I will not included it here.
  3. Place “planets.xls’ some place on the client computer. I have modified Oracle’s original form to display a “File Select dialog” rather than hard code the file name and location in the code.
  4. Ensure you have configured the Forms Builder to allow running a form from the Forms Builder. This document does not cover how to perform this configuration.
  5. Create a simple form for PLANETS table data block.
  6. Palace a button on form canves and name it “Read from Excell File”.
  7. on Button Press trigger paste below code.

Code:

DECLARE
 application   Client_OLE2.Obj_Type;
 workbooks     Client_OLE2.Obj_Type;
 workbook      Client_OLE2.Obj_Type;
 worksheets    Client_OLE2.Obj_Type;
 worksheet     Client_OLE2.Obj_Type;
 worksheet2    Client_OLE2.Obj_Type; 
 cell      Client_OLE2.OBJ_TYPE;
 args      Client_OLE2.OBJ_TYPE;
 cell_value   varchar2(100);
 num_wrkshts  NUMBER;
 wksht_name  VARCHAR2(250);
 eod      boolean:=false;
 j        integer:=1;
 v_fName    VARCHAR2(250);
BEGIN
 — Get the name of the file to open
 –v_fName := ‘D:\MyDevelopment\Forms\Samples\WebUtil\Read_Excel\planets3.xls’;
 v_fName := WebUtil_File.File_Open_Dialog(
       directory_name => ‘C:\’
       –,file_name => Get_Form_Property(:System.Current_form,Form_Name)||’.xls’
       ,File_Filter => null
       ,Title => ‘Select Client filename to Open.’
     );

 IF ( v_fName IS NOT NULL ) THEN
 
  — The following sets up communication with the excel spreadsheet
  — ————————————————————–
  — Open the OLE application
  application := Client_OLE2.create_obj(‘Excel.Application’);
  — Keep the application hidden
  Client_OLE2.set_property(application,’Visible’,’false’);
 
  workbooks := Client_OLE2.Get_Obj_Property(application, ‘Workbooks’);
  args := Client_OLE2.CREATE_ARGLIST;
  
  — Open the selected File
  — ———————-
  Client_OLE2.add_arg(args,v_fName);
  workbook := Client_OLE2.GET_OBJ_PROPERTY(workbooks,’Open’,args);
  Client_OLE2.destroy_arglist(args);
 
  worksheets := Client_OLE2.GET_OBJ_PROPERTY(workbook, ‘Worksheets’);
  
  — Get number of worksheets
  — ————————
  num_wrkshts := Client_OLE2.GET_NUM_PROPERTY(worksheets, ‘Count’);
  worksheet := Client_OLE2.GET_OBJ_PROPERTY(application,’activesheet’);
    
  –Go to the first record
  go_block(‘planets’);
  first_record;
    
  loop
    If :system.record_status <> ‘NEW’ then
           create_record;
    end if;
    
  exit when eod;
   
   for k in 1..3 loop  –3 fields per record
    args:= Client_OLE2.create_arglist;
    Client_OLE2.add_arg(args, j);
    Client_OLE2.add_arg(args, k);
    cell := Client_OLE2.get_obj_property(worksheet, ‘Cells’, args);
    Client_OLE2.destroy_arglist(args);
    cell_value :=Client_OLE2.get_char_property(cell, ‘Value’);
     
    
     
    if upper(cell_value) = ‘EOD’ then
     eod:=true;
     Message(‘End of Data’);
     exit;
    end if;
  
  –Could be done this way also ->
  
  /*if k =1 then
   :dept.deptno:=cell_value;
  end if;
  
  if k =2 then
   :dept.dname:=cell_value;
  end if;
  
  if k =3 then
   :dept.loc:=cell_value;
  end if; 
  */
 
    –Less code this way ->
    copy(cell_value,name_in(‘system.cursor_item’));
    next_item;
  
   end loop; –for
   
   j:=j+1;
  end loop;–main loop
  
  — Release the Client_OLE2 object handles
  IF (cell IS NOT NULL) THEN
   Client_OLE2.release_obj(cell);
  END IF;
  IF (worksheet IS NOT NULL) THEN
   Client_OLE2.release_obj(worksheet);
  END IF;
  IF (worksheets IS NOT NULL) THEN
   Client_OLE2.release_obj(worksheets);
  END IF;
  IF (worksheet2 IS NOT NULL) THEN
   Client_OLE2.release_obj(worksheet2);
  END IF;
  IF (workbook IS NOT NULL) THEN
   Client_OLE2.release_obj(workbook);
  END IF;
  IF (workbooks IS NOT NULL) THEN
   Client_OLE2.release_obj(workbooks);
  END IF;
  Client_OLE2.invoke(application,’Quit’);
  Client_OLE2.release_obj(application);
 ELSE
  Message(‘No File selected.’);
  message(‘ ‘);
  RAISE Form_Trigger_Failure;
 END IF;
END;

Now enjoy!

 

 

 

 

 

Advertisements

About Oracle Technology Blogs
Oracle Application Development

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: