Setting up Maven to retrieve ojdbc8.jar

googling around in StackOverflow there is a huge variety of approaches to this very common problem: you must add the artifact to your build, but it's not available in Maven Central.... what to do?

Some resort to downloading it manually and deploying it to the local Maven repo. Some even include the file in their WEB-INF/lib folder in their SCM project. Some use some third party public repositories (like Atlassian, code.lds.org, ... ) who graciously host these artifacts.... all fine when you play on your PC, but in a serious company with strict security control all this would not be allowed. Some folks simply cowboy-style put it somewhere in their HD and add the external JAR to Eclipse.... what happens next, they don't really care, as long as it works on their machine.

Oracle hosts these artifacts in their Public Oracle Maven repository, but you need to authenticate yourself (for which reason, it's totally obscure to me!)

https://docs.oracle.com/middleware/1213/core/MAVEN/config_maven_repo.htm#MAVEN9016 here how to setup maven to connect to the Oracle repo (basically: in settings.xml you have to declare the server maven.oracle.com authenticating with your user, the in your pom.xml you must declare a rerpository with id matching this maven.oracle.com server, then a pluginRepository with id again maven.oracle.com. At this point you can declare the dependency

<dependency>
   <groupId>com.oracle.jdbc</groupId>
   <artifactId>ojdbc8</artifactId>
   <version>12.2.0.1</version>
  </dependency>

This post explains it in a lot of detail https://blogs.oracle.com/dev2dev/get-oracle-jdbc-drivers-and-ucp-from-oracle-maven-repository-without-ides

To make things much more complicated, the repository is not browsable https://maven.oracle.com/com/oracle/ojdbc8/ ... how to determine it content, no clue!


See also https://stackoverflow.com/questions/9898499/oracle-jdbc-ojdbc6-jar-as-a-maven-dependency] and https://stackoverflow.com/questions/1074869/find-oracle-jdbc-driver-in-maven-repository

https://mvnrepository.com/artifact/com.oracle/ojdbc6/12.1.0.1-atlassian-hosted to get ojdbc6.jar from maven (atlassian hosted!)

https://developer.atlassian.com/docs/advanced-topics/working-with-maven/atlassian-maven-repositories to configure atlassian repo in pom.xml



IMPORTANT: when running in Eclipse, make sure you are NOT using the Embedded installation of Maven while you are configuring an EXTERNAL Maven configuration.... this multiplicity of installations and configurations only makes the developer's life more miserable.... IMHO it's better to have an independent, external, universal installation rather than an embedded one.... again another major fuck-up in Eclipse design. Forget Eclipse, use Netbeans and Intellij.


CODE: a working pom.xml is available here https://github.com/vernetto/JavaMonAmour/tree/master/oracletest

Spring JDBC tutorial by JavaBrains

EntityManager and statement timeout

We spoke here about Statement Timeout to set on a Datasource http://www.javamonamour.org/2013/04/statement-timeout-on-datasource.html

If you use an EntityManager, you don't have access to the javax.sql.Statement object. This is the beauty of abstraction (sarcasm here): it prevents you from using the full power of the underlying technology, and it forces you to awkward acrobatics.

I would give a try to javax.persistence.query.timeout

http://stackoverflow.com/questions/24244621/set-timeout-on-entitymanager-query

https://docs.jboss.org/hibernate/entitymanager/3.6/reference/en/html_single/

query.setHint("javax.persistence.query.timeout", 2000);

see https://docs.oracle.com/javaee/6/api/javax/persistence/Query.html

but it works (actually, they told me it doesn't work AT ALL with WebLogic 12 and Eclipse JPA) only if you use Queries with EntityManager....

oracle.jdbc.driver.OraclePreparedStatement.setupDbaBindBuffers ArrayIndexOutOfBoundsException

We occasionally get this stacktrace in an application processing XA transactions

Caused By: java.lang.ArrayIndexOutOfBoundsException: -1
 at oracle.jdbc.driver.OraclePreparedStatement.setupDbaBindBuffers(OraclePreparedStatement.java:3528)
 at oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers(OraclePreparedStatement.java:3046)
 at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:12194)
 at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:246)
 at weblogic.jdbc.wrapper.PreparedStatement.executeBatch(PreparedStatement.java:216)
 at weblogic.store.io.jdbc.ReservedConnection.executeBatchForStatement(ReservedConnection.java:1204)
 at weblogic.store.io.jdbc.JDBCStoreIO.flushInner(JDBCStoreIO.java:2603)
 at weblogic.store.io.jdbc.JDBCStoreIO.flushWithRetry(JDBCStoreIO.java:2413)
 at weblogic.store.io.jdbc.JDBCStoreIO.flush(JDBCStoreIO.java:1999)
 at weblogic.store.io.jdbc.JDBCStoreIO.flush(JDBCStoreIO.java:1955)
 at weblogic.store.internal.PersistentStoreImpl.synchronousFlush(PersistentStoreImpl.java:1143)
 at weblogic.store.internal.PersistentStoreImpl.run(PersistentStoreImpl.java:1116)
 at java.lang.Thread.run(Thread.java:745)

