How to connect to Oracle without TnsNames.ora?

[aehimself]

We all know the regular way to connect to an Oracle instance: Have your TnsNames.ora file, set your TNS_ADMIN environmental variable, put the connection descriptor in the TnsNames.Ora file and set ZConnection.Database to the descriptor name. But, can we connect to an Oracle database leaving all the TnsNames.ora bullshit behind?

As it turns out, yes, we can.

An average descriptor in TnsNames.ora will look something like this:

Code: Select all

DescriptorName = (
  DESCRIPTION =
  (
    ADDRESS = (PROTOCOL = TCP) (HOST = server.host.lan) (PORT = 1521)
  )
  (
    CONNECT_DATA =
      (SERVICE_NAME = DBNAME)
  )
)

The format is quite strict but removing (specific) padding and line breaks are allowed. So the above is - in the eye of oci.dll - equal to:

Code: Select all

DescriptorName=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server.host.lan)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME=DBNAME)))

As we don't really need a descriptor name (it is only needed to identify each entry in the file anyway) feel free to leave it out:

Code: Select all

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server.host.lan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBNAME)))

Now, we have a oneliner what we can insert into ZConnection.Database property. Set everything else right and you should be able to establish the connection!
I'm only writing this "guide" because I remembered reading something similar but never got any confirmation.

I wrote a TnsNames.ora parser for... reasons, which handles each descriptor as a separate class and can be serialized to a TnsNames.ora accepted string format. This class can be used to build your connection string on the fly, without care of the actual format itself.

I'll publish it once the final refinements are completed.

Now you know. The curse of TnsNames.ora nightmare can finally be lifted.
Happy hardcoding!

[aehimself]

ZConnection setup:

NoTnsNamesOra #1.png

TestApp code:

NoTnsNamesOra #2.png

Result:

NoTnsNamesOra #3.png

[MJFShark]

That's been possible since way back. Alternately you can use the EZConnect syntax: //server/sid (it can be more complex than that.)

https://docs.oracle.com/en/database/ora ... F9F79A7504

-Mark

[aehimself]

Nice!

I knew it is possible somehoe, I just had no idea Zeos is capable of doing that too until now.

[MJFShark]

I actually don't mind tnsnames.ora. I think I need to use it currently since I use the Oracle Wallet thing. However I find that a lot of issues occur if you don't use TNS_ADMIN to force its location. You can do that with an environment var set before the client load or you can set it in the registry.

btw. I've been looking into that 1406 error... some interesting stuff and some head scratching stuff lol. Did you know you can diagnose 1406 using the indicator variables? I just learned that and am hoping it adds some insight. The thing is that the individual define sizes are correct (actually way too big for utf-16, and so that doesn't seem to be the actual problem.) I've written my own "clean room" version of the statement handler and that has no problems with the same table and UTF16. More to come...

-Mark

[aehimself]

My huge issue in the UTF16 error (and the AL32UTF8 64-bit AV too) is my current debugging and this deep Zeos knowledge doesn't seem to be sufficient. I did play around in both and however I did achieve an effect - I did not find the root cause and therefore could not fix them myself.

I was hoping for an easy buffer overrun or type mismatch

Keep in mind that there was a fix in UTF16 for TZCachedResultSet as there were some encoding malformations a while ago, look for commit

584814045dcd7d80c165ef2e7d618285a9d839e0
By AEHimself: Let's see what Jenkins will say about this. Fix for TZCachedResultSet to function using UTF16.
marsupilami79 on 2/13/2022, 9:19:56 AM

Maybe they are related somehow.

[MJFShark]

My huge issue in the UTF16 error (and the AL32UTF8 64-bit AV too) is my current debugging and this deep Zeos knowledge doesn't seem to be sufficient. I did play around in both and however I did achieve an effect - I did not find the root cause and therefore could not fix them myself.

I'm looking at the UTF16 and ORA-01406 error. I'm not sure I know about the AL32UTF8 error you mention.

I was hoping for an easy buffer overrun or type mismatch

Agreed 100%. I feel like I checked all the obvious possibilities. Right now, I'm tracking down stuff related to ConSettings (which gets oddly messed up when UTF16 is used, just look at GetRawCharacterSet which changes it based on the Oracle NLS_CHARACTERSET setting and then partially changes the clientcodeset settings but the ResetCurrentClientCodePage procedure does some odd things too like retaining the codepage name while changing all the other values.) Also the CharWidth part of clientcodepage is changed in TZOracleConnection.Open, but without changing anything else. It's rather dizzying.

Keep in mind that there was a fix in UTF16 for TZCachedResultSet as there were some encoding malformations a while ago

I'm not sure it's related but I do have that change.

[MJFShark]

Nothing I did seemed to help until I replaced the current FRowsBuffer with my own buffers that I'm storing in each TZSQLVar. I have it working perfectly (so far lol) with UTF16. I'm not quite sure what this means, but whatever is causing the issue appears to happen in procedure TZOracleResultSet.Open and appears to relate to how it stores the data all in one big tbytes array with pointers into it.

-Mark

[marsupilami]

I am sorry to resurrect this thread but I wonder why we don't add a possibility for Zeos to internally create a correct connection string? Something like:

Code: Select all

  if HostName <> '' then begin
    ConnStr := '//' + HostName;
    if Port <> 0 then
      ConnStr := ConnStr + ':' + IntToStr(Port);
    if Database <> '' then
      ConnStr := ConnStr + '/' + Database;
  end else
    ConnStr := Database;

This would make the handling of Oracle more like we handle other databases too?

I think about this beacause I think about using Oracle Instant Client to be able to automatically test different client versions or to be able to have different client version on one system side by side, so I can test different server versions with their respective clients.

Opinions?

[MJFShark]

Looks good to me!

-Mark