| Urgent !!!! call pl/sql function which returns an Object Type having nested tables from Java [message #38536] |
Thu, 25 April 2002 07:47  |
Yogita
Messages: 8
Registered: April 2002
|
Junior Member |
|
|
Hi,
I have a function contactData which return contact object Type.
type contact as object(contactid NUMBER, contacttypeid NUMBER, companyData company, addresslist address_list,linklist link_list );
type company is object( companyid Number, companyName Varchar2(50) );
type address is object( addressid NUMBER, contactid NUMBER,
typeid NUMBER, street1 VARCHAR2(50), street2 VARCHAR2(50),
city VARCHAR2(30), state VARCHAR2(30), zipcode VARCHAR2(20), country VARCHAR2(50));
type address_list is table of address;
type link is object ( linkid NUMBER, contactid NUMBER, linktypeid NUMBER, typeid NUMBER, link VARCHAR2(50));
type link_list is table of link;
Can anyone help me on how to call the pl/sql function from java ? I have a problem in retrieving the addresslist from java.
-Yogita
|
|
|
|
| Re: Urgent !!!! call pl/sql function which returns an Object Type having nested tables from Java [message #38537 is a reply to message #38536] |
Thu, 25 April 2002 09:05  |
ranjan
Messages: 20
Registered: August 1999
|
Junior Member |
|
|
Try the following code...
> > import java.sql.*;
> > import oracle.jdbc.driver.*;
> > import oracle.sql.*;
> >
> > // create or replace type SimpleArray
> > // as table of varchar2(30)
> > // /
> > //
> > // create or replace function getSimpleArray
> > // return SimpleArray
> > // as
> > // l_data simpleArray := simpleArray();
> > // begin
> > // for i in 1 .. 10 loop
> > // l_data.extend;
> > // l_data(l_data.count) := 'entry ' || i;
> > // end loop;
> > // return l_data;
> > // end;
> > // /
> >
> > class Array
> > {
> >
> > public static void main(String args[[]]) throws
> > Exception
> > {
> >
> > DriverManager.registerDriver
> > (new
> > oracle.jdbc.driver.OracleDriver());
> >
> > Connection conn =
> > DriverManager.getConnection
> >
> > ("jdbc:oracle:thin:@aria:1521:ora8i",
> > "scott",
> > "tiger");
> >
> > OracleCallableStatement stmt =
> > (OracleCallableStatement)conn.prepareCall
> > ( "begin ? := getSimpleArray;
> > end;" );
> >
> > stmt.registerOutParameter( 1,
> > OracleTypes.ARRAY,
> > "SIMPLEARRAY" );
> > stmt.executeUpdate();
> >
> > ARRAY simpleArray = stmt.getARRAY(1);
> >
> > System.out.println ("Array is of type " +
> >
> > simpleArray.getSQLTypeName());
> >
> > System.out.println ("Array element is of type
> > code
> > " +
> >
> simpleArray.getBaseType());
> >
> > System.out.println ("Array is of length " +
> > simpleArray.length());
> >
> >
> > String[[]] values =
> > (String[[]])simpleArray.getArray();
> >
> > for( int i = 0; i < values.length; i++ )
> > System.out.println( "row " + i + " = '" +
> > values[[i]] + "'" );
> >
> > stmt.close();
> > conn.close();
> > }
> > }
>
|
|
|
|
| Re: Urgent !!!! call pl/sql function which returns an Object Type having nested tables from Java [message #38542 is a reply to message #38536] |
Thu, 25 April 2002 11:30 |
Yogita
Messages: 8
Registered: April 2002
|
Junior Member |
|
|
Hi Ranjan !!!
Thanks, I have tried similar example And it works fine. Currently, I am using Multi-Level Collection Types i.e a nested table of nested tables.
E.g from Oracle9i Application Developer's Guide - Object-Relational Features
Release 1 (9.0.1)
under
2. Basic Components of Oracle Objects
CREATE TYPE satellite_t AS OBJECT (
name VARCHAR2(20),
diameter NUMBER);
CREATE TYPE nt_sat_t AS TABLE OF satellite_t;
CREATE TYPE planet_t AS OBJECT (
name VARCHAR2(20),
mass NUMBER,
satellites nt_sat_t);
I am using a similar nested table of nested tables.
Can you help me to retrieve the object and it attributes from java ?
Thanks
Yogita
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
