$ mkstore -wrl $ORACLE_BASE/wallets -listCredential Oracle Secret Store Tool : Version 12.1.0.2 Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 2: ORCL scott 1: 192.168.1.100:1521/ORCL FRANKLIN $And I can test the functionality with:
$ echo "SELECT 'Connected as: '||user FROM DUAL;" | sqlplus -s / as sysdba Connected as: SYS $ echo "SELECT 'Connected as: '||user FROM DUAL;" | sqlplus -s '/@ORCL' Connected as: SCOTT $ echo "SELECT 'Connected as: '||user FROM DUAL;" | sqlplus -s '/@192.168.1.100:1521/ORCL' Connected as: FRANKLIN $(Note: I do have "SET HEADING OFF" in $ORACLE_HOME/sqlplus/admin/glogin.sql)
use DBI;
my $dbh = DBI->connect('dbi:Oracle:ORCL','','',{RaiseError=>1,PrintError=>1}) || die( $DBI::errstr . "\n" );
if ($dbh ne undef) {
my $sth = $dbh->prepare(qq{SELECT 'Connected as: '||user FROM DUAL});
$sth->execute();
print $sth->fetchrow_array()."\n";
$sth->finish();
$dbh->disconnect || warn $dbh->errstr;
}
Execution using the Oracle distributed Perl is as simple as:
$ $ORACLE_HOME/perl/bin/perl OraConnect.pl Connected as: SCOTT $Replacing the connect method data_source to use an EZconnect string shows it works equally as well:
$ grep Oracle OraConnect.pl
my $dbh = DBI->connect('dbi:Oracle:192.168.1.100:1521/ORCL','','',{RaiseError=>1,PrintError=>1}) || die( $DBI::errstr . "\n" );
$ $ORACLE_HOME/perl/bin/perl OraConnect.pl
Connected as: FRANKLIN
$
So with perl the only change required is to use an empty string for both the username and password.
import java.sql.*;
public class OraConnect {
public static void main(String[] argv) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Can't find the Oracle JDBC Driver");
e.printStackTrace();
return;
}
try {
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:/@192.168.1.100:1521/ORCL");
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("SELECT 'Connected as '||user FROM DUAL");
while (rset.next())
System.out.println(rset.getString(1));
rset.close();
stmt.close();
} catch (SQLException e) {
System.out.println("Connection Failed");
e.printStackTrace();
return;
}
}
}
If we compile and run as normal, an error is generated:
$ $ORACLE_HOME/jdk/bin/javac OraConnect.java $ $ORACLE_HOME/jdk/jre/bin/java -cp $ORACLE_HOME/jdbc/lib/ojdbc6.jar:. OraConnect Connection Failed java.sql.SQLException: PKI classes not found. To use 'connect /' functionality, oraclepki.jar must be in the classpath: java.lang.NoClassDefFoundError: oracle/security/pki/OracleWallet at oracle.jdbc.driver.PhysicalConnection.getSecretStoreCredentials(PhysicalConnection.java:1445) at oracle.jdbc.driver.PhysicalConnection.parseUrl(PhysicalConnection.java:1210) at oracle.jdbc.driver.PhysicalConnection.readConnectionProperties(PhysicalConnection.java:961) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:620) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:398) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:31) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:560) at java.sql.DriverManager.getConnection(DriverManager.java:582) at java.sql.DriverManager.getConnection(DriverManager.java:154) at OraConnect.main(OraConnect.java:20) $So we must specify the PKI classes. Adding $ORACLE_HOME/jlib/oraclepki.jar and trying again gives another error:
$ $ORACLE_HOME/jdk/bin/javac OraConnect.java $ $ORACLE_HOME/jdk/jre/bin/java -cp $ORACLE_HOME/jdbc/lib/ojdbc6.jar:$ORACLE_HOME/jlib/oraclepki.jar:. OraConnect Connection Failed java.sql.SQLException: ORA-01017: invalid username/password; logon denied at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:392) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:385) at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1018) at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:497) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:433) at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:950) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:639) at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:662) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:560) at java.sql.DriverManager.getConnection(DriverManager.java:582) at java.sql.DriverManager.getConnection(DriverManager.java:207) at OraConnect.main(OraConnect.java:16) $The ORA-01017 error is common when dealing with SEPS and Wallets and sometimes can be a bit misleading. When using OCI (such as standard SQLPlus or RMAN connections), the "ORA-12578: TNS:wallet open failed" error is usually returned when the wallet files cannot be found or read. However, the JDBC driver is working a little bit differently meaning that in this case it's not finding the wallet file and instead is trying to connect to the database with the null username and password (as provided in the JAVA code) and therefore is getting the ORA-01017 error returned from the database. (Database auditing can be used to confirm this.) Therefore we need to add the "-Doracle.net.wallet_location" JAVA command line argument and try one more time:
$ $ORACLE_HOME/jdk/bin/javac OraConnect.java $ $ORACLE_HOME/jdk/jre/bin/java -cp $ORACLE_HOME/jdbc/lib/ojdbc6.jar:$ORACLE_HOME/jlib/oraclepki.jar:. -Doracle.net.wallet_location=$ORACLE_BASE/wallets OraConnect Connected as FRANKLIN $As we can see, now it works. But we had to change the java command line arguments. If we don't want to modify the java call, an alternative is to add the wallet location as a "property" within the JAVA source code. Hence the " OraConnect.java" script becomes:
import java.sql.*;
import java.util.Properties;
public class OraConnect {
public static void main(String[] argv) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Can't find the Oracle JDBC Driver");
e.printStackTrace();
return;
}
try {
Properties props = new Properties();
props.setProperty("oracle.net.wallet_location","(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/wallets)))");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:/@192.168.1.100:1521/ORCL",props);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("SELECT 'Connected as '||user FROM DUAL");
while (rset.next())
System.out.println(rset.getString(1));
rset.close();
stmt.close();
} catch (SQLException e) {
System.out.println("Connection Failed");
e.printStackTrace();
return;
}
}
}
And by specifying the JAVA classes via the CLASSPATH variable, no extra command line arguments are required:
$ export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc6.jar:$ORACLE_HOME/jlib/oraclepki.jar:. $ $ORACLE_HOME/jdk/bin/javac OraConnect.java $ $ORACLE_HOME/jdk/jre/bin/java OraConnect Connected as FRANKLIN $As you may have noticed with these examples, JDBC THIN connections work best with EZconnect connection strings (or fully expanded OracleNet connection strings). To use a TNS service name we must also use the "-Doracle.net.tns_admin" command line option or include the "oracle.net.tns_admin" property in the source code.
# yum install -y unixODBC*Verifying the location of the ODBC Driver Manager configuration files:
# odbcinst -j unixODBC 2.3.1 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/oracle/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8 #I then need to add the Oracle driver information to /etc/odbcinst.ini, providing the full path to the libsqora.so file from the $ORACLE_HOME. After adding we see:
# tail -7 /etc/odbcinst.ini [OracleODBC] Description = Oracle ODBC Driver Driver = /u01/app/oracle/product/12.1.0/dbhome_1/lib/libsqora.so.12.1 Setup = FileUsage = CPTimeout = CPReuse =Now that the prerequisite work is done, testing as the "oracle" user again, I can setup some local DSNs in ~/odbc.ini. I've added two almost identical DSNs in my ~/odbc.ini file. The first one I've called "[ORCL_TNS]" and I specify "ServerName = ORCL" (referencing my TNS service name). The second DSN is called "[ORCL_EZ]" and it's the same except it uses "ServerName = 192.168.1.100:1521/ORCL" to force the EZconnect connection. In both cases the "UserID" DSN property is not set and I reference the Oracle database driver created previously via "Driver = OracleODBCDriver". Hence the entries are simply:
$ cat ~/.odbc.ini [ORCL_TNS] Driver = OracleODBCDriver ServerName = ORCL [ORCL_EZ] Driver = OracleODBCDriver ServerName = 192.168.1.100:1521/ORCL $Testing both entries :
$ echo "SELECT user FROM DUAL" | isql ORCL_TNS +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> SELECT user FROM DUAL +-------------------------------+ | USER | +-------------------------------+ | SCOTT | +-------------------------------+ SQLRowCount returns -1 1 rows fetched SQL> $ echo "SELECT user FROM DUAL" | isql ORCL_EZ +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> SELECT user FROM DUAL +-------------------------------+ | USER | +-------------------------------+ | FRANKLIN | +-------------------------------+ SQLRowCount returns -1 1 rows fetched SQL>Worked perfectly and was pretty simple to set up!
Using a Secure External Password Store with the JDBC Thin Driver (Doc ID 1441745.1) How to Configure the Unix ODBC Driver Manager (64-bit) on Linux x86-64 (Doc ID 742548.1) How To Configure Oracle External Password Store (Wallet) To Work With Oracle ODBC (Doc ID 1430666.1)
Ready to optimize your Oracle Database for the future?