Inserting an array within an object construction, constructed based on a query

Question

How can I construct an object using a query if I also need to insert values in the parent object?

Clearly I'm using the wrong syntax.

EDITED, getting closer:

insert into myTable
select
    mybigtype('foo', 'bar', 'fizzle', myarrayoflittletypes(
        select ref(S)
        from anotherTable S
        where S.stname='dingle'
            or S.stname='fangle')));

mylittletype was created as:

CREATE TYPE myarrayoflittletypes AS VARRAY(20) OF REF mylittletype

anotherTable was created as:

CREATE TABLE anotherTable OF mylittletype

mybigtype has a few strings and a type that's defined to hold an array of references to mylittletype objects. So I want to be able to insert a row into this table that creates an object of type mybigtype with the array constructed based on a query that I define. Can I do this all in one insertion? Or do I need to put in a null placeholder and UPDATE this field subsequently?

Oracle's current complaint is

ORA-01427: single-row subquery returns more than one row

So how can I get the multiple OID returns to this query into the array in the object I'm constructing?

Answer 1

You almost have it!

insert into myTable
select 
    mybigtype('foo', 'bar', 'fizzle', myarrayoflittletypes(
        (select
            ref(S.mylittletype)
        from anotherTable S
        where S.mylittletype.stname='dingle
            or S.mylittletype.stname='fangle')));

Answer 2

There may be a better way to do this so that I can insert multiple rows into my array from a single query, but the following worked:

insert into myTable
    values(mybigtype('foo', 'bar', 'fizzle', myarrayoflittletypes(
        (select ref(S)
        from anotherTable S
        where S.stname='dingle'),
        (select ref(S)
         from anotherTable S
         where S.stname='fangle'))));