Re: Two object selection questions

Qn 1:

  1 declare
  2 iemp oemp := oemp(null, null);
  3 begin
  4 select oemp(empno, ename) into iemp from emp;   5* end;
  6 .
SQL> / PL/SQL procedure successfully completed.

Qn 2:

    You have done exactly what you appear to want:     You have an object type with a single attribute that is a collection type

    You have set the collection-type attibrute to a value which is the result set of the query.

    Try adding the lines:

     dbms_output.put_line(col_emp.myemptab(1).nempno);
     dbms_output.put_line(col_emp.myemptab(2).nempno);
     dbms_output.put_line(col_emp.myemptab(3).nempno);

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

dperez_at_juno_nospam.com wrote in message
<39c05074.264885425_at_news.uswest.net>...


>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;

>end;

>

>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?