Oracle 9i, inconsistent datatypes

fperez · Post by **fperez** » 04.12.2006, 17:19

I put this here because I have no luck looking for an Oracle forum in the forum index.

If I put a ZTable getting an oracle table with DATE fields, then I get an Oracle ORA-0932 Error (Inconsistent datatypes)

Is the same with a ZQuery and with a ZReadOnlyQuery components. And I have the same behaviour with protocol oracle and oracle 9i.

Are there any problem with this kind of fields ?

Thanks in advance...

fperez · Post by **fperez** » 05.12.2006, 09:33

Perhaps this could help.

With datetype DATE this function in ZPlainOracleDriver unit give me result -1 :

function TZOracle9iPlainDriver.DefineByPos(stmtp: POCIStmt;
var defnpp: POCIDefine; errhp: POCIError; position: ub4; valuep: Pointer;
value_sz: sb4; dty: ub2; indp, rlenp, rcodep: Pointer; mode: ub4): sword;
begin
Result := ZPlainOracle9i.OCIDefineByPos(stmtp, defnpp, errhp, position,
valuep, value_sz, dty, indp, rlenp, rcodep, mode);
end;

I cannot help far away because I got lost on OCIDefineByPos function. I cannot trace into.

Greetings.

fperez · Post by **fperez** » 05.12.2006, 10:14

I have found the problem (I think so), but I don't know how to solve it.

I have 3 ORACLE_HOMEs on my workstation and 2 oci.dll.

One of this oci.dll is 8.1.4 related and the other is 9.2.0.1. I have executed a test application opening a table with DATE types on another workstation with only an oci.dll related to 9.2.xxx and all was ok.

Ok. I am afraid ZConnection is getting my oci-8.1.4 dll instead of oci-9.2.0.1. How can I set what oci.dll ZConnection must be work with ?

I hope the solution don't be modifying oracle_home on HKLM and so on. It is a bit difficult because of another third party application dependencies but I am ready to read the best/right solution.

Greetings.

Post by **mdaems** » 05.12.2006, 10:21

What about your system PATH? Which dll comes first? That should be the one we use. You can try to edit "WINDOWS_DLL_LOCATION = 'oci.dll';" in ZPlainOracle9i.pas to point to your specific dll.

Mark

fperez · Post by **fperez** » 05.12.2006, 11:20

That's right.

If I change the SYSTEM PATH to this:
1º- Oracle_home: 9.2.xxx
2º- Oracle_home: 8.1.4

application runs fine.

If I put the SYSTEM PATH on this way:
1º- Oracle_home: 8.1.4
2º- Oracle_home: 9.2.xxx

Then I got a ora-00932 inconsistent datatypes.

But I have a lot of workstation clients with _only_ oracle_home (8.1.4) running and not 9.2.xxxx at all.

I though ZeosDBO was "transparent" with Oracle versions 8, 9i, 10g... doesn't it ?

gto · Post by **gto** » 05.12.2006, 12:47

You may try put the DLL into the folder of the Zeos application. It should work, as the current folder always come first than any location in path.

fperez · Post by **fperez** » 05.12.2006, 18:21

Ok, but it is ZeosDBO compatible with Oracle 8 client ?

Thanks..

Post by **mdaems** » 06.12.2006, 09:33

No Idea. I'm afraid not, but maybe somebody else has other experiences.

Maybe it can be solved by adding a new plaindriver file that wraps the Oracle 8 client dll, but then you need to know that dll's interface definition and how to map a C interface to delphi code.
When it's just some constant definitions or function names that changed it should be quite an easy thing to do. (something like comparing the dll header files and do those changes to a copy of the zeoslibplaindriver file for 9i)
If the logic of function calls between those two versions changed, it's more difficult to do.

In any case : it should be done by somebody who uses oracle 8. So, if you want to take your chance, please go on. We are very willing to help you and your additions will be added to our repository/distribution when it proves to be mature enough.

Mark
In all cases :

fperez · Post by **fperez** » 07.12.2006, 09:10

gto wrote:You may try put the DLL into the folder of the Zeos application. It should work, as the current folder always come first than any location in path.

I have tried it, but no luck. Opening tables results on strange behaviour, strange characters on oracle messages and so on...

mdaems wrote:Maybe it can be solved by adding a new plaindriver file that wraps the Oracle 8 client dll, but then you need to know that dll's interface definition and how to map a C interface to delphi code.

I have never worked with DLL's. Anyway I am going to search developer reference/documentation on OCI 8 dll and, perhaps, I try it. If it will be sucesfull I will came back to notice you.

More Info on the problem with date types: When connecting to 8i, the only field giving errors is the oracle DATE type. I am opening another tables (even with BLOB fields showing on a TImage) correctly.

Greetings.