search

utPLSQL / utPLSQL-cli

Command line client for invoking utPLSQL
Apache License 2.0
40 stars 15 forks source link

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

Open alesana-san opened 5 years ago

alesana-san commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

@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 commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

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

alesana-san commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

@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 commented 5 years ago

@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 commented 5 years ago

@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:

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 commented 5 years ago

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:

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 commented 5 years ago

@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 commented 5 years ago

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

SebGt commented 5 years ago

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