Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1068937761.982029_at_yasure>...
> Oeln wrote:
>
> > Oracle9i 9.2.0.1.0/Windows 2000
> >
> > I'd like to obtain an ID from a nested table object in order to insert
> > it into an ID field (outside the nested table I'm getting it from).
> > I've got a function that lets me get a foreign key ID from one other
> > table ('objtarget'); but it isn't nested:
> >
> > CREATE FUNCTION oeln.get_obj_id(
> > obj_type IN oeln.objtarget.objecttarget%type
> > )
> > RETURN oeln.objtarget.obj_id%type
> > IS id_type oeln.objtarget.obj_id%type;
> > BEGIN
> > SELECT obj_id INTO id_type
> > FROM oeln.objtarget
> > WHERE oeln.objtarget.objecttarget = obj_type;
> > RETURN(id_type);
> > END;
> > /
> > SELECT obj_id
> > FROM oeln.objtarget
> > WHERE obj_id = get_obj_id('<objecttarget>');
> >
> > I'm getting compilation errors on functions to obtain ID's from nested
> > tables instead. I've got the table 'opt' (includes the fields 'opt_id'
> > & 'optics'). The 'optics' field in opt is of type o_tab(o_ty),
> > including the field 'optinfo'. I'd like to get the opt_id out of the
> > opt table, from input I obtain for the optinfo field: i.e.,
> >
> > SELECT opt_id
> > FROM oeln.opt
> > WHERE opt_id = get_opt_id(<optinfo>);
> >
> > In other words, I'd like to get opt_id from the opt table like I get
> > obj_id from the objtarget table; but instead of looking it up by
> > objecttarget I've got to look it up by optinfo, a field in a nested
> > table object in the opt table itself. I've only gotten compilation
> > errors on functions in order to do this. Is this an option?
> >
> > - Noel
>
> You should be using REF and DEREF if you are trying to create
> referential integrity with a nested table. Go to
> http://tahiti.oracle.com and look up REF and DEREF.
>
> You can find an example at:
> http://www.psoug.org/reference/nest_tab_cons.html
> Scroll down to REFERENTIAL CONSTRAINT.
Okay, thanks for the input. I guess that ought to improve the
integrity of the nested table object(s) I've got, too..
Received on Wed Nov 19 2003 - 05:55:27 CST
