Uploading Data From Excel to Oracle Datbase(Through Forms 6i)

1. Create the form with Emp Data block in Tabular Style
2. Create Control Block having Two Push Buttons

   Properties            Push_Button1            Push_Button2
   Name                PB_BROWSE            PB_IMPORT
   Key board navigation        no                no
   mouse navigation        no                no
   Label            Browse                Import

3. Create two Display Item in Control Block having following Properties
   Properties            Display_Item1            Display_Item2
   Name                v_line_buf            file_name
   Data Type            Character            Character
   Maximum Length        2000                30


4. Create Following Function on Program Unit
FUNCTION DATA_VALID (EMP_ID IN NUMBER) RETURN BOOLEAN IS
V_EMPNO NUMBER;
BEGIN
    SELECT EMPNO INTO V_EMPNO FROM EMP
    WHERE EMPNO=EMP_ID;
    RETURN(FALSE);
EXCEPTION
    WHEN NO_dATA_FOUND THEN
  RETURN(TRUE);
END;

5. Create Following Procedure on Program Unit
PROCEDURE SHOW_MSG (p_text varchar2, p_title varchar2 default 'Message', pms_type out number) IS
msgbox number;
BEGIN
    set_alert_button_property('msg_alert',alert_button1, label,'Add on Existing');
    set_alert_button_property('msg_alert',alert_button2, label,'Create New');
  set_alert_property('msg_alert',title,P_title);
    set_alert_property('msg_alert',alert_message_text,p_text);
    msgbox :=show_alert('msg_alert');
    if msgbox=alert_button1 then
        pms_type:=1;
    elsif msgbox=alert_button2 then
        pms_type:=2;
    end if;
   
END;

6. Create an Alert and name them Msg_Alert

7. Write following code on Browse Button (When Button Pressed)

declare
    v_path    varchar2(1000);
begin
 v_path := get_file_name(file_filter=> 'CSV Files (*.csv)|*.csv|'
                ||'XLS Files (*.xls)|*.xls|'
                ||'TXT Files (*.txt)|*.txt|');   
 :file_name:= v_path;
exception
    when others then null;
end;

8.Write the following code on Import Button (When Button Pressed)
Declare
in_file     Text_IO.File_Type;
linebuf     VARCHAR2(20000);
filename     VARCHAR2(30);
CHK         VARCHAR2(1000);
LOC_VALUE     VARCHAR2(1000);
SNO         NUMBER:=0;
AA         NUMBER:=0;
V_EMPNO     EMP.EMPNO%TYPE;
V_ENAME     EMP.ENAME%TYPE;
V_JOB       EMP.JOB%TYPE;
V_MGR       EMP.MGR%TYPE;
V_HIREDATE     EMP.HIREDATE%TYPE;
V_SAL        EMP.SAL%TYPE;
V_COMM        EMP.COMM%TYPE;
V_DEPTNO    EMP.DEPTNO%TYPE;
V_CURR            NUMBER:=0;
v_ret_no          number:=0;

BEGIN
go_block('emp');
if :empno is not null then
    show_msg('What You Want To Do','Alert',v_ret_no);
    if v_ret_no=1 then
        next_record;
    elsif v_ret_no=2 then
        clear_block(no_validate);
    end if;
end if;

in_file:=Text_IO.Fopen(:file_name,'r');
LOOP
    AA:=AA+1;
Text_IO.Get_Line(in_file,linebuf);
IF AA>1 THEN
  :V_LN_BUFF    :=LINEBUF;
  V_EMPNO       :=RTRIM(SUBSTR(:V_LN_BUFF,1,4));
  v_curr       :=INSTR(LINEBUF,',',1,1)+1;
  V_Ename       :=RTRIM(SUBSTR(:V_LN_BUFF,V_CURR,INSTR(SUBSTR(:V_LN_BUFF,V_CURR,10),',',1,1)-1));
  v_curr       :=INSTR(LINEBUF,',',1,2)+1;
  V_JOB        :=  RTRIM(SUBSTR(:V_LN_BUFF,V_CURR,INSTR(SUBSTR(:V_LN_BUFF,V_CURR,10),',',1,1)-1));
  v_curr       :=INSTR(LINEBUF,',',1,3)+1;
  V_MGR            :=RTRIM(SUBSTR(:V_LN_BUFF,V_CURR,INSTR(SUBSTR(:V_LN_BUFF,V_CURR,10),',',1,1)-1));
  v_curr       :=INSTR(LINEBUF,',',1,4)+1;
  V_HIREDATE       :=RTRIM(SUBSTR(:V_LN_BUFF,V_CURR,INSTR(SUBSTR(:V_LN_BUFF,V_CURR,15),',',1,1)-1));
  v_curr       :=INSTR(LINEBUF,',',1,5)+1;
  V_SAL            :=RTRIM(SUBSTR(:V_LN_BUFF,V_CURR,INSTR(SUBSTR(:V_LN_BUFF,V_CURR,10),',',1,1)-1));
  v_curr       :=INSTR(LINEBUF,',',1,6)+1;
  v_COMM           :=RTRIM(SUBSTR(:V_LN_BUFF,V_CURR,INSTR(SUBSTR(:V_LN_BUFF,V_CURR,10),',',1,1)-1));
  v_curr       :=INSTR(LINEBUF,',',1,7)+1;
  V_DEPTNO         :=RTRIM(SUBSTR(:V_LN_BUFF,V_CURR,10));
  IF DATA_VALID(V_EMPNO) THEN
      :EMPNO:=V_EMPNO; :ENAME:=V_ENAME; :JOB:=V_JOB; :MGR:=V_MGR;
      :HIREDATE:=V_HIREDATE; :SAL:=V_SAL; :COMM:=V_COMM; :DEPTNO:=V_DEPTNO;
  END IF;
 
     
END IF;
next_record;
END LOOP;
FIRST_RECORD;
EXCEPTION
    WHEN no_data_found THEN
  Text_IO.Fclose(in_file);
  --COMMIT;
  WHEN OTHERS THEN
  NULL;
  previous_record;
END;

9 Save and Run the Form