Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Howto Map Complex Objects from PL/SQL routines without JDeveloper?

 
Pete Neu
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I have complex objects which are returned from an oracle db through a pl/sql routine. Normally I would create the necessary wrapper objects with jdeveloper. But the wrapper objects created by jdeveloper have one problem - once they are loaded into java I can only read them - Modifying them on any level below the first one results in memory loss.

Is there another way to do this? I read Hibernate documentation but I couldn't find any hint on how to do this.

My workaround for now is to simply create new instances of the wrapper objects and refill them with the changed data. This a very error prone approach.

And ideas?

Cheers,
Pete
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes it is a very error prone way to do things. Basically you would have to create an adapter class to transform the read object into your object, and back the other way.

Basically, you are calling a Stored Procedure, and due to Oracle not correctly implementing the JDBC spec for their driver, causes the need for some rules to be able to call a stored procedure. \

They are

1. Only one parameter can be an Out parameter
2. It must be the first parameter.
3. The out parameter must be a ref cursor. Not Oracle datatypes.


Now, if you go to hibernate.org, the wiki pages has some other things you can create to do more.

Good Luck

Mark
 
Pete Neu
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, so I need the rules you outlined. But how do I proceed further? After all I have to feed the PL/SQL routine with a oracle specific wrapper object or can I do it elsewhise? E.g. call the PL/SQL routine with my own objects?

I always thought due to the array like nature of PL/SQL this shouldn't be done by the progammer.

Just to give you an impression what the graph looks like that the PL/SQL procedure returns have a look a this picture:
http://img516.imageshack.us/my.php?image=hierachytl9.jpg

Is this doable?

I also had a look at the hibernate wiki which does not say much - http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#sp_query

It also states the rules you outlined but I does not provide an example how top map a four level deep object graph...

I get the impression that you can't work with complex object hierachies with Stored Procedures and Java. Is this right? Are flat hierachies the
only option?
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The big key being, the Out parameter can only be a Reference Cursor, so it can't be an Oracle Type.

You create a named query pointing to the pl/sql, the out is a Reference Cursor, so you can map it to an object. The Object model should be able to be a complex object.

Also, go to the documentation pages for more information on Stored Procedures, which includes an example.

Good Luck

Mark
 
Pete Neu
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you provide an example on how to do this? I still don't know exactly how this works.

A link to the right documentation will do fine.

cheers,
Pete
[ June 01, 2007: Message edited by: Pete Neu ]
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
 
Pete Neu
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hm, that's the same link I put in my second post. It does not show how to query a stored procedures with a complex object graph.

The stored procedure I have to use is not that simple. I can post some of the pl/sql scripts on tuesday because I won't have access to it earlier. Hope that clears things up.

Again on the subject of the out parameter. The object graph consists of custom oracle types. Because only this way the object graph can be build in oracle.

Did you mean earlier that this won't work?

cheers,
Pete
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Exactly, that breaks those rules I posted.

"The object graph consists of custom oracle types."

a custom Oracle type is not a Ref Cursor, so it breaks the rule.

Now, there is a way to map to some custom Oracle types, but it requires using a third party JDBC driver for Oracle, well there is another solution that is out there if you must use custom Oracle types, but it still requires adapter classes. That is JPublisher from Oracle. Personally, I would run as far away as possible from it, but sometimes you don't have a choice.

Mark
 
Pete Neu
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, so if have something like this:

create or replace TYPE Anlage_obj AS OBJECT (Code_AL Float,
Gruppe VARCHAR2(200),
Typ VARCHAR2(200),
PREIS FLOAT,
Kapazitaet FLOAT,
Einheit VARCHAR2(15),
Bezug VARCHAR2(15),
AFA FLOAT,
Zinsen FLOAT,
Wartungskosten FLOAT,
Berechnen Float,
Teilanlagen Teilanlagen_Obj);

I can only use JPublisher(JDeveloper) which has the problem above which I outlined in my first post.

That means I'm out of luck here! Hibernate won't work, JPublisher doesn't work -> I will have to do it the ugly way.

I'm disappointed that this is not possible. I mean creating structured oracle data types is clearly a clean and simple approach to delivering complex structured data from an Oracle db. That's clearly another point on my endless list "why big corporate products suck".
[ June 05, 2007: Message edited by: Pete Neu ]
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The main problem is that the Oracle JDBC driver does not accurately implement the JDBC Spec.

Mark
 
Pete Neu
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So, do you have any suggestion what to do?

How would you proceed when faced with this problem?

-Pete
[ June 06, 2007: Message edited by: Pete Neu ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic