CLIENT_OLE2.get_num_property crashes for values with decimal point

Charon

Hello,

overall I am using webutil in Forms 10g successfully, but stumbled over the following problem with Excel.

My Environment:

Forms 10g 10.1.2.3.0

Jacob 1.6

Excel 2010

Java 1.6.0_45

When I am reading a number property from Excel, webutil sometimes just throws an exception with the not so helpful error number "-1".

This first happened to me with the code at the end, but different examples are possible.

With a font size of 22, the "Top" of the "Range" is 57, with font size 21 webutil throws the exception.

To check that my calling of Excel is correct, I implemented this example also in C# using Excel.Interop and in Java with directly calling Jacob. Both versions showed the same value for font size 22, but didn't crash with font size 21. Both show me the value 56,25 instead.

I later remembered to set webutils logging to "detailed" which also showed me a value of 56,25.

2016-Aug-04  14:25:44.199 WUO[setProperty()] Setting property WUO_OLE2_PROPNAME to Top2016-Aug-04  14:25:44.199 WUO[getProperty()] Getting property WUO_OLE2_GET_PROPERTY2016-Aug-04  14:25:44.199 WUO[getProperty()] Value of WUO_OLE2_GET_PROPERTY=56,252016-Aug-04  14:25:45.325 WUO[getProperty()] Getting property WUO_OLE2_ERROR2016-Aug-04  14:25:45.325 WUO[getProperty()] Value of WUO_OLE2_ERROR=-1,null,null,null,-12016-Aug-04  14:25:46.165 WUO[getProperty()] Getting property WUO_OLE2_ERROR2016-Aug-04  14:25:46.165 WUO[getProperty()] Value of WUO_OLE2_ERROR=-1,null,null,null,-1

And then something dawned upon me: please note the "," instead of a ".".

My PC runs with a german locale, so my Excel does so too. The Oracle Forms application runs with AMERICAN_AMERICA.WE8ISO8859P1 and thus a "." for the decimal point.

I think webutil tries to convert this to a NUMBER and just fails because of the unexpected format.

So what can i do to get Excel to return numbers in the american format? Can I somehow start Excel with a different locale?

This has to work on a lot of computers with both german, english and chinese locales.

Thanks in advance

Markus

Here the example code:

DECLARE  application CONSTANT CLIENT_OLE2.obj_type := CLIENT_OLE2.create_obj( 'Excel.Application' );  workbooks   CONSTANT CLIENT_OLE2.obj_type := CLIENT_OLE2.get_obj_property( application, 'Workbooks' );  workbook    CONSTANT CLIENT_OLE2.obj_type := CLIENT_OLE2.invoke_obj( workbooks, 'Add' );  worksheets  CONSTANT CLIENT_OLE2.obj_type := CLIENT_OLE2.get_obj_property( workbook, 'WorkSheets' );    args CONSTANT CLIENT_OLE2.obj_type := CLIENT_OLE2.create_arglist();BEGIN  CLIENT_OLE2.add_arg( args, 1 );      DECLARE    worksheet CONSTANT CLIENT_OLE2.obj_type := CLIENT_OLE2.get_obj_property( worksheets, 'Item', args );  BEGIN    CLIENT_OLE2.destroy_arglist( args );    CLIENT_OLE2.invoke( worksheet, 'Activate' );    CLIENT_OLE2.set_property( application, 'Visible', 1 );    CLIENT_OLE2.set_property( application, 'DisplayAlerts', 1 );      DECLARE      args CONSTANT CLIENT_OLE2.obj_type := CLIENT_OLE2.create_arglist();                cell CLIENT_OLE2.obj_type;    BEGIN      CLIENT_OLE2.add_arg( args, 1 );      CLIENT_OLE2.add_arg( args, 1 );      cell := CLIENT_OLE2.get_obj_property( worksheet, 'Cells', args );      CLIENT_OLE2.destroy_arglist( args );      CLIENT_OLE2.set_property( cell, 'Value', 'BLAHBLAH' );                DECLARE        font CONSTANT CLIENT_OLE2.obj_type := CLIENT_OLE2.get_obj_property( cell, 'Font' );      BEGIN        CLIENT_OLE2.set_property( font, 'Name', 'Wingdings' );        CLIENT_OLE2.set_property( font, 'Size', 21 ); -- < CRASH WITH Size=21, NOT WITH Size=22        CLIENT_OLE2.set_property( font, 'Bold', 1 );      END;    END;          DECLARE      args CONSTANT CLIENT_OLE2.obj_type := CLIENT_OLE2.create_arglist();            cell CLIENT_OLE2.obj_type;    BEGIN      CLIENT_OLE2.add_arg( args, 'G4' );      cell := CLIENT_OLE2.get_obj_property( worksheet, 'Range', args );      CLIENT_OLE2.destroy_arglist( args );                CLIENT_OLE2.set_property( cell, 'Value', 'BLAH' );                message( 'Left: ' || CLIENT_OLE2.get_num_property( cell, 'Left' ) );      message( 'Top: ' || CLIENT_OLE2.get_num_property( cell, 'Top' ) ); -- < CRASH HAPPENS HERE    END;  END;  CLIENT_OLE2.release_obj( workbooks );  CLIENT_OLE2.release_obj( application );EXCEPTION  WHEN CLIENT_OLE2.ole_error THEN    message( 'ole_error' );    message( 'errno: ' || CLIENT_OLE2.last_exception() );    DECLARE      ole_error_message VARCHAR2( 4000 );    BEGIN      IF( CLIENT_OLE2.last_exception( ole_error_message ) != 0 )        THEN          message( ole_error_message );      END IF;    END;    CLIENT_OLE2.release_obj( application );END;

