Two object selection questions

Simple problem........

Using the standard EMP table.

Create type oemp as object (nempno number(4), vname varchar2(10));

I want to SELECT from the EMP table and put the results DIRECTLY INTO an instance of oemp. For example

declare
  iemp oemp := oemp(null, null);

begin

  select empno, ename into iemp from emp;

end;

gives me a PLS-00385: type mismatch found at 'IEMP' in SELECT...INTO statement

I KNOW THE QUERY WORKS if I specify EVERY column

                select empno, ename into iemp.empno, iemp.vname from emp;

but I do NOT WANT TO DO THAT!

Question 2:

For a collection object I want to do the same thing. Since I cannot have methods on a collection, I want to create a collection object to hold my table, then put the methods on it. The goal is to call a member function that will return me an instance of the collection object that is LOADED. So:

create type emptab as table of oemp;

create type c_emp as object (myemptab emptab);

declare

       col_emp c_emp := c_emp(emptab()); begin

       select cast(multiset(select empno, ename from emp) 
       as emptab) into col_emp.myemptab) from dual;

The above works with the COLLECTION, but I want it to put it into the collection that is the attribute of the collection object.

So, given my limited object knowledge, HOW THE @#$^* DO I GET ORACLE TO LET ME DO THESE? Received on Wed Sep 13 2000 - 23:13:52 CDT