Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • 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
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

Problem getting user defined type in Oracle from JDBC

 
Greenhorn
Posts: 1
Oracle Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
I've a user defined type in oracle like this:



and I need to do a simple query:



from Java where ROLES attribute is defined as:



I need to extract the ROLES attribute from Java using WebLogic but I get this error:



The code I run from JDeveloper(v. 12.2.1.4.0) is below:



The strange thing is that when I use this code as stand alone application in Eclipse:



It runs correctly.

Any idea on how I can get it working on weblogic?

Another hint: when I run the code on JDeveloper 12.1.2.0.0 it runs fine.
 
Saloon Keeper
Posts: 22097
151
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, I don't recommend defining custom database data types unless you absolutely have to. En particular,, it looks like you would actually be better off with multiple rows in your UI_MSG_ALERT_T table than by cramming multiple role columns into it.  Among other things, the necessary SQL is likely to be easier and you have more overall flexibility within Oracle and more portability in case your employer decides to do like Amazon did and get rid of Oracle in favor of some other DBMS where things work differently.

Beyond that, to get Oracle-specific features, you would generally have to cast the Connection object to an Oracle Connection. And that's a problem with a pooled JDBC Connection, since the Pool Connection is actually a fa├žade for the real Connection. It has to be, since close() on a Pool Connection simply returns it to the pool, but close() on a "real" Connection destroys the network link to the database server. So in a webapp, you'd first have to get the Pool Connection via the DataSource, then de-reference it to get to the underlying Oracle Connection.

It's all somewhat messy and not to be done lightly. Better, as I said, to keep multiple records in UI_MSG_ALERT_T or have the roles column serve as a key to a second table with the actual values in it (Parent-child in a one-to-many relation).
 
When evil is afoot and you don't have any arms you gotta be hip and do the legwork, but always kick some ... tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic