Re: Re: Object Types in PL/SQL

• Original Message ----- From: <rgaffuri_at_cox.net>

> 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... ;) Received on Thu Jul 17 2003 - 06:00:26 CDT