Forum Stats

  • 3,727,536 Users
  • 2,245,406 Discussions
  • 7,852,848 Comments

Discussions

SQLcl 20.4: no ocijdbc21 in java.library.path

User_H3J7U
User_H3J7U Member Posts: 76 Blue Ribbon

Windows 10, Oracle client 19.3 x64, Oracle database 19.5

Tnsnames.ora is absent, connections configured through LDAP (sqlnet.ora+ldap.ora):

=> sqlnet.ora
NAMES.DIRECTORY_PATH = (LDAP,TNSNAMES,EZCONNECT)
NAMES.DEFAULT_DOMAIN = WORLD
NAMES.LDAP_CONN_TIMEOUT = 3

=> ldap.ora
DIRECTORY_SERVERS = (myldap:389)
DIRECTORY_SERVER_TYPE = OID
DEFAULT_ADMIN_CONTEXT = "dc=world"

=> tnsping mydb
Used parameter files:
C:\193x64\cl\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (description=(address=(protocol=tcp)(host=mydbhost)(port=1521))(connect_data=(service_name=mydb)))
OK (30 msec)

sqlcl 20.4 returns error:

=> sql usr/[email protected]
 USER          = usr
 URL           = jdbc:oracle:oci8:@mydb
 Error Message = no ocijdbc21 in java.library.path: [C:\Program Files\Java\jdk-11.0.9\bin, C:\windows\Sun\Java\bin, C:\windows\system32, C:\windows, C:\193x64\cl\bin, C:\Program Files\Java\jdk-11.0.9\bin, C:\Program Files\Java\jdk-11.0.9\bin, .]
 USER          = usr
 URL           = jdbc:oracle:thin:@mydb
 Error Message =  /: The Network Adapter could not establish the connection (CONNECTION_ID=JI6dJ5JuRVaHgl54yfjVww==)
 ...

But sqlcl 20.4 connects successfully with direct ldap url (ezconnect?):

=> sql usr/[email protected]://myldap:389/mydb,cn=OracleContext,dc=world
SQLcl: Release 20.4 Production on пн янв. 25 14:43:56 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Пн Янв 25 2021 14:43:57 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

sqlcl 20.3 connects successfully via ldap alias and direct ldap url.