Andreas Weiden

When we migrated from 6i to 10g (OLE to CLIENT_OLE) we saw that the performance of CLIENT_OLE is very poor, as each single statement is send over the network. We wrote our own "wrapper" for excel-OLE-statement in the form of a java-bean, which directly communicated with excel via JACOB and no webutil in between. Maybe this setup would also help you (as you already tried the pure java way you would only have to encapsulate it with a javabean).

Kalpataru

Hi @Charon,

I have not find any decimal point used in your code.

How you have used the decimal point ?

In font property the decimal values will not work.

Have you signed the jacob.jar file ?

If you have not signed the jacob.jar file then sign it and check.

If you have signed the jar file then Excel 2010 may cause the problem, do a checking with excel 2007.

Try this not tested write the Top in small case.

CLIENT_OLE2.get_num_property( cell, 'top' );

I hope it will solve your issue.

Andreas Weiden

I would search for solution in the general excel ole-sources rather than in the forms-forum. Maybe this is a start https://msdn.microsoft.com/de-de/library/office/ff195207.aspx

Charon

Hallo Kalpatura,

I don't send data with a decimal point to Excel. My problem arises when I want to get a value from Excel.

But this has nothing to do with signing the jacob.jar since in fact it works correctly for everything else. (it is signed btw.)

I only have access to Excel 2010 right now and we will upgrade to 2013 in the future.

Charon

Hello Andreas,

thanks for your tip, but I already tried this one. It is just used for entering values into Excel cells, so only affects the Property "Value" of a "Range". (interestingly, once set these also effect manual entry into Excel forever until you set this back)

Trying to get "Top" from a "Range" is more like quering an internal property (Which isn't even changeable through OLE. In Excel.Interop it's only implemented as a getter and not a setter).

I already searched the OLE documentation and several forums for this problem, but it seems to have nothing to do with OLE itself (as in OLE as an IPC).

It seems to have something to do with the way webutil opens Excel.

Perl for example seems to have a way to set the NLS for OLE objects (search for the string "NLS"):

http://elib.ru/Docs/Perl/site/lib/win32/ole/tpj.html

Andreas Weiden

When we migrated from 6i to 10g (OLE to CLIENT_OLE) we saw that the performance of CLIENT_OLE is very poor, as each single statement is send over the network. We wrote our own "wrapper" for excel-OLE-statement in the form of a java-bean, which directly communicated with excel via JACOB and no webutil in between. Maybe this setup would also help you (as you already tried the pure java way you would only have to encapsulate it with a javabean).

Kalpataru

Hi @Charon,

I don't understand why you have used more than one begin declare in you code.

Can you post your original code which fails why because you have given your fail code in messages ?

First try this code in this thread(it is for generating excel sheet) Client_OLE2.create_obj('Excel.Application') --> when it called from Oracle Form for second time desnt work 

if it works then change you code accordingly.

Charon

Sorry getting back to you this late.

I now had some more time to tinker with this and completely support Andreas answer.

I think I might even go so far and completely port it so it can run on the AS, or even port it to a standalone application in C#.

Thanks for your help.