Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Devaka Cooray
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

java.sql.SQLException: Not a wrapper of oracle.jdbc.driver.OracleConnection  RSS feed

 
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


I want to send array of integer and retrieve array of sturct  from Oracle Procedure.
but I get Error:   "java.sql.SQLException: Not a wrapper of oracle.jdbc.driver.OracleConnection"
Environment is:
oracle Export: Release 11.2.0.1.0
Apache Tomcat/8.0.37
Struts 1.1
Java 1.8
JDBC driver version is 10.1.0.2.0
SERVER.XML
<Resource name="jdbc/db" auth="Container" type="oracle.jdbc.pool.OracleDataSource"  accessToUnderlyingConnectionAllowed="true"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
username="USER" password="password" maxTotal="20" maxIdle="10"  maxWaitMillis="-1"
  driverClassName="oracle.jdbc.OracleDriver"  url="URL" />
My Code is
public SalryHdrDTO getFacWrkingDays(SalryHdrDTO objSalryHdr ) throws HrisException{
this.logger.info(SalryDAO.class.getName() + " --->> getFacWrkingDays() Starts");
Connection conn = null;
try {
conn = this.connectionPoolerObj.getConnection(); //from JNDI
if (conn != null){

if (objSalryHdr !=  null){
int[] multiFacId = objSalryHdr.getMultiFacId();
final String typeName = "HRIS.FAC_OBJ"; //created in database
final String typeTableName = "HRIS.ARR_FAC_OBJ"; //created in database
OracleConnection oConn=null;
//if (conn.isWrapperFor(OracleConnection.class)) {
//ERROR :  java.sql.SQLException: Not a wrapper of oracle.jdbc.driver.OracleConnection
// if I use "oracle.jdbc.OracleConnection.class" it gives compilation error
oConn = conn.unwrap(OracleConnection.class);
//}
ArrayDescriptor des = ArrayDescriptor.createDescriptor("HRIS.ARR_FAC_ID", oConn);
ARRAY arr_fac = new ARRAY(des,conn,multiFacId);

final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), oConn);
final ResultSetMetaData metaData = structDescriptor.getMetaData();

CallableStatement st = conn.prepareCall("call HRIS.EMP_SAL_CALC.get_fac_wrking_days(?,?,?)");
// Passing an array to the procedure -
st.setArray(1, arr_fac);
st.registerOutParameter(2, Types.INTEGER);
st.registerOutParameter(3,Types.ARRAY,typeTableName);
st.execute();

System.out.println("size : "+st.getInt(2));
Object[] data = (Object[]) ((Array) st.getObject(3)).getArray();
for(Object tmp : data) {
Struct row = (Struct) tmp;
// Attributes are index 1 based...
int idx = 1;
for(Object attribute : row.getAttributes()) {
System.out.println(metaData.getColumnName(idx) + " = " + attribute);
++idx;
}
System.out.println("---");
}
st.close();
}
}
}
catch (Exception ex) {
this.logger.debug(SalryDAO.class.getName() + " getFacWrkingDays() Exception Raised --->> " + ex.getMessage());
throw new HrisException(ex.getMessage());
} finally {
this.connectionPoolerObj.returnConnection(conn);
}
this.logger.info(SalryDAO.class.getName() + " --->> getFacWrkingDays() Starts");
return objSalryHdr;
}
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This code works fine if I run it directly from public static void main() method using

Class.forName("oracle.jdbc.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:url ","DB","DB");
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using ConnectionPooler.
 
Swastik Dey
Rancher
Posts: 1926
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You probably need to typecast it to OracleConnection type



should be

[/code]
 
Bartender
Posts: 20310
110
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Versha,

Our message editor has a "Code" button. You can use it to generate special code tags that wrap sample code, xml and other formatted text and makes them more readable.


Using a vendor-specific JDBC driver class is a very, very bad idea unless you absolutely have to. In general you should always use just the Connection class itself. Excessive use of vendor-specific database features locks your code into that vendor, which not only can be a problem in a world where no less a company than the mighty Amazon.com has dedicated itself to getting out of Oracle's clutches entirely, but it also shrinks the number of people who can help you, since lots of people use SQL, but not so many use Oracle. It's unclear to me that you need direct access to the Oracle driver to call a stored procedure. In most cases you wouldn't. Of course in most cases, I don't recommend using stored procedures at all, since they, too, not only promote vendor lock-in, they also tend to make system maintenance much more expensive.

