Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> 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>...Received on Tue Sep 19 2000 - 15:49:30 CDT
>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?
![]() |
![]() |