AW: Re: Object Types in PL/SQL

Hi

Sorry for the late reply ... weekend ;). If I understand you correctly, you do the following:

Use "normal" tables (a normalized data model ?), then create an additional Object Type for each table with methods to do DML on that table. From Java, only use stored procs/funcs to manipulate the data. The procs/funcs use the Object Types in return. Java only "knows" these Object Types, not the tables.

Advantage: JDBC doesn't have to query for that much metadata, since Java Objects map quite well to Object Types.

Is that correct so far ?

Stefan

-----Ursprüngliche Nachricht-----
Von: Nuno Souto [mailto:nsouto_at_optusnet.com.au] Gesendet: Donnerstag, 17. Juli 2003 14:05 An: Multiple recipients of list ORACLE-L Betreff: Re: Re: Object Types in PL/SQL

• Original Message -----

> how does the object features improve performance of the jdbc? its fairly
common now to have a java or .net(which we are using) and an Oracle backend.
>

When we started to use JDBC, one of the things I noticed was some very unusual network traffic. Certainly more than I was expecting given the level of statements being executed. A few traces showed a lot of traffic by JDBC driver itself trying to find information about the parameters involved in a ExecutableStatement. I believe this is specific to JDBC 2 and its new MetaData functionality, but I don't know for sure.

A little bit of experimentation showed that if we created an object type definition for each table involved in our procedures and used the object type to pass the parameters (each parameter is a column) to the stored procedures, the network traffic dropped dramatically.

Now we use a package for each table with an object type matching the table columns used as the single parameter to each function/procedure. Each package has as a minimum a MNG_<table> and a DEL_<table> procedure, with the first one handling both INSERT and UPDATE (depending if PK is set to NULL or not). We also write "getters" to access the data, returning a REF CURSOR typed on the same object type.

This is not only blinding fast but the network traffic is negligeable.

> your comments would be much appreciated.

Not really comments, just the experience so far. It might change in the next version. So far it seems to work like a charm, so we're sticking with it. The next step is to get a few of the hierarchical accesses done via O-R views returning a single object with an array for the child object types. First trials seem very promising, but I have to wait for the 9i upgrade: 8i lets me do only one level of hierarchical object.

After that, it's XML all the way through! If the customer doesn't kick us all out b4... ;)

Cheers
Nuno Souto
nsouto_at_optusnet.com.au

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nuno Souto
  INET: nsouto_at_optusnet.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may