Re: Analytic Functions in Stored Procedure 8.1.7 - do they work?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 18 Jan 2004 17:48:23 +0000 (UTC)
Message-ID: <buegt7$js$1@hercules.btinternet.com>

As you've found, pl/sql in 8.1.7 does not recognise analytic functions. The standard workaround is to use weak ref cursors:

declare

    type ref_cursor is ref cursor;
    c1 ref_cursor;
begin

    open c1 for 'select .....';

    loop

        fetch c1 into {list of variables};
        exit when c1%notfound;

    end loop;

end;
/

As another poster suggested, you could hide the analytic functions inside a view, but you may find that Oracle instantiates the entire view before applying any predicate you want to use. This may make the view extremely inefficient.

-- 
Regards

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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February


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


"Charlie Brown" <cbrown_at_ghx.com> wrote in message
news:dfd4623d.0401161520.67a173c4_at_posting.google.com...


> I've got a SQL script with MIN and MAX analytic functions that works

> just fine all by itself but as soon as I try to compile a procedure

> using that same script it fails after the OVER saying it's looking for

> either ',' or 'from'.  I'm by no means a guru but have compiled other

> procedures without analytic functions without any problem.  Will 8.1.7

> accept an analytic function within a procedure?

>

> Any advice or information....????  Thanks

>

Received on Sun Jan 18 2004 - 11:48:23 CST