Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved How to capture Oracle Client info dynamically

  1. How-to
0
Votes
Undo
  1. Mary Jane Foster
    Mary Jane Foster
  2. PowerBuilder
  3. Tuesday, 14 September 2021 11:58 AM UTC

Hello:

I was wondering if there was a way to programmatically capture the information on the Oracle Client a user connects to when running a PB application.  

We are using PB 2017 (migrating to 2019 over the next 4-6 weeks).  We are implementing Oracle Instant Client soon and we are incrementally rolling it out to our end-users and we would like to test our planned implementation.

Thank you.

MJ

 

PB 2021 STD Framework OLE - Error
the limitations of various software developed by p...
Accepted Answer
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 15 September 2021 03:20 AM UTC
  2. PowerBuilder
  3. # Permalink

Maybe this select could solve your problem?

SELECT DISTINCT s.client_version
FROM v$session_connect_info s
WHERE s.sid = SYS_CONTEXT('USERENV', 'SID');

When I run it in SQLDeveloper on my PC, I get "12.1.0.2.0", because that's the driver that comes with my SQLDeveloper version I guess, and when I run it from a Powerbuilder Database painter, I get "19.3.0.0.0" which is correct in my case. So that means that if you run this select (maybe using a datastore or some embedded sql), you would get the version the user finds when running your application.

I'm not sure if that's enough for you to know if the instant client or the 'old' client is being used, but I think it would as long as you know which instant client version you're rolling out.
If not, you can dig through the registry and find out which client this version belongs to etc.

Make sure all of your users have "select" access rights for this v$session_connect_info table.

regards

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 15 September 2021 15:04 PM UTC
Great feedback Miguel! :-)
  1. Helpful 1
Mary Jane Foster
  1. Mary Jane Foster
  2. Wednesday, 15 September 2021 18:23 PM UTC
Miguel Leeuwe, you are my newest hero! This is working beautifully for us!



Thank you so much!



MJ

  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 15 September 2021 18:26 PM UTC
YW,

Hha thank you Mary Jane, I'm using it myself too in our applications now. It's very useful!

regards
  1. Helpful
There are no comments made yet.
Responses (3)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 14 September 2021 17:31 PM UTC
  2. PowerBuilder
  3. # 1

Hi Mary Jane;

   That is a great question and even more complicated when you add in ODBC, JDBC, ADO.net, OLEDB, etc connectivity to Oracle on top of the native OCI.dll connection mechanism you first mentioned. Then for each one of these you have different versions and implementations (ie: Full vs Instant client). So it can be a real dilemma for the application program (regardless of what language its written in) to understand the connectivity layer(s) its using as these things are basically "at arms length" from the App. The problem of course is when things go wrong, it is often key to knowing what pieces and versions of such are "in play" that might be causing the application's performance, connectivity, DML, etc issues!

   My suggestion though would be to contact your Oracle Support or pose this question in an Oracle support forum as I would anticipate that developers using for example VisualStudio, Java, Delphi, etc development languages (not just PB) must also have posed this question to the Oracle technical staff. I would think that they have responded to various developers on how to "introspect" the Oracle Client(s) information.

  Food for thought.  HTH

Regards ... Chris

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 14 September 2021 16:42 PM UTC
  2. PowerBuilder
  3. # 2

Hi MJ,

I'm not sure what you mean when you say "capture the information", but you could for example activate the TRACE on the connections. Things will run a bit slower, but a log file will be generated with lots of useful information.

In you SQLCA.DBMS = "ORA ...." put

SQLCA.DBMS = "TRACE ORA ....".

regards.

Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 14 September 2021 17:07 PM UTC
haha, I don't know whether the TRACE will give you that information either. It would be a matter of trying. But... having said that, Oracle is pretty complicated.

There's the possibility a user has set TNS_NAMES as an environment variable, there's also the registry entries in Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ORACLE\ODP.NET. If there's more than one client installed, I don't know how to determine which is the one really being used, maybe by doing a tnsping or looking at the PATH environment variable. I suggest you google a bit on this, maybe someone else has more insight on this than I have. Maybe the best solution is to make sure that IT let's all users have the correct Oracle client in the first place?

Sorry I can't help more.

regards.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 14 September 2021 17:13 PM UTC
If there's going to be 2 clients installed, maybe one way to assure your client app runs with a certain version is to modify the path by code, preceding everything with the Oracle path you want and setting the TNS_NAMES environment variable (if you are using tnsnames). Of course this means you'd have to know which client and which paths to it are being used, which might be different for every user, unless policies prevent that.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 15 September 2021 03:01 AM UTC
I've done some browsing and these 2 came up:

https://stackoverflow.com/questions/1171643/whats-the-best-way-to-determine-which-version-of-oracle-client-im-running

https://stackoverflow.com/questions/13188670/how-to-know-installed-oracle-client-is-32-bit-or-64-bit

People are suggesting all kinds of things like using "sqlplus /nolog" but there's no guarantee that sqlplus is installed. Others mention "tnsping" and write the output to a file, but if I'm not wrong, tnsping would depend on which client is found first in your PATH. The TNS_ADMIN or ORACLE_HOME environment variables might have been set to a different client as the one found first on the PATH.

There doesn't seem to be anyone giving a clear straitforward way of doing this.

I find this also an interesting topic, so if I find any useful information I'll let you know.

Meanwhile, like Chris said, your best shot is to ask Oracle, there might just not be a straightforward way of doing this.

regards
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.