Get rid of select from dual! Hah, the next thing you will suggest is that we
convert to the metric system!

Marge: Now, I know you haven't liked some of my past suggestions, like
switching to the metric system --
Abe: The metric system is the tool of the devil! My car gets forty rods to
the hogshead and that's the way I likes it.
- The Simpsons episode "A Star is Burns"

- Ethan

-----Original Message-----
Sent: Thursday, March 07, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L

The "the()" operator, which changed to the "table()"
operator in 8.1 takes a single value that has to be
a nested table or varray as its operand.

What you are casting is the single row and single
column that you select from dual, but that single
item is the result of casting a result set into
a nested table or varray.

In fact in Oracle 9, you can get rid of the
select from dual, and for a function returning
the varray/nested table write:

select * from table(function_name);

or, if TABX is a table with a column COLX

select colx, function_name(colx)
from TABX;


You can play similar games with 8.1, but
the SQL looks messier.  You may then
need to wrap one level of SQL inside an
inline view to convert the whole thing into
a flattened table.



Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

<<winmail.dat>>

Reply via email to