Re: insane error?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 14 Sep 1999 14:52:19 +0100
Message-ID: <937317188.29606.0.nnrp-14.9e984b29@news.demon.co.uk>

In theory I think you should be table to do the following in 8.0, but on my NT 4/SP3/8.0.5 system it crashes with ORA-03113.

Please let me know if it works for you
(NB In 8.1.5 you can do exactly as you
want, the ORDER BY inside the view
is valid).

This script runs against the DEMO
id, using the CUSTOMER table.

create or replace type jpl_row as
ct(

        id  number,
        description varchar2(40) 

);
/

create or replace type jpl_array as table of jpl_row; /

select id, description
from

    the (

        select 
            cast(
              multiset(
                   select customer_id, address
                   from customer
                   order by address
              )
              as jpl_array
            )
        from dual

    )
where rownum <= 10
;

The cast(multiset()) turns the ordered
select statement into an object table;
then the 'the select ()' turns the now
ordered object table back into a
row set, from which you select
the first 10.

--

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

kev wrote in message <37DE1BE1.B6381D0F_at_fast.no>...
>Jonathan Lewis wrote:
>
>> Order by in views is not legal in 8.0.5,
>> so the error arises because a closing
>> bracket is expected after
>> > AND category.sect = section.id
>>
>
>Ah, I see. How else should it be done then?
>It seems to me that I'm only trying to do what many other people must need
>to do also. Surely there is a way this can be done satisfactorily?
Received on Tue Sep 14 1999 - 08:52:19 CDT