It seems there is a patch for this
"Getting ArrayIndexOutOfBoundsException When Doing ExecuteBatch With Oracle JDBC Driver 12.1.0.2 (Doc ID 1985195.1)"
Patch 19002423

test-connections-on-reserve

If you get:

java.sql.SQLException: Protocol violation: [ 0, ] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:536)
java.sql.SQLRecoverableException: Closed Connection at oracle.jdbc.driver.PhysicalConnection.needLine(PhysicalConnection.java:4220) at oracle.jdbc.driver.T4CXAResource.doStart(T4CXAResource.java:187)

then make sure that /jdbc-data-source/jdbc-connection-pool-params/test-connections-on-reserve is set to true

-Dweblogic.jdbc.ignoreExceptionsWhileCreatingInitialCapacity=true

I have just discovered the existence of this flag -Dweblogic.jdbc.ignoreExceptionsWhileCreatingInitialCapacity=true , whose name is quite self explanatory. However I found no documentation on it.
There is a note (Doc ID 1557843.1) in support saying that this flag is not supported and not really recommended.
I have personally always used InitialCapacity = 0, I don't care so much about losing performance on the first connection, I hate the AppServer to go in FAILED state if only ONE Datasource (which might be used only in some rare use case) is not available...
But again you MIGHT need this flag for special cases.... so.... do as you like, just don't rely too much on this flag...
See also -Dweblogic.deployment.IgnorePrepareStateFailures=true to the same purpose.

WebLogic dbping

The good old weblogic dbping utility seems to require the weblogic.jdbc.oracle.OracleDriver class with the ORACLEB option, and I have no clue where to find it (not in /opt/oracle/fmw11_1_1_5/oracle_common/modules/oracle.jdbc_11.1.1/ojdbc6dms.jar) , so... I wrote my little tool:

package com.acme.osb.db;

import java.sql.DriverManager;
import java.sql.SQLException;

public class DBPing {
 public static void main(String[] args) throws ClassNotFoundException, SQLException {
  Class.forName("oracle.jdbc.driver.OracleDriver");
  System.out.println("length " + args.length);
  String user = args[0];
  String password = args[1];
  String url = args[2];
  String now = new java.util.Date().toString();
  System.out.println(now + " user= " + user + " password=" + password + " url=" + url);
  java.sql.Connection conn = DriverManager.getConnection(url, user, password);
  System.out.println("ok");
 }
}

run this
java com.acme.osb.db.DBPing myuser mypassword "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=myport))(CONNECT_DATA=(SERVICE_NAME=myservicename)))"

oracle.jdbc.ReadTimeout for oracle.sql.CLOB.getChars

we often have this stuck thread on oracle.sql.CLOB.getChars:

####<Jan 6, 2014 10:34:39 AM CET> <Error> <WebLogicServer> <hqchacme110> <osbpr1ms3> <[ACTIVE] ExecuteThread: '19' for queue:
'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <d1a7b26be2c41106:7f955c62:143498f1087:-8000-00000000001dcb6a> <1389
000879486> <BEA-000337> <[STUCK] ExecuteThread: '15' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "708"
seconds working on the request "weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl@16c083ce", which is more than the conf
igured time (StuckThreadMaxTime) of "600" seconds. Stack trace:
        java.net.SocketInputStream.socketRead0(Native Method)
        java.net.SocketInputStream.read(SocketInputStream.java:129)
        oracle.net.nt.MetricsEnabledInputStream.read(TcpNTAdapter.java:718)
        oracle.net.ns.Packet.receive(Packet.java:295)
        oracle.net.ns.DataPacket.receive(DataPacket.java:106)
        oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:317)
        oracle.net.ns.NetInputStream.read(NetInputStream.java:262)
        oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:107)
        oracle.jdbc.driver.T4CMAREngine.getNBytes(T4CMAREngine.java:1579)
        oracle.jdbc.driver.T4C8TTILobd.unmarshalLobData(T4C8TTILobd.java:455)
        oracle.jdbc.driver.T4C8TTILob.readLOBD(T4C8TTILob.java:796)
        oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:389)
        oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:204)
        oracle.jdbc.driver.T4C8TTIClob.read(T4C8TTIClob.java:245)
        oracle.jdbc.driver.T4CConnection.getChars(T4CConnection.java:3630)
        oracle.sql.CLOB.getChars(CLOB.java:756)
        oracle.sql.CLOB.getSubString(CLOB.java:398)
        weblogic.jdbc.wrapper.Clob_oracle_sql_CLOB.getSubString(Unknown Source)
        oracle.tip.adapter.db.sp.oracle.TypeConverter.toString(TypeConverter.java:241)
        oracle.tip.adapter.db.sp.oracle.TypeConverter.toString(TypeConverter.java:275)
        oracle.tip.adapter.db.sp.oracle.XMLBuilder.DOM(XMLBuilder.java:198)
        oracle.tip.adapter.db.sp.AbstractXMLBuilder.buildDOM(AbstractXMLBuilder.java:264)
        oracle.tip.adapter.db.sp.SPInteraction.executeStoredProcedure(SPInteraction.java:148)
        oracle.tip.adapter.db.DBInteraction.executeStoredProcedure(DBInteraction.java:1102)
        oracle.tip.adapter.db.DBInteraction.execute(DBInteraction.java:247)
        oracle.tip.adapter.sa.impl.fw.wsif.jca.WSIFOperation_JCA.performOperation(WSIFOperation_JCA.java:529)
        oracle.tip.adapter.sa.impl.fw.wsif.jca.WSIFOperation_JCA.executeOperation(WSIFOperation_JCA.java:353)
        oracle.tip.adapter.sa.impl.fw.wsif.jca.WSIFOperation_JCA.executeReque

