Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 159 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 473 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
CLIENT_OLE2.get_num_property crashes for values with decimal point
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;
Best Answer
-
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).
Answers
-
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.
-
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
-
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.
-
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"):
-
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).
-
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.
-
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.