When you obtain a Connection from a Connection Pool, you're not getting the actual Connection for that pool's JDBC driver, you're getting a façade object that has overriden certain Connection methods that facilitate managing the pool. So there's another reason why trying to work with the Connection in a vendor-specific way.
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you all for quick reply. But I have already tried above solutions. Nothing worked
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I get same error message
 
Master Rancher
Posts: 3887
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you have to unwrap this to get at the OracleConnection object then I suggest running through a debugger and seeing what that connection object is, and what it contains.

However, that will only apply to the specific connection pooling you are using, and (as Time says) to the specific database you are using.

Are you sure you need the OracleConnection?
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When I use Connection conn from ConnectionPooler I get erroe nessage as below


and when I try to convert it to OracleConnection

it gives 'not a wrapper class'

I have tried with  ojdbc7.jar  & ojdbc8.jar in C:\Program Files\Apache Software Foundation\Tomcat 8.0\lib folder
 
Swastik Dey
Rancher
Posts: 1926
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you show us the code of getConnection method of ConnectionPooler?
 
Dave Tolls
Master Rancher
Posts: 3887
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You do need to physically debug this, at least that would be the easiest way to see what you are dealing with.

What is the ConnectionPooler class, by the way?
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is code for  ConnectionPooler.java
 
Dave Tolls
Master Rancher
Posts: 3887
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, so it looks like something sitting between your code and the actual connection pool.

Though there's some issues with that code (your getInstance is not threadsafe, for example), it's not relevant to the current problem.

That leaves two things.  First, do you have to get the OracleConnection, and second have you debugged the code to find out what class is being returned, and what it is returning from the call to unwrap?
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


At this line it gives exception 'java.lang.ClassCastException: com.sun.proxy.$Proxy9 cannot be cast to oracle.jdbc.OracleConnection'  that means OracleConnection is needed

and when I inspect

it returns
oracle.jdbc.driver.T4CConnection@12299890

and
prototype and explanation of unwrap is :

unwrap(Class <T> iface): T - Wrapper

Returns an object that implements the given interface to allow access to non-standard methods, or standard methods not exposed by the proxy.
If the receiver implements the interface then the result is the receiver or a proxy fo
eceiver. If the receiver is a wrapper and the wrapped object implements the interface then the result is the wrapped object or a proxy for the wrapped object.
Otherwise return the the result of calling unwrap recursively on the wrapped object or a proxy for that result.
If the receiver is not a wrapper and does not implement the interface, then an SQLException is thrown.
Parameters:<T> the type of the class modeled by this Class objectiface A Class defining an interface that the result must implement
.Returns:an object that implements the interface. May be a proxy for the actual implementing object.Throws:java.sql.SQLException - If no object found that implements the interfaceSince:1.6

 
Swastik Dey
Rancher
Posts: 1926
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What App or web server are you using?
 
Swastik Dey
Rancher
Posts: 1926
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You might get some input from here.

https://docs.oracle.com/cd/B10500_01/java.920/a96654/connpoca.htm

 
Dave Tolls
Master Rancher
Posts: 3887
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Versha Agarwal wrote:



I thought there was a cure for having to use Oracle classes these days, but I can't seem to find it.

Versha Agarwal wrote:
At this line it gives exception 'java.lang.ClassCastException: com.sun.proxy.$Proxy9 cannot be cast to oracle.jdbc.OracleConnection'  that means OracleConnection is needed

and when I inspect

it returns
oracle.jdbc.driver.T4CConnection@12299890


T4CConnection is-a OracleConnection.
It implements that interface (actually I think there might be an inbetween class).
If you are getting this error with that class then I'm wondering if there's a class-loader issue.

Just to clarify.
conn is the Proxy class.
conn.unwrap is returning the T4CConnection.
 
Tim Holloway
Bartender
Posts: 20310
110
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The ConnectionPooler class is awful. You don't need to cache Connections in your web application. In fact, it's a very, very bad practice.

