Re: Custom Object Types

• Previous message: Jose R via JavaKB.com: "Problem with DB2java JDBC driver"
• In reply to: Dom: "Re: Custom Object Types"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Tue, 15 Feb 2005 17:19:00 -0600

On Tue, 15 Feb 2005 17:51:51 +0000, Dom wrote:

>
> "Chuck Simpson" <chuckls@cox-internet.com> ha scritto nel messaggio
> news:pan.2005.02.15.16.04.51.559741@cox-internet.com...
>> On Tue, 15 Feb 2005 10:11:28 +0000, Dom wrote:
>>
>> > Hi to all,
>> > I have a PLSQL package containing 2 methods: get_account and
> set_account.
>> > The former returns an Account type, the latter accepts an Account
>> > type. Account TYPE is defined as %ROWTYPE of an Account Table
>> > contained inside DB.
>> >
>> > This is the PLSQL package:
>> > CREATE OR REPLACE PACKAGE test AS
>> > SUBTYPE Account IS MEMBERSHIP.ACCOUNT%ROWTYPE;
>> >
>> > PROCEDURE get_account (
>> > p_username VARCHAR2,
>> > p_password VARCHAR2,
>> > p_account OUT Account,
>> > return_code OUT NUMBER);
>> >
>> > PROCEDURE set_account (
>> > p_account IN Account,
>> > return_code OUT NUMBER);
>> > END;
>> > /
>> >
>> > Now I want to call these 2 procedures from a java program via a
>> > callable statement....
>> > what is the best approach to deal with Account type? I have a Java
>> > Bean Account.java describing Account type.... I have read that there
>> > are 2 approaches: one based on oracle.sql.STRUCT and the other based
>> > on java.sql.SQLData.... If my Account.java also implements
> java.sql.SQLData,
>> > does not match JavaBean pattern anymore?
>> >
>> > Thanks for any advice, link and code examples
>> >
>> > Dom
>>
>> Dom,
>>
>> As you said there are two approaches but, a better description of the
>> approaches is WEAK vs STRONG object orientation. Using the WEAK object
>> model involves the use of the java.sql.Struct (or oracle.sql.STRUCT)
>> data type and the STRONG object model involves the use of the Oracle
>> user defined object types, the Connection.typemap and the
>> java.sql.SQLData interface. Oracle has excellent documentation in its
>> reference guides on all these subjects but, I recommend you get a copy
>> of the O'Reilly book, "Java Programming with Oracle JDBC" by Donald
>> Bales. Part IV of the book covers Oracle Object-Relational programming
>> very well.
>>
>> If you only need to handle these two procedures then use the WEAK model
>> and read the OUT parameter as a java.sql.Struct type and map the type
>> attributes to the Java fields in your code. In either case (WEAK or
>> STRONG) you will need to define an Oracle object type to be returned
>> instead of a ROWTYPE. ROWTYPE can only be used in PL/SQL.
>>
>> If you need a more general solution for the long term then you have to
>> decide where you want to handle the OR mapping. You can use Oracle's
>> STRONG or WEAK object model and create your own application layer that
>> maps the Oracle data to Java classes or you can use an OR-mapping
>> framework such as TopLink, Hibernate or iBatis. (Hibernate and iBatis
>> are excellent OpenSource frameworks. TopLink is a commercial framework
>> from Oracle.) Constructing your own mapping layer is a lot of work even
>> if you use Oracle's Object-Relational technologies. (I know this from
>> experience.) I recommend you consider using one of the OR-mapping
>> frameworks before you attempt to create your own mapping layer.
>>
>> Chuck
>>
>>
> Ok Chuck,
> I have understood that ACCOUNT%ROWTYPE can only be used in PL/SQL.... so I
> have created a UsedDefinedType "Account" and I have modified my Package
> ... this is the code:
>
> CREATE OR REPLACE PACKAGE crm_test AS
>
> PROCEDURE get_account (
> p_usr VARCHAR2,
> p_dom VARCHAR2,
> p_account OUT TIPO_ACCOUNT,
> retcode OUT NUMBER);
>
> PROCEDURE set_account (
> p_account IN TIPO_ACCOUNT,
> retcode OUT NUMBER);
> END;
> /
>
>
> CREATE OR REPLACE PACKAGE BODY crm_test AS
>
> PROCEDURE get_account (p_usr IN VARCHAR2,
> p_dom IN VARCHAR2,
> p_account OUT TIPO_ACCOUNT,
> retcode OUT NUMBER)
> AS
> CURSOR c1 IS SELECT * from ACCOUNT where username=p_usr and
> dominio=p_dom;
> BEGIN
> retcode:=1;
> OPEN c1;
> FETCH c1 INTO p_account;
> CLOSE c1;
> END get_account;
>
> PROCEDURE set_account (p_account TIPO_ACCOUNT, retcode OUT NUMBER) IS
> BEGIN
> retcode:=1;
> INSERT into ACCOUNT values p_account;
>
> END set_account;
> END crm_test;
> /
>
> Now the problem is when I try to FETCH Cursor c1 (ACCOUNT%ROWTYPE) into
> p_account (ACCOUNT type as defined by me) the cast is not automatic...
> I wonder if my approach is correct ...
>
> thanks
> Dom

No, the cast is not automatic. At least it wasn't in 8i. You will need to
fetch into a ROWTYPE variable as you did before and assign each column
attribute to the object. You have to construct the object first. Something
like the following should work but, consult the Oracle documentation on
OBJECTS and COLLECTIONS for a better treatment.

PROCEDURE get_account (
  p_usr IN VARCHAR2,
  p_dom IN VARCHAR2,
  p_account OUT TIPO_ACCOUNT,
  retcode OUT NUMBER
) AS
  r_account account%ROWTYPE;
  o_account TIPO_ACCOUNT := TIPO_ACCOUNT();
  CURSOR c1 IS SELECT * from ACCOUNT where username=p_usr and
dominio=p_dom;
BEGIN
  retcode:=1;
  OPEN c1;
  FETCH c1 INTO r_account;
  CLOSE c1;

--
-- substitute your column names instead columnX
--
  o_account.column1 := r_account.column1;
  o_account.column2 := r_account.column2;
  ...
  o_account.columnN := r_account.columnN;
  p_account := o_account;
END get_account;
I really must caution you that you really need to understand Oracle
objects and collections and things like object-relational views before you
attempt something like this. There are different ways to accomplish what
you are attempting, e.g. REF CURSOR. I advise you to find someone who
really knows this stuff and learn from them. (Not me, I am rusty as I no
longer use Oracle.) If you don't have access to someone with the knowledge
you need you can enroll in a training class or read all the books and
reference manuals you can and play around with examples. Another source of
information is the O'Reilly book "Oracle PL/SQL Programming" Third Edition
by Steve Feuersteing and Bill Pribyl. It has a lot of info on collections
and objects.
Chuck

• Previous message: Jose R via JavaKB.com: "Problem with DB2java JDBC driver"
• In reply to: Dom: "Re: Custom Object Types"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]