utPLSQL-cli fails with "no ocijdbc12 in java.library.path"

[alesana-san]

Hi!

tried to run a report via utplsql-cli like that:
utplsql.bat run login/pass@tns -f=ut_coverage_html_reporter -o=report.html
and get an error:
jdbc:oracle:oci8:****/****@tns: no ocijdbc12 in java,library.path jdbc:oracle:oci8:****/****@tns: Unknown host specified

Running like that:
utplsql.bat run login/pass@host:port/inst -f=ut_coverage_html_reporter -o=report.html
works like magic!

[pesse]

Hi @alesana-san
I assume the log is not complete, is it? There should be a line similar to jdbc:oracle:thin:****/**** ....
this would be the interesting part, though I guess it's the same: Unknown host specified.
It means that your TNS-name can't be resolved.
The two first errors just inform that you have no oracle thick-client installed. This is part of the new fallback mechanism introduced with 3.1.2

Can you provide the value of your ORACLE_HOME environment variable/check it's pointing to the correct location?
It is neede if you want to use TNS-names.

[alesana-san]

@pesse
other messages are:
jdbc:oracle:thin: ... Unknown Host Specified like you've said

I have Windows 7 machine and echo %ORACLE_HOME% doesn't return path. But tnsping works well.
Should I add ORACLE_HOME to my env parameters?

[pesse]

Yes, you should definitely setup ORACLE_HOME if you want to use TNS.
I don't know how tnsping works exactly, maybe it looks up the registry or something, but with utPLSQL-cli we have not much chance than to rely on environment variables (like sqlplus or sqlcl do, too)
Out of curiosity: Does sqlcl work with the given connect-string and without a set ORACLE_HOME?

https://www.oracle.com/database/technologies/appdev/sqlcl.html

[alesana-san]

I have TNS_ADMIN set to \network\admin but have no ORACLE_HOME set.
I guess my sqlplus looks into TNS_ADMIN to resolve TNS_STRING

[pesse]

Might be a nice enhancement to also look for TNS_ADMIN variable.
Can you check if it works with ORACLE_HOME?

[alesana-san]

Now I'm kinda confused because I have ORACLE_HOME in registry in a \HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g1. So I'm not sure if sqlplus takes info from registry or env params.
Will try to corrupt them and find out what param affects on connection via sqlplus and let you know

[alesana-san]

So changing TNS_ADMIN didn't have any effect. And changing a value in registry affected sqlplus so I'm guessing it should take tns data from it.

[pesse]

@alesana-san So it did work when you set ORACLE_HOME, right?
Maybe we should improve documentation to make it crystal clear that you should provide ORACLE_HOME environment variable when you want to use TNS-Names.
TNS_ADMIN, as far as I read, is more about the sub-path (which is standardized) so I don't see a benefit from interpreting it in cli at the moment (I am open for correction on this, though :) )

[alesana-san]

@pesse
So yeah, adding ORACLE_HOME to env variables helped and CLI worked.

IMHO, CLI should work like an Oracle Client and try to get ORACLE_HOME path from the registry like in my case sqlplus does (I don't have a preset variable ORACLE_HOME, just an ORACLE_HOME record in the registry with a correct path). I guess it could be an improvement for CLI.

[felipebz]

@pesse I agree with @alesana-san, the TNS_ADMIN variable should be considered, IMHO.

First, to follow the Oracle documentation:

By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory. Oracle Net will check the other directories for the configuration file. For example, the order checking the tnsnames.ora file is as follows:

1. The directory specified by the TNS_ADMIN environment variable. If the file is not found in the directory specified, then it is assumed that the file does not exist.

2. If the TNS_ADMIN environment variable is not set, then Oracle Net checks the ORACLE_HOME/network/admin directory.

[Bonus points if the cli scans the Windows registry:]

Note:
On Microsoft Windows, the TNS_ADMIN environment variable is used if it is set in the environment of the process. If the TNS_ADMIN environment variable is not defined in the environment, or the process is a service which does not have an environment, then Microsoft Windows scans the registry for a TNS_ADMIN parameter.

Below, some examples of configurations that, by default, doesn't have the "ORACLE_HOME/network/admin" directory expected by utPLSQL-cli:

• In a Oracle Forms Builder 12c (standalone) the TNSNAMES is located at the Forms Builder instance directory (defined at the installation, so it can be anywhere).
• In a Weblogic + Forms 12c server installation the TNSNAMES is in the directory USERDOMAIN_HOME\config\fmwconfig.
• In very old clients, like Oracle Forms 6i, the TNSNAMES is located at ORACLE_HOME\NET80\ADMIN.

Of course, one can create a special ORACLE_HOME\network\admin folder just to use in the utPLSQL-cli, but it's a kinda ugly workaround IMHO, considering that Oracle tools can handle these normally.

Currently, I recommend my coworkers to pass the complete connection string to utPLSQL-cli (in the form of <user>/<password>@<host>:<port>:<SID>), just to be sure that it works. It would be very convenient if the cli used the TNS_ADMIN instead.

[pesse]

Thanks for your informative input, @felipebz
I have absolutely no experience with Forms, so this is really the first-ever situation for me that TNSNAMES is not located in network/admin. One more thing learned :)

I'd suggest an approach like this:

• Look for ORACLE_HOME env-variable in
  • Java-Properties
  • Environment Variables
  • Registry (if windows)
• Look for TNS_ADMIN in
  • Java-Properties
  • Environment Variables
  • Registry (not yet sure where to search for, but should be possible)
  • Use default: $ORACLE_HOME/network/admin

I'd also assume that TNS_ADMIN value, if it is provided, would be a full path, not only a relative one from ORACLE_HOME.

[felipebz]

@pesse it looks good to me. 👍 And yes, the TNS_ADMIN variable is a full path like "C:\Oracle\Middleware\Oracle_Home\builder".

Please let me know if you need some help. I could work on this and send a pull request. ;-)

[pesse]

Sure, if you'd like to contribute we're more than happy!

[SebGt]

Just in case of, have a look on issue #90 .
Regards.