Does SQLcl 20.4 works with Oracle Client 21c only?

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 76 Blue Ribbon

    SQLcl 20.4 from SQL Developer 20.4 working with LDAP alias too. It uses ocijdbc19 library (my oracle client is 19.3).

  • User_H3J7U
    User_H3J7U Member Posts: 76 Blue Ribbon

    SQLcl 20.4 uses own copy lib\ocijdbc8.jar from OracleClient 21. SQLcl 20.3 and SQLDeveloper 20.4 uses own copy lib\ocijdbc8.jar from OracleClient 19. I replaced ocijdbc8 from my oracle_home 19c and SQLcl 20.4 is now worked.

    There is no reason to use a specific library to work with an arbitrary client. Or the program should load the library dynamically from oracle home or parse LDAP settings itself.

  • User_V0ZJV
    User_V0ZJV Member Posts: 1 Green Ribbon

    Hi

    I install SQLcl 20.4 and same error

    no ocijdbc21 in java.library.path

    I have oracle 19.0 client.


    Is there a way to overcome this issue?


    Regards

  • User_OOTEX
    User_OOTEX Member Posts: 2 Green Ribbon

    Hello

    I am experiencing the same issue on W10 SQLcl 20.4 when attempting to connect to XE the default database included in Oracle Express 18c

    no ocijdbc21 in java.library.path

    Can someone please suggest a connect solution by viewing the verbose inline output below

    ./sql -verbose -noupdates /nolog

    SQLcl: Release 20.4 Production on Wed Mar 31 10:27:36 2021 Copyright (c) 1982, 2021, Oracle. All rights reserved.

    2021-03-31 10:27:36.506 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:SOFTWARE\ORACLE\KEY_OraDB18Home1

    2021-03-31 10:27:36.544 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

    2021-03-31 10:27:36.545 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

    2021-03-31 10:27:36.545 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

    2021-03-31 10:27:36.545 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:SOFTWARE\ORACLE\KEY_OraDB18Home1

    2021-03-31 10:27:36.546 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

    2021-03-31 10:27:36.547 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

    2021-03-31 10:27:36.547 INFO  oracle.dbtools.raptor.utils.TNSHelper getOracleHome Checking found key for ORACLE_HOME:null

    2021-03-31 10:27:36.549 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPlusProviderForSQLPATH getOracleEnv Checking found key for ORACLE_HOME:SOFTWARE\ORACLE\KEY_OraDB18Home1

    2021-03-31 10:27:36.549 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPlusProviderForSQLPATH getOracleEnv Checking found ORACLE_HOME:C:\app\rmj6\oracle_express\18.0.0\dbhomeXE\dbs

    2021-03-31 10:27:36.550 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPlusProviderForSQLPATH getOracleEnv Checking found key for ORACLE_HOME:null

    2021-03-31 10:27:36.551 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPlusProviderForSQLPATH getOracleEnv Checking found key for ORACLE_HOME:null

    2021-03-31 10:27:36.552 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPlusProviderForSQLPATH getOracleEnv Checking found key for ORACLE_HOME:null


    SQL> connect sys/[email protected]:1521/XE as SYSDBA

    2021-03-31 10:32:30.676 INFO  oracle.dbtools.raptor.utils.TNSHelper checkForTns Checking for tnsnames.* in :C:\Users\rmj6

    2021-03-31 10:32:30.678 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking Registry for ORACLE_HOME

    2021-03-31 10:32:30.678 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking found key for ORACLE_HOME:SOFTWARE\ORACLE\KEY_OraDB18Home1

    2021-03-31 10:32:30.679 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking found key for TNS_ADMIN:null

    2021-03-31 10:32:30.680 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSFileContent Checking found ORACLE_HOME:C:\app\rmj6\oracle_express\18.0.0\dbhomeXE

    2021-03-31 10:32:30.680 INFO  oracle.dbtools.raptor.utils.TNSHelper getTNSEntries Using TNS info from :C:\app\rmj6\oracle_express\18.0.0\dbhomeXE\network\admin

    2021-03-31 10:32:30.756 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL Attempting to connect using URL= "jdbc:oracle:oci8:@localhost:1521/XE"

    2021-03-31 10:32:30.762 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:<clinit>:38:No Message

    2021-03-31 10:32:30.762 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:<clinit>:45::ORest driver loaded

    2021-03-31 10:32:31.172 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:acceptsURL:80:No Message

    2021-03-31 10:32:31.338 INFO  oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL Attempting to connect using URL= "jdbc:oracle:thin:@localhost:1521/XE"

    2021-03-31 10:32:31.339 INFO  oracle.dbtools.jdbc.util.LogUtil log oracle.dbtools.jdbc.orest.Driver:acceptsURL:80:No Message

     USER         = sys

     URL          = jdbc:oracle:oci8:@localhost:1521/XE

     Error Message = no ocijdbc21 in java.library.path

     USER         = sys

     URL          = jdbc:oracle:thin:@localhost:1521/XE

     Error Message = ORA-01017: invalid username/password; logon denied


    2021-03-31 10:32:33.948 INFO  oracle.dbtools.raptor.scriptrunner.commands.NLSLANGListener runOnConnect NLS_LANG is not set

    SQL>


    Thank you

  • User_A5FJV
    User_A5FJV Member Posts: 3 Green Ribbon

    Hi, do we have a work around on this error ? no ocijdbc21 in java.library.path

  • User_H3J7U
    User_H3J7U Member Posts: 76 Blue Ribbon

    The thin connection works fine. The workaround for thick connection i described in the comment above - replace ojdbc8.jar to the corresponding file from the oracle home being used.

    But the ojdbc8 of earlier versions does not support all the functionality of new SQLcl 20.4. For example, in 19.3 client the oracle.sql.json.* package is missing. It is used by sqlcl to format the output of query.

    SQL> select /*insert*/ * from dual;
    REM INSERTING into DUAL
    SET DEFINE OFF;
    Insert into DUAL (DUMMY) values (Exception in thread "main" java.lang.NoClassDefFoundError: oracle/sql/json/OracleJsonDatum
           at oracle.dbtools.raptor.utils.DataTypesUtil.stringValue(DataTypesUtil.java:406)
    ...
    
  • User_A5FJV
    User_A5FJV Member Posts: 3 Green Ribbon
    edited April 14

    Thanks for sharing. So thin works fine with 20.4 ojdbc8.jar, but if you have 19.3 client, replaced ojdbc8.jar from oracle_home 19c.. same jar file name but built differently?

  • User_H3J7U
    User_H3J7U Member Posts: 76 Blue Ribbon

    LDAP aliases can be overwritten with local sqlcl (+sql developer) net aliases. Then a thin client is used and ocijdbc21.dll is not needed.

    D:\>sql /nolog
    
    SQLcl: Release 21.1 Production on вт май. 32 25:52:60 2021
    
    Copyright (c) 1982, 2021, Oracle. All rights reserved.
    
    SQL> connect user/[email protected]
     USER         = user
     URL          = jdbc:oracle:oci8:@dbalias
     Error Message = no ocijdbc21 in java.library.path: [...]
     ...
    
    SQL> host tnsping dbalias
    
    Used parameter files:
    C:\193x64\network\admin\sqlnet.ora
    
    Used LDAP adapter to resolve the alias
    Attempting to contact (description=(address=(protocol=tcp)(host=dbhost)(port=1521))(connect_data=(server=dedicated)(service_name=dbalias)))
    OK (40 msec)
    
    SQL> net dbalias=dbhost:1521/dbalias;
    SQL> connect user/[email protected]
    Connected.
    

    The NET command permanently stores aliases to %APPDATA%\sdsql\netEntries.xml

    User_A5FJV
  • User_H3J7U
    User_H3J7U Member Posts: 76 Blue Ribbon

    LDAP specific solution.

    LDAPCON is SQLcl variable with jdbc-url pattern. Once is set in the SQLcl session, it affects subsequent connect commands. But it is not saved between SQLcl runs.

    Setting a variable in the OS solves the problem of connecting through a thick client when using LDAP.

    C:\> setx LDAPCON jdbc:oracle:thin:@ldap://myldap:389/#ENTRY#,cn=OracleContext,dc=world

    (!) The windows command setx does not change the value in the current process.

  • User_OOTEX
    User_OOTEX Member Posts: 2 Green Ribbon

    @User_H3J7U

    I do not understand on what basis you are saying that "The thin connection works fine. "

    My March 31st ./sql -verbose -noupdates /nolog output shows that the thin connection does NOT work fine. I am still looking for a solution. I was hoping someone could view the output of my -verbose login and suggest a solution.

    I believe you are proposing a workaround to the thin NOT working by possibly using a thick connection

    " replace ojdbc8.jar to the corresponding file from the oracle home being used."

    This statement is VERY unclear to me. I do not understand which ojdbc8.jar is the 'correct' *.jar to use. Are you saying copy the ojdbc8.jar file from the path of ./sql that you want to use as in copy the standalone sqlcl/lib/ojdbc8.jar to \oracle_express\18.0.0\dbhomeXE\jdbc\lib\ojdbc8.jar?

    Because I tried this and that also resulted in the same no ocijdbc21 in java.library.path error.

    In a perfect world I'd like to be able to use the thin client. Why are you saying "the thin connection works fine" I am unable to connect to XE using thin client. Whereas i can connect to *OTHER oracle databases using the thin client without issue.

  • User_H3J7U
    User_H3J7U Member Posts: 76 Blue Ribbon

    @User_OOTEX

    You do two atempts. The first attempt is thick:

    URL = jdbc:oracle:oci8:@localhost:1521/XE

    Error Message = no ocijdbc21 in java.library.path


    The second attempt is thin and connect is successful with database error:

    URL = jdbc:oracle:thin:@localhost:1521/XE

    Error Message = ORA-01017: invalid username/password; logon denied

  • User_OOTEX
    User_OOTEX Member Posts: 2 Green Ribbon

    Thank you @User_H3J7U


    You think so?? I would love this simple explanation...I like you you are showing the first error is emanating from "oci8" and the second error from "thin". But just so I understand you correctly. You believe that my verbose login shows that the my SQLcl command of connect sys/[email protected]:1521/XE as SYSDBA 


    is trying connect with a THICK client oci8 (which fails due to "Error Message = no ocijdbc21 in java.library.path" )


    then and only then SQLcl goes on to attempt a thin client connection which 'succeeds' but for a bad username/password.

     Error Message = ORA-01017: invalid username/password; logon denied


    You believe that is what is being shown to occur here??

    USER     = sys

     URL     = jdbc:oracle:oci8:@localhost:1521/XE

     Error Message = no ocijdbc21 in java.library.path

     USER     = sys

     URL     = jdbc:oracle:thin:@localhost:1521/XE

     Error Message = ORA-01017: invalid username/password; logon denied


    That would be GREAT, but I have serious doubts that is the only thing that is going wrong with my thin connection attempts. And the reason for that is that I CAN connect to XE using this user/password combination using SQL*Plus. Additionally I can connect to XE using user jd password joedata (all lowercase) by using SQL*Plus. As shown below. 

    sqlplus /nolog

    SQL*Plus: Release 18.0.0.0.0 - Production on Wed Apr 21 08:59:32 2021 Version 18.4.0.0.0

    Copyright (c) 1982, 2018, Oracle. All rights reserved.

    SQL> conn system/[email protected]:1521/XEPDB1 as sysdba

    Connected.

    SQL> exit

    Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0

    PS C:\Users\rmj6> sqlplus /nolog

    SQL*Plus: Release 18.0.0.0.0 - Production on Wed Apr 21 09:03:19 2021

    Version 18.4.0.0.0

    Copyright (c) 1982, 2018, Oracle. All rights reserved.

    SQL> conn jd/[email protected]:1521/XEPDB1 as sysdba

    Connected.


    This leads me to believe the issue with SQLcl is NOT a user/password issue. 

    When I use both of these user/password combinations I get the same result from SQLcl

    bin\sql\sqlcl\bin> ./sql -verbose -noupdates /nolog

    SQLcl: Release 20.4 Production on Wed Apr 21 09:13:23 2021

    SQL> connect system/[email protected]:1521/XE as SYSDBA

    USER     = system

     URL      = jdbc:oracle:oci8:@localhost:1521/XE

     Error Message = no ocijdbc21 in java.library.path

     USER     = system

     URL      = jdbc:oracle:thin:@localhost:1521/XE

     Error Message = ORA-01017: invalid username/password; logon denied


    connect jd/[email protected]:1521/XE as SYSDBA

     USER     = jd

     URL      = jdbc:oracle:oci8:@localhost:1521/XE

     Error Message = no ocijdbc21 in java.library.path

     USER     = jd

     URL      = jdbc:oracle:thin:@localhost:1521/XE

     Error Message = ORA-01017: invalid username/password; logon denied


    This leads me to believe that the SQLcl thin client is failing to connect, but NOT because of a bad user/password. I believe the error message is indicating that my thin connection has some problem with my java.library.path


    Can anyone provide greater clarity on this verbose output? What other steps should I take to further drilldown/troubleshoot my connection issue?

  • User_OOTEX
    User_OOTEX Member Posts: 2 Green Ribbon

    led me to use sqlplus to Grant “CREATE SESSION” to user JD

     

    PS C:\Users\rmj6> sqlplus /nolog

     

    SQL*Plus: Release 18.0.0.0.0 - Production on Tue May 4 15:17:08 2021 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved.

     

    SQL> conn system/[email protected]:1521/XEPDB1 as sysdba

    Connected.

    SQL> GRANT CREATE SESSION TO JD;

    Grant succeeded.

     

    Now miraculously, I can now FINALLY use SQLcl to:

     

    connect jd/[email protected]:1521/XEPDB1

     

    SQL> show connection

    CONNECTION:

    [email protected]:oracle:thin:@localhost:1521/XEPDB1

    CONNECTION_IDENTIFIER:

    localhost:1521/XEPDB1

    CONNECTION_DB_VERSION:

    Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

    Version 18.4.0.0.0

    NOLOG:

    false

    PRELIMAUTH:

    False

     

    For some reason I still can’t connect with SQLcl as user sys: 

    ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

    ..but that should not matter for my immediate purposes anyway. So marking this as resolved.


    Thank you all for the assistance.

Sign In or Register to comment.