Apache OpenOffice (AOO) Bugzilla – Issue 11746
numeric(n) data from jdbc/oracle rendered as currency and not as number
Last modified: 2008-05-21 10:49:12 UTC
When retieving table data from a oracle database using the oracle jdbc driver and blackdown java j2se1.3 the oracle data type number(4)|number(2) is formatted as currency instead of number. Changing the column formatting and saving the request does not persist (the changed column formatting is not saved).
Hi Jürgen, I'm sorry to say that this is a bug in the JDBC from Oracle. They always return that a column is a currency field. Best regards, Ocke
Created attachment 4872 [details] Testcase jdbc/oracle metadata
Created attachment 4873 [details] start script to JdbcCheckup.java
Created attachment 4874 [details] logfile for start_java.sh
Unfortunatley i can come up with a working example, which i provide as attachments. It consists of threfiles "JdbcCheckup.java" (taken from oracle demos and slightly modified), and a shellscript "start_java.sh" starting the beast and a log file documenting the results gotten here. What did i use? : Oracle database 920 on debian woody on separate database server User scott, password tiger (oracle provided examples) Oracle client 920 on debian unstable Oracle jdbc12 from Oracle920 distribution
what does the ResultMetaData return for isCurrency?
So , I guess that you accept that this is not a OOo issue. Bye Marc
.
I do agree that the error is on oracle's side. I will file a bug against their jdbc implementation using metalink. Anyway the current situation renders the usage of oracle in Openoffice.org almost unusable. After digging in the oracle documentation it seems clear that oracle does not support a currency datatype at all. The database is providing the datatype "number" with a varying degree of precision. As a oracle client it is the clients responsibility to display the number dates as currency. So it is indeed a stupid decision to set "isCurrency" to true on fields that returns numbers. So my question/pledge/suggestion is twofold: 1. might it be possible to ignore the "isCurrency" switch in case you are using a oracle database ? 2. It would help tremendoulsy if it might be possible to save at least querys. Now you can change the displaydataype to number, but that gets lost after closing the dialog. Getting keys displayed as currency is really confusing.
> might it be possible to ignore the "isCurrency" switch in case you > are using a oracle database ? It might, with some kind of additional switch. It's hard to (reliably!) determine that we're using Oracle, and even if it would be easy, such generic magics like "if type XYZ, do ABC" is bad, because not future-proof. But we could probably try to add a pref (per data source), which cares for this. For 1.1, there may even be the possibility for a hidden pref, but at the moment I am targeting this to 2.0. Additionally, I change the type to ENHANCEMENT, because I do not really consider this a bug on OOo side, but a valid request for enhancement. > It would help tremendoulsy if it might be possible to save at least > querys. Now you can change the displaydataype to number, but that > gets lost after closing the dialog. Getting keys displayed as > currency is really confusing. Sorry, I am not really what you mean here - if you change the column formatting in the data source browser (not only in the data view of the query design, this is only temporary view), then this change should persist. And it does, to my best knowledge :) - everything else is indeed a bug. For the formatting not persisting in the temporary view in the query design - well, this could probably be changed, too, but I would consider this as another (valid :) RFE - if you really mean this, do you mind filing one?
This case can be closed. I can confirm that the display preferences can be saved, my fault sorry. By the way, do you know how make a form, connecting to oracle via jdbc writable? I get it readonly only.
Jürgen, thanks for confirming this. I'll close the issue > By the way, do you know how make a form, connecting to oracle via jdbc > writable There was an explanation about this recently in either users@dba or dev@dba - I don't remember exactly where it was. The summary is: There is a bug in the Oracle JDBC driver (each version has it's own bug :), which prevents this. Sorry.
I have just filed a bug at oracle metalink concerning the jdbc isCurrency issue. If you give me more detailed information about the jdbc failure concering read only access issue (test case would be good ) i will file a additional bug against oacle jdbc (if you do not have done so). Regards Juergen
Lastest news concerning the oracle-jdbc number format bug. After some messages have been exchanged in a tar at metalink (oracle bug tracking system) here are the results. Oracle denies that this behaviour is a bug. They claim it is a feature and that number und float datatypes may potentially hold currencies. Changing that behavour may break (!) applications. Oracle tries to prevent that. The maintainer promised to open a enhancement request which shall eventually lead to a reversed "isCurrency" value. I will keep you informed about that issue. Jürgen
Jürgen, thanks for the update. Sorry that I missed this one here: > If you give me more detailed information about the jdbc failure > concering read only access issue (test case would be good ) OOo relies on meta information about privileges returned by the driver, and the Oracle driver does not return proper privileges for tables owned by the user who is connected. Thus, OOo claims that such tables are not updateable/insertable. AFAIK, this is already raised at Oracle. OOo will feature a (still hidden, unfortunately) possiblity to switch off the asking for privileges - it will assume full privileges then.
change subcomponent to 'none'
Hi, according to http://development.openoffice.org/releases/Target_Milestone.html retarget to OOo_Later Bye Marc
Hi guys, Whatever happened with this? Regards and best wishes, Justin Clift
*** Issue 53396 has been marked as a duplicate of this issue. ***
*** Issue 54295 has been marked as a duplicate of this issue. ***
I have the same behaviour with OOO 2.2.1 and Oracle 10g using ojdbc14.jar.
Fixed in cws dba24d
Please verify. Thanks.
*** Issue 83469 has been marked as a duplicate of this issue. ***
clu->oj: like we have spoken: not to test from qa -> dev issue (has to be veryfied by dev)
@clu: How to verify - Create a new db for Oracle JDBC - When you ope dialog under Edit->Database->Advanced Settings, on the 2nd tabpage you now see an additional setting "Ignore currency...".
verified in cws
verified in master