Re: Order by Problem on 8i

It's a standard feature of Oracle (prior to 9) that there are legal constructs that are not recognised within the PL/SQL version of the SQL parser. (Try using an inline view with an order by in pl/sql, or using an analytic function - these probably fail too under your version).

The standard workaround is to put quotes around the SQL, and execute it as dynamic sql using:

    execute immediate
    'piece of SQL'
    ;

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

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

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

tIM wrote in message ...


>> As usual no exact version and no error messages.

>

>The version is 8.1.7 NT where as this works on 9i.

>The point was to show a reproducable example. The real code is much more

complex.


>

>What I do is insert so records into a Temp Table in order then extract them

>from the temp table doing some other stuff.

>

>

>The point is why does INSERT INTO ... SELECT ... ORDER BY

>work when run stand alone but does not compile when placed in a procedure.

>

>I rewrote without the ordder by in the insert and handled it another way.

>Still wondering if this is a bug or just awkward SQL.

>

>The other post probably hints to my answer that I needn't care about

>about the order by which is what I did.

>

>Thanks