Re: Getting reference from a newly inserted object

jerry wrote:
> HI
> I am troubling in getting reference of an user defined type after
> inserted it into a table.
> For example, I use a java program to insert an user defined object:
>
> .
> .
> .
> OraclePreparedStatement pstmt =
> (OraclePreparedStatement)conn.prepareStatement ("insert into My_Table
> values (?)");
> pstmt.setObject(1,this);
> pstmt.execute();
> .
> .
> .
>
> I can't use another select statement (ex. select ref(myref) from
> My_Table where My_Table.ID=?) to retrieve the record just inserted due
> to there is no constraint on all the columns of My_Table.
> Please help to get the reference of a newly inserted object, thanks a
> lot.

I'm with Jim questioning the design ... and if the design originated with someone whose skill is Java rather than SQL you should put it out of its misery. I'd suggest also reading Tom Kyte's comments about the use of object tables before touching the design (http://asktom.oracle.com).

But it is possible the problem is that you don't know how to retrieve a record in an object table. Please provide the DDL that creates the table the actual DML statement that performs the insert, and the SELECT statement you are using in an attempt to retrieve the record.

Constraints have nothing to do with the ability to retrieve a record. Their only purpose is in guaranteeing the integrity of the data.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)