Re: PLS-00201: identifier 'STDDEV_POP' must be declared

> When trying to use the STDDEV_POP() analytic function inside a stored
> procedure i get a compilation error:
>
> The following error has occurred:
> ORA-06550: line 5, column 8:
> PLS-00201: identifier 'STDDEV_POP' must be declared
> ORA-06550: line 5, column 1:
> PL/SQL: SQL Statement ignored
> ORA-06550: line 10, column 1:
>
>
> But the same STDDEV_POP() when used in a select statement directly, it
> gives the expected result.
>
> I am facing this problem in Oracle 8.1.7.0.0 version. Waiting for a
> solution at the earliest to overcome this problem.

This is because Oracles 8.1 PL/SQL engine lags a bit behind the SQL enigne (which is fixed with 9i). In your case, you need to do some dynamic cursors:

declare
  type t_cr is ref cursor;
  tc t_cr;
  f1 <result_datatype>;
  f2 <result_datatype>;
begin
  open tc for 'select stddev_pop(...) over (...) where which what';   loop
    fetch tc into f1, f2....;
    exit when tc%notfound;

• do something

  end loop;
end;
/

You can even use bind variables this way:

  open tc for 'select stddev_pop(...) over (...) where x=:1 and y=:2   which what' using v_x, v_y;

Not tested, but this is the direction you need to go.

Hth
Rene

-- 
  Rene Nyffenegger
  www.adp-gmbh.ch