• 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
  • Bear Bibeault
  • Junilu Lacar
  • Martin Vashko
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Scott Selikoff
  • salvin francis
  • Piet Souris

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: 21306
140
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).
 
That's my roommate. He's kinda weird, but he always pays his half of the rent. And he gave me this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!