at the same time on the Oracle DB side we have:

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
  Time: 06-JAN-2014 10:22:25
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 78
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.56.34.53)(PORT=1125))
Mon Jan 06 10:23:48 2014

I will try to fix this by setting the property oracle.jdbc.ReadTimeout to 1 800 000 (milliseconds = 30 minutes)

WebLogic: how to find JDBCDriver version for oracle.jdbc.OracleDriver

These ways don't use the ACTUAL driver being used by WebLogic.

You can simply run WLST, connect(....) to a Managed Server,

serverRuntime()
cd ('/JDBCServiceRuntime/' + msname + '/JDBCDataSourceRuntimeMBeans/' + dsname)
ls()

and you will see interesting stuff:

-r--   DatabaseProductName                          Oracle
-r--   DatabaseProductVersion                       Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-r--   DeploymentState                              2
-r--   DriverName                                   Oracle JDBC driver
-r--   DriverVersion                                11.2.0.2.0

PS: interestingly, OracleDriver contains a public static final String getDriverVersion()

SQLServerDriver timeout

http://docs.oracle.com/cd/E21764_01/web.1111/e13753/mssqlserver.htm#i1074598

LoginTimeout The amount of time, in seconds, that the driver waits for a connection to be established before timing out the connection request.
Valid Values 0| x where x is a number of seconds.
If set to 0, the driver does not time out a connection request.
If set to x, the driver waits for the specified number of seconds before returning control to the application and throwing a timeout exception.
Default: 0
Data Type: int


QueryTimeout Sets the default query timeout (in seconds) for all statements created by a connection.
Valid Values: -1 |0 | x where x is a number of seconds.
If set to x, the driver uses the value as the default timeout for any statement created by the connection. To override the default timeout value set by this connection option, call the Statement.setQueryTimeout() method to set a timeout value for a particular statement.
If set to -1, the query timeout functionality is disabled. The driver silently ignores calls to the Statement.setQueryTimeout() method.
If set to 0 (the default), the default query timeout is infinite (the query does not time out).
Default: 0
Data Type: int


After you apply these settings QueryTimeout=60 and LoginTimeout=30 to the weblogic.jdbc.sqlserver.SQLServerDriver, you get this error message on test:

Connection test failed with the following exception: weblogic.common.resourcepool.ResourceDeadException: 0:weblogic.common.ResourceException: Could not create pool connection. The DBMS driver exception was: [FMWGEN][SQLServer JDBC Driver]Login has timed out.

Bean already exists: "weblogic.j2ee.descriptor.wl.JDBCPropertyBeanImpl JDBCDriverParams/Properties/Properties[user])

I was getting this error while committing changes on a DataSource:

Bean already exists: "weblogic.j2ee.descriptor.wl.JDBCPropertyBeanImpl JDBCDriverParams/Properties
/Properties[user])

Deleting the DataSource didn't help.

I tried editing manually config.xml, and I discovered that there was a swap file for vi to recover. I deleted it:

mv .config.xml.swp config.xml.swpORI

Then I discovered that in the config/jdbc folder there was still a xml file with the preexisting DataSource, although I had deleted it. So I removed it:

rm jdbc/AcmeDataSourceUS-0327-jdbc.xml

At this point I managed to recreate the DataSource.

Wow. What a fight.

oracle.net.CONNECT_TIMEOUT

A Oracle Consultant suggested to set the property

