Home » SQL & PL/SQL » SQL & PL/SQL » Use of statistical functions in PL/SQL Script
Use of statistical functions in PL/SQL Script [message #37136] Sun, 20 January 2002 19:49 Go to next message
Steve Latz
Messages: 1
Registered: January 2002
Junior Member
I am somewhat new to PL/SQL programming. I am working in V 8.1.7 trying to use CORR and COVAR_POP statistical functions on a pair of timeseries in a script. I have tested the SQL query on a PL/SQL command line with no problem [[e.g. "select corr(a.logreturn,b.logreturn) from pricehistory a, pricehistory b where a.id = 'IBM' and b.id = '.SPX' and a.valuedate = b.valuedate and a.logreturn is not null and b.logreturn is not null]].

When I try to use an analogous query in a script, I get a compile error that "CORR" is not declared.

Does one have to do something special to make the Oracle SQL statistical functions "visible" in a PL/SQL script?

Any help or insight would be appreciated.

Thanks
Re: Use of statistical functions in PL/SQL Script [message #37140 is a reply to message #37136] Mon, 21 January 2002 00:48 Go to previous message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Hi,

i think it is bug in the pl/sql parser.in oracle 8.x... SQL engine and pl/sql engine use different parsers.PL/SQL engine use the same parser as SQL in Oracle9i.

so workaround is to use dynamic pl/sql(which uses sql engine).

here is an example;

SQL> select * from my_table;

COL1 COL2
--------- ---------
1 2
1 2
3 4
34 44

SQL> set serveroutput on
SQL> declare
2 n number;
3 begin
4 execute immediate 'select corr(col1,col2) from my_table' into n;
5 dbms_output.put_line (n);
6 end;
7 /
.9999198136110651179564758875262072741699

PL/SQL procedure successfully completed.


Hope it helps!!!

cheers
pratap
Previous Topic: disallowing insert into table using trigger without error message...
Next Topic: SCRIPT TO CREATE HOST STRING
Goto Forum:
  


Current Time: Fri May 29 09:59:45 CDT 2020