obtain info from nested table in function?

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