Forum Stats

  • 3,853,765 Users
  • 2,264,266 Discussions
  • 7,905,444 Comments

Discussions

SEPS (wallet) seems ignored in Java stored procedure in OJVM

We are required to code Java stored procedure in DB (because needs to be called by PL/SQL) and make separate remote DB connection, and using wallet. Tried standalone Java code with using wallet is working properly. When put the same code to DB with "loadjava", publish the function in PL/SQL we tested and found it always responds with ORA-1017:

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)
   at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:639)
   at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:656)
   at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
   at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java)
   at java.sql.DriverManager.getConnection(DriverManager.java:633)
   at java.sql.DriverManager.getConnection(DriverManager.java:207)
   at dbconn.pass_string(dbconn:33)

When modified the code to feed set user and password in connection properties this Java stored procedure works as expected. The same code running "outside" OJVM is okay, the Java code has the required system properties set, and connection is using TNS alias, like:

System.setProperty("oracle.net.tns_admin","/u01/db/12.1.0/network/admin/UAT_ebs101");
System.setProperty("oracle.net.wallet_location","(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=/u01/db/12.1.0/network/admin/UAT_ebs101)))");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:/@JUAT")

But it seems it only recognizes the "oracle.net.tns_admin" property (as it found with the TNS alias) but not the wallet_location property when running in the OJVM.

This issue we tested happens in 19c (19.8) and also 12c (12.1), so would it be OJVM does not work with SPES by design (but I see no doc or info saying so)? Or anything missing in Java code certain properties need to be set to make it work?

I know when using SPES in Java we need to include "oraclepki.jar" in classpath, while in OJVM we do not have this option, would it be related?

Thanks.

geoff

Answers

  • Ghui-Oracle
    Ghui-Oracle Member Posts: 3 Employee

    Here is the simple Java code we used to test. Thanks.

    import java.text.SimpleDateFormat;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.*;
    import java.util.*;
    import java.io.StringWriter;
    import java.io.PrintWriter;
    
    public class dbconn {
    
    public static String pass_string(){
    
     try {
    
       Class.forName("oracle.jdbc.driver.OracleDriver");
       //Properties props = new Properties();
       //props.setProperty("user","apps");
       //props.setProperty("password","apps");
    
       // Environment specific information: TNS_ADMIN, wallet location
       // Also the TNS alias was created as wallet credential (e.g. JUAT)
       System.setProperty("oracle.net.tns_admin","/u01/db/12.1.0/network/admin/UAT_ebs101");
       System.setProperty("oracle.net.wallet_location","(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=/u01/db/12.1.0/network/admin/UAT_ebs101)))");
       System.setProperty("oracle.net.ssl_client_authentication","false");
       System.setProperty("oracle.net.ssl_version","0");
       //Connection con=DriverManager.getConnection("jdbc:oracle:thin:@JUAT", props);
       Connection con=DriverManager.getConnection("jdbc:oracle:thin:/@JUAT");
    
    
    PreparedStatement pstmt = con.prepareStatement("select 'Connected DB: '||name from v$database");
    ResultSet rs = pstmt.executeQuery();
    String col1 = new String();
    
    while (rs.next())
    {
    col1 = rs.getString(1);
    }
    
    if (con != null) {
      con.close();
      return col1;
    } else {
      return new String("Failed to get connection object");
    }}catch(SQLException e) {
         StringWriter sw = new StringWriter();
         PrintWriter pw = new PrintWriter(sw);
         e.printStackTrace(pw);
         String stacktrace = sw.toString();
    return new String("EXCEPTION (SQLException): " + stacktrace + "\n");
    } catch(Exception e) {
    return new String("EXCEPTION (Exception): " + e.getMessage() + "\n");
    }
    }
    
    public static void main(String[] args) {
      String rs = pass_string();
      System.out.println("Status:"+rs);
    }
    
    };
    
  • User_N3N2V
    User_N3N2V Member Posts: 2 Green Ribbon

    One quick question , Will get a different cwallet.sso file each database or we can use the same file for all databases. 

    we have around 25 db's How do we load cwallet.sso file before getting the connection ....

    Please give any suggestions ..


    System.setProperty("oracle.net.wallet_location", "(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=C:/Users/X/Desktop/SSL AWS/Oracle SSL/wallets)))");

    If we keep around 10 files in the same location... Will oralce driver will load automatically respective DB file....

    acwallet.sso , bwallet.sso, cwallet.sso etc..