Forum Stats

  • 3,853,698 Users
  • 2,264,256 Discussions
  • 7,905,434 Comments

Discussions

Oracle forms 12. Import data from Excel. how to read pseudo-numeric values as text

ciceroan
ciceroan Member Posts: 112 Bronze Badge

hi, Im using OLE2 package to import data from Excel sheet. my procedure copies values into a Data Block, then it stores them into DB


application OLE2.obj_type; 

workbooks OLE2.OBJ_TYPE; 

workbook OLE2.OBJ_TYPE; 

worksheets OLE2.OBJ_TYPE; 

worksheet OLE2.OBJ_TYPE; 

args OLE2.list_type;

....

   args:=OLE2.CREATE_ARGLIST;

OLE2.ADD_ARG(args,[row]);

OLE2.ADD_ARG(args, [col]);

 cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args); 

:block_vis.COL_ID:=OLE2.Get_Char_Property(cell,'Value');

...


ID field is a text value, and :BLOCK_VIS.COL_ID is VARCHAR2(10). I noticed that, when Excel sheet contains pseudo-numeric values (eg: 10, 20,...) , OLE2.GET_CHAR_PROPERTY reads them as numbers. furthermore, it truncates values to the first digit (ie: 10, 11,... are read as 1!). Vice versa, when values are preceeded by a single/double quote (eg: '10), they are read correctly. neither forcing excel column to Text works.

How can I fix this issue? I can not ask users to check every single cell value and put a preceeding quote.

thanks for help

Best Answers

Answers

  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 7,395 Employee

    In the future, please always include the complete product versions you are using. In this case you did not include the Forms version or DB version or client and server platform information or the Excel version.

    Regarding your issue, to be clear are you performing this (the OLE calls) on the server? And is this what you really want to do? Wouldn't you prefer to read the excel file on the user's machine? The process would be a bit slower, but would prevent the server from having to run many instances of Excel (one for each user).

    Regarding your comment:

    I can not ask users to check every single cell value and put a preceeding quote.

    If you believe you have a work-around, you don't need to ask customers to do anything. You can make the change in code.

    I would recommend creating a simple test case and contacting Oracle Support. Be sure your test case is not based on your own DB as Support will not be able to run the test case. Also, you will need to provide all the version information I mentioned above to Support if you decide to contact them.


    Michael Ferrante

    Senior Principal Product Manager

    Oracle

    Twitter: @OracleFormsPM

  • juliojgs
    juliojgs Member Posts: 599 Silver Badge

    As you say you send it to the database, you may consider taking the excel processing to plsql.

    google this:

    as_xlsx package oracle

    I switched to this long time ago, and never regretted setting loose OLE2.

  • ciceroan
    ciceroan Member Posts: 112 Bronze Badge

    hi Michael,

    Im using Forms 12.2.1.3.0 and Oracle Fusion Middleware 12c as Application Server; anyway, issue was holding on previous version (11g).

    issue is not related to DB, just to interface with Excel Sheet, so I created a very simple test consisting in reading two text columns (ID and DESC). As you will see, values preceeded by a quote (eg: '10, '20... ) are correctly read; while pseudo-numeric ID values are incorrect (10 is read as 1).

    About running OLE2 on server instead of client's computer, we detected that running CLIENT_OLE2 tools on client's computer was causing a critical loss of performance; that's why we choosed using OLE2 on server computer.

    here below the test procedure, in attachment the excel sheet

    regards




    --OPEN EXCEL SHEET AND READ VALUES

    PROCEDURE TEST_EXCEL IS

    application OLE2.obj_type; 

    workbooks OLE2.OBJ_TYPE; 

    workbook OLE2.OBJ_TYPE; 

    worksheets OLE2.OBJ_TYPE; 

    worksheet OLE2.OBJ_TYPE; 

    args OLE2.list_type; 

    cell OLE2.OBJ_TYPE; 

    V_FILE VARCHAR2(200) := '[SERVER PATH]\TEST_EXCEL.XLSX';

    BEGIN 

    --create the OLE2 application Object 

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

    --create the workbook object 

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

    --open the excel file 

    args := OLE2.create_arglist;

    OLE2.add_arg(args, v_file); 

    workbook := OLE2.invoke_obj(workbooks, 'Open', args); 

    OLE2.destroy_arglist(args); 

    --Select the required sheet (eg :sheet1) 

    worksheets := OLE2.get_obj_property(workbook, 'worksheets'); 

    args := OLE2.create_arglist; 

    OLE2.add_arg(args, 'Sheet1'); 

    worksheet := OLE2.get_obj_property(worksheets, 'Item', args);

    OLE2.destroy_arglist(args); 

    for r in 2..6

    loop-- number of rows 

      for c in 1..2 --cells

      loop

      BEGIN--number of columns 

      args:=OLE2.CREATE_ARGLIST;

      OLE2.ADD_ARG(args,r);

    OLE2.ADD_ARG(args, c);

      cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args); 

      MESSAGE('ROW ' || R || ' - COL: ' || C);

      MESSAGE('CELL VALUE: ' || OLE2.Get_Char_Property(cell,'Value'));

       OLE2.Release_Obj(cell); 

       exception

    when others then

       --Releasing all objects

         OLE2.Release_Obj(cell); 

         OLE2.Release_Obj(worksheet); 

         OLE2.Release_Obj(worksheets); 

         OLE2.Release_Obj(workbook); 

         OLE2.Release_Obj(workbooks); 

         OLE2.Invoke(application, 'Quit'); 

         OLE2.Release_Obj(application); 

         raise;

    END; 

      end loop;-- END COLUMNS LOOP

     end loop;--END ROWS LOOP



     --Releasing all objects

     OLE2.Release_Obj(cell); 

     OLE2.Release_Obj(worksheet); 

     OLE2.Release_Obj(worksheets); 

     OLE2.Release_Obj(workbook); 

     OLE2.Release_Obj(workbooks); 

     OLE2.Invoke(application, 'Quit'); 

     OLE2.Release_Obj(application); 


    exception

    when others then

         raise;

    End; 



  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 7,395 Employee

    @ciceroan

    I just tested what you described in Forms 12.2.1.4 and can reproduce the problem. I also tested with WebUtil and Jacob 1.20 and it works correctly (mostly - numbers are returned with a decimal 0 - so 10 comes back as 10.0).

    I would recommend contacting Oracle Support and discussing the issue. Assuming a bug has not already been filed on the issue and new one can be created. This will allow development to properly investigate the issue and fix it if possible and/or appropriate.


    Michael Ferrante

    Senior Principal Product Manager

    Oracle

    Twitter: @OracleFormsPM

  • juliojgs
    juliojgs Member Posts: 599 Silver Badge
    Answer ✓

    I had a glimpse of my times using ole2 ... @ciceroan Can you try get_char_property(Cell,'Text') instead of get_char_property(Cell,'Value') for that text cells?

  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 7,395 Employee
    Answer ✓

    @juliojgs

    I was thinking the same thing, but when I tried it I got the same result. It seems there may be a deeper problem here. Either Excel or how Forms is handling this OLE call in our OLE2 package.


    Michael Ferrante

    Senior Principal Product Manager

    Oracle

    Twitter: @OracleFormsPM

  • ciceroan
    ciceroan Member Posts: 112 Bronze Badge

    hi julio,

    sorry for the delay. i tested your solution and it works. Thank you all for help

  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 7,395 Employee

    @ciceroan

    Just so you know, I ran a few tests with the change suggested by @juliojgs and although it seems to work for many cases a few tests I ran exposed a similar problem.

    If you are fine with the behavior, that's good. However, as time permits I will work with the dev team to better understand what is happening.


    Michael Ferrante

    Senior Principal Product Manager

    Oracle

    Twitter: @OracleFormsPM