Our company develops an application in health care (ORACLE DB + applications in Delphi).
One of our applications is used for dealing with interfaces (treating inbound files, for instance HL7 v2.x).
It is capable doing a lot of things (say, code mappings etc.), it can also take a look at the record in the DB to compare against what the received file says about an "item". Hence, we are using ORACLE syntax (that's what the developers knew best back when developing the application).
After a few of our recent updates, one of these statements fails (no error, but giving false result.
The really bad thing (for us) is: it still works well in SQLPlus:
SQL*Plus: Release 11.2.0.1.0 Production on Fr Mai 25 09:25:39 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Verbunden mit:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL> select
2 DECODE('string',
3 NULL, NULL,
4 'PV1'
5 ) from dual
6 ;
DECOD
-----
PV1
SQL>
But when we're running "the same thing" by our application, the Result is "PV" only (2 characters).
Sometimes, we're getting the correct (expected) 3-character result when adding one or two blanks to the result string:
4 'PV1 '
or
4 'PV1 '
We can work around the whole thing by, say, comparing NULL with an empty string here, or rewriting in a CASE statement, or ... But we're really stumped what's happening exactly.
I've researched a bit (mostly in here), and found the following discussion:
It seems in our case, ORACLE not only determines that type to be VARCHAR2, but VARCHAR2(2)?
Since SQLPlus is still fine, we let our DEV team investigate. The resolution was inconclusive: the error seems to be due to a combination of the (first result) parameter of the DECODE statment, the ORACLE version an ODAC.
I'm told SQLPlus is not using ODAC, so all is well in that regard (or rather: that should explain the difference between our application and SQLPlus).
In my book, since our application misbehaves, it should be corrected there.
My question here is: has anybody seen something similar or can make an educated guess as to what might go wrong here?
Anything that might shed more light and/or will allow me to get back to our DEV team is much appreciated!
Greetings from good old Europe!
NextName