oracle.net.CONNECT_TIMEOUT=10000
for every ConnectionPool

What is this?

http://docs.oracle.com/cd/E25178_01/core.1111/e10106/dbac.htm

__________________________________


Another useful property is:

http://docs.oracle.com/cd/B28359_01/rac.111/b28252/configwlm.htm

"Set TimeToLiveTimeout to m seconds
Set PropertyCheckInterval to n seconds

if the network call is blocked, then just setting the TimeToLive timeout alone won't work. In that case, an additional step is needed -- setting the JDBC driver connection properties as follows:

for the Thin driver: oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR
for the OCI driver: sqlnet.outbound_connection_timeout
"

oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR

There is a Oracle KB doc:
"How to Ensure that JDBC Queries are Always Timed Out [ID 559564.1]"

For more info on the TimeToLiveTimeout and PropertyCheckInterval :

http://docs.oracle.com/cd/B13789_01/java.101/b10979/conncache.htm#sthref460

_____________________________

set a timeout on Socket level for the jdbc thin driver:

oracle.net.READ_TIMEOUT (in ms)

in reality:

oracle.net.READ_TIMEOUT for jdbc versions < 10.1.0.5 oracle.jdbc.ReadTimeout for jdbc versions >=10.1.0.5

see http://docs.oracle.com/cd/E17904_01/web.1111/e13737/generic_oracle_rac.htm

"In some deployments of Oracle RAC, you may need to set parameters in addition to the out of the box configuration of a data source in an Oracle RAC configuration. The additional parameters are:

Set oracle.jdbc.ReadTimeout=300000 (300000 milliseconds) for each data source."

_____________________________


The Oracle Consultant says:
"I would set the read and connect timeouts to slightly longer than the stuck thread time setting.
This ensures you get stuck thread warnings but the thread is released soon after "


_________________


Related Oracle KB docs:

ORACLE.NET.READ_TIMEOUT Property Does Not Work On 11g JDBC Driver [ID 1341966.1]


WebLogic Server: Intermittent Stuck Threads Caused Due to the 11.1.x Oracle JDBC Thin Driver [ID 1083794.1] (this one seems to be pretty old)


How to Ensure that JDBC Queries are Always Timed Out [ID 559564.1], the suggest using

• for the Thin driver:  oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR
• for the OCI driver:  sqlnet.outbound_connection_timeout

Weblogic 11 trace and log SQL statements on a Datasource

gone are the days of P6Spy...

Gone are the days of WLSpy : "The wlspy.jar has been deprecated"

Now there is JDBCSpy:

JDBCSpy in action

Official doc:
http://docs.oracle.com/cd/E21764_01/web.1111/e13753/spy.htm

also this article is interesting


one day also JDBCSpy will die.... and se superseded by something else ... nothing lasts forever, apart human stupidity...

I tried to make SQLSpy work, no way.... maybe it works only with WebLogic drivers, and not Oracle drivers.
I try adding the ";spyAttributes=(log=(file)acmespy.log)" string to the URL, but it fails with a

java.sql.SQLRecoverableException: IO Error: NL Exception was generated
Caused by: oracle.net.ns.NetException: NL Exception was generated
at oracle.net.resolver.AddrResolution.resolveAddrTree(AddrResolution.java:614)

So I use the weblogic debug flag -Dweblogic.debug.DebugJDBCSQL or set it from the WebLogic console in the server/debug/weblogic/jdbc/sql/DebugJDBCSQL

and you get this


####<Jun 21, 2012 6:46:18 PM CEST> <Debug> <JDBCSQL> <hqchnesoa200> <osbpl1ms1> <[STUCK] ExecuteThread: '6' for queue: 'weblogic.kern
el.Default (self-tuning)'> <<WLS Kernel>> <> <11d1def534ea1be0:-115b5766:1380fddf0f3:-8000-0000000000000234> <1340297178066> <BEA-000
000> <[[weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection-SOAXADataSource-1
830, oracle.jdbc.driver.LogicalConnection@5249d560]] prepareStatement(DELETE FROM FILEADAPTER_IN WHERE FILE_PROCESSED='2' AND FILE_R
EADONLY='N' AND ROOT_DIRECTORY=?)>

WebLogic, how to run SQL commands without SQLPlus

few people know this :o) :

cd $DOMAIN_HOME
. ./bin/setDomainEnv.sh

java utils.Schema
Usage: java utils.Schema <url> <driver> [options] <SQL file>

where:
<url> JDBC driver URL.
<driver> JDBC driver class pathname.
<SQL file> Text file with SQL statements.

where options include:
-u <user> User name to be passed to database.
-p <password> User password to be passed to database.
-verbose Print SQL statements and database messages.


utils.Schema is a class in weblogic.jar