Win a copy of Svelte and Sapper in Action this week in the JavaScript 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 ...
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Problem getting user defined type in Oracle from JDBC

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. 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 it runs fine.
Saloon Keeper
Posts: 22485
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).
If I'd had more time, I would have written a shorter letter. -T.S. Eliot such a short, tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
    Bookmark Topic Watch Topic
  • New Topic