First, there's a perfectly good connection pooler already at work. It's what your JNDI lookup returns and if you cache that pooler, your "get connection" class can be collapsed down to a simple locate for the pooler and a call to the pooler's getConnection() method.

Secondly holding Connections in an app-local pool not only potentially starves other applications (the server's pool can be shared between multiple webapps), but also it attempts to hold resources without regard to dynamics that the real connection pooler allows for. Plus, a Connection is not an object, it's an interface, so it cannot be serialized if the webapp server needs to park it.

Thirdly, as I said earlier, the Connections returned from the underlying Connection Pool are not the driver connections, they're facade connections - a special implementation of the Connection that facilitates management by the pool.

I also have a gripe about a special method to get a Connection and start a transaction at the same time. While Connections should be obtained as late as possible and returned (to the Connection Pool!) as soon as possible, transactions should have an ever shorter duration.

All in all this is an extremely baroque and complex solution to what is normally a much simpler problem and I've yet to hear an explanation for why it is necessary.  Again, repeating myself, accessing the vendor-specific JDBC driver directly when using a connection pool is almost never a good practice. JDBC was designed to avoid the kind of messes that occur (for example, in PHP) when a particular brand and version of a DBMS is hard-coded into the application.
 
Dave Tolls
Master Rancher
Posts: 3887
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This comes down to your earlier thing regarding using Stored Procedures.

Once you have an Oracle SP which has a parameter that uses a user defined type (in this case it's either a table or VARRAY) then using the Oracle classes to handle that was the only thing you could do.
Had to do it myself more than once back in the day.

I would have hoped (and I thought it had happened) that this cludge would have been sorted by now, but I expect Oracle don't really care all that much.
 
Tim Holloway
Bartender
Posts: 20310
110
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you, Dave! I did consider that the Stored Procedure was at the rotten root of this, but it has been too long since I've needed to deal with Oracle. Yet one more reason why Stored Procedures are a huge pain.

It's hardly a unique problem to need special driver functions. I think to get the PostGIS functions of a PostgreSQL DBMS you also need the raw driver.

However, I still recommend minimizing the exposure. If you walk your way up/across the driver chain to get the vendor driver, a safer way to handle it would be to provide a utility method to convert a generic Connection to the Driver-specific Connection. Probably in the application class that gets Connections from the Connection Pool. And use that driver-specific connection only on operations that need it. That way you can minimize the corrupting influence of vendor-specific code on general database operations and also be less likely to invoke the wrong kind of close functionality when you're done with the Connection.

Like so:


"spConn" here is used as an alias for "conn"s Oracle-specific functions, but because it's not an actual Pool Connection object, you would never close it, since conn.close() is what's required to actually return it to the pool.

And note also that "spConn" should have a very limited scope - maybe block or method, since when you do close "conn", using "spConn" after that point would likely corrupt the Connection pool and potentially crash the entire server.
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I understand your view. But it is difficult to change ConnectionPooler class at the moment.
and the solution given again require  to convert

'OracleConnection spConn' from 'Connection conn'
(
)

So the problem remains same.
 
Dave Tolls
Master Rancher
Posts: 3887
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Which leaves us with the issue that unwrap is returning a T4CConnection and for some reason casting to an OracleConnection is causing a class cast exception, even though a T4CConnection is-a OracleConnection.

As I said above, the first thing that comes to mind is a class loader issue.
And for that I'm not the best person to ask about those, I'm afraid...
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Still I am trying ....
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Finally Removing 'classes12.jar'  from classpathh solved the issue.
But now ArrayDescriptor.createDescriptor () is deprecated. I have to struggle with that.
Thank You all for your support.
 
Dave Tolls
Master Rancher
Posts: 3887
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So you had multiple jdbc drivers in the classpath?

As for the ArrayDescriptor, there's a method on the OracleConnection called something like createArray to use instead.
 
Tim Holloway
Bartender
Posts: 20310
110
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Something I was supposed to mention earlier and I forgot: JDBC driver jars sometimes contain more than one Driver class and, of course, vendors often have multiple JDBC driver jars. So it's important to make sure you know what you're dealing with.
 
Versha Agarwal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
trying to use OracleConnection.createOracleArray()
 
All of the world's problems can be solved in a garden - Geoff Lawton. Tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!