Re: how to use an object in a select statement?

André Rothe wrote:

> Hi,
>
> I wrote a function in package which returns an object like
> the following:
>
> create or replace type as object (
> id number,
> test number,
> adate date
> );
>
> The function works very well, but I cannot use the
> return value in a select statement.
>
> I tried:
>
> select pa.func().id from dual;
>
> But it doesn't work. How can I write a select statement,
> which use the attributes of the object?
> Can I use the return values without a temporary table
> in which I have to copy the values?
>
> Thank you
> Andre

CREATE TYPE ProjectList AS VARRAY(50) OF Project; /

CREATE TABLE department (
dept_id NUMBER(2),
name VARCHAR2(15),
budget NUMBER(11,2),
projects ProjectList);

INSERT INTO department

VALUES(30, 'Accounting', 1205700,ProjectList (
Project(1, 'Design New Expense Report',3250),
Project(2, 'Outsource Payroll', 12350),
Project(3, 'Evaluate Merger Proposal', 2750),
Project(4, 'Audit Accounts Payable', 1425)));

Correct syntax generally works.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)