1 Reply Latest reply: Nov 17, 2013 7:23 AM by rdnits RSS

    Infinite Loop

    rdnits

      Can anyone please help me with the below issue

       

      I want to export the data from excel to DB,code is compiling without any issues and running but when i am executing the following code it is not exiting the loop

      In the progress field i am getting next row inserted and after that it keeps on inserting 0's to the table .

       

      DECLARE

      application OLE2.OBJ_TYPE;

      workbooks OLE2.OBJ_TYPE;

      workbook OLE2.OBJ_TYPE;

      worksheets OLE2.OBJ_TYPE;

      worksheet OLE2.OBJ_TYPE;

      cell OLE2.OBJ_TYPE;

      args OLE2.OBJ_TYPE;

      ctr NUMBER(12);

      cols NUMBER(2);

      name_var1 Number(38);

      name_var2 VARCHAR2(20);

      name_var3 VARCHAR2(20);

      name_var4 Number(5);

      name_var5 Number(5);

      filename varchar2(100);

        vcInsert                           VARCHAR2(3500);

       

       

       

      PROCEDURE OLEARG IS

      args OLE2.OBJ_TYPE;

      BEGIN

      args := OLE2.CREATE_ARGLIST;

      ole2.add_arg(args,ctr); -- row value

      ole2.add_arg(args,cols); -- column value

      cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell

      ole2.destroy_arglist(args);

      END;

       

       

       

      BEGIN

      filename :=GET_FILE_NAME('V:\Corp\IS\ALL\a\New folder','Test.xls','XLS Files (*.xls)|*.xls|');

      application := OLE2.CREATE_OBJ('Excel.Application');

      ole2.set_property(application,'Visible','true');

      workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

      args := OLE2.CREATE_ARGLIST;

      ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name

      workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);

      ole2.destroy_arglist(args);

      /*args := OLE2.CREATE_ARGLIST;

      ole2.add_arg(args,'Sheet1');

      worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);

      ole2.destroy_arglist(args); */

      worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');

      OLE2.SET_PROPERTY(worksheet , 'Value','Sheet1');

       

       

      ctr := 1; --row number

      cols := 1; -- column number

       

       

      :progress:= 'Excel File Open';

      FIRST_RECORD;

       

       

      LOOP

      OLEARG;

      name_var1 := ole2.get_num_property(cell,'Value'); --cell value of the argument

      cols := cols+1;

       

       

      OLEARG;

      name_var2 := ole2.get_char_property(cell,'Value'); --cell value of the argument

      cols := cols+1;

       

       

      OLEARG;

      name_var3 := ole2.get_char_property(cell,'Value'); --cell value of the argument

      cols := cols+1;

       

       

      OLEARG;

      name_var4 := ole2.get_num_property(cell,'Value'); --cell value of the argument

      cols := cols+1;

       

       

      OLEARG;

      name_var5 := ole2.get_num_property(cell,'Value'); --cell number value of the argument

      :progress:='First Row Fetched';

       

      IF ctr = 1 then

      vcInsert :=('INSERT INTO emp VALUES('||name_var1||' ,' '||name_var2||' ',' '||name_var3||' ','||name_var4||','||name_var5||')') ;

      FORMS_DDL(vcInsert);

      --commit_form;

      STANDARD.COMMIT;

      :progress:='First Row Inserted';

      ELSE

      NEXT_RECORD;

      vcInsert :=('INSERT INTO emp VALUES('||name_var1||',' '||name_var2||' ',' '||name_var3||' ','||name_var4||','||name_var5||')') ;

      FORMS_DDL(vcInsert);

      --commit_form;

      STANDARD.COMMIT;

      :progress:='Next Row Inserted';

      END IF;

      standard.commit;

       

       

      EXIT WHEN length(name_var1) = 0 or length(name_var1) is null;

      ctr := ctr + 1;

      cols := 1;

       

       

      END LOOP;

       

       

       

       

      :progress:=' Data inserted into table Successfully';

       

       

      OLE2.INVOKE(worksheet, 'Save');

      OLE2.INVOKE(worksheet,'CLOSE');

      OLE2.INVOKE(workbook, 'Save');

      OLE2.INVOKE(workbook,'CLOSE');

      OLE2.INVOKE(application,'Quit');

      OLE2.RELEASE_OBJ(cell);

      OLE2.RELEASE_OBJ(worksheet);

      OLE2.RELEASE_OBJ(worksheets);

      OLE2.RELEASE_OBJ(workbook);

      OLE2.RELEASE_OBJ(workbooks);

      OLE2.RELEASE_OBJ(application);

       

       

      END;

       

       

      I am having 2 errors

       

      1-> Code is not exiting the loop , i have to manually close the excel file to make it exit the loop, tried various possiblities but nothing is working

      2-> While inserting varchar it is just inserting ||name_var2|| and ||name_var3||

       

      Please help, stuck on the infinite loop since 2 days!!!!!!!!!