Re: obtain info from nested table in function?

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.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)