Re: Oracle Analytical Function ratio_to_report

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 21 Jul 2001 21:34:47 GMT
Message-ID: <9il84s0284@drn.newsguy.com>

In article <3B4E1CCD.1A7E2FF0_at_ea.com>, Abhijit says...
>
>Is it possible to use ratio_to_report function within PL/SQL block ? I
>used it from SQL*PLUS, it worked. I could have it work from Perl DBI
>also. When I try to use it with PL/SQL blockor PRO*C program then it
>generates syntax error . Any help will be greatly appreciated .
>
>DECLARE
>v_sales sales%ROWTYPE ;
>BEGIN
> SELECT name,
> country,
> SUM(SUM(amt)) OVER (PARTITION BY COUNTRY) AS sum_amt
> INTO v_sales
> FROM sales
> WHERE name = 'ABHI'
> GROUP BY name,country ;
> DBMS_OUTPUT.PUT_LINE(v_sales.name||' '||v_sales.country) ;
>END ;
>
>SUM(SUM(amt)) OVER (PARTITION BY COUNTRY) AS sum_amt
> *
>ERROR at line 6:
>ORA-06550: line 6, column 30:
>PLS-00103: Encountered the symbol "(" when expecting one of the
>following:
>, from into bulk
>

Until 9i, you have to hide that construct from PLSQL -- either in a view or by using dynamic sql:

DECLARE
v_sales sales%ROWTYPE ;
BEGIN
   execute immediate
   'SELECT name,

          country,
          SUM(SUM(amt)) OVER (PARTITION BY COUNTRY) AS sum_amt

   FROM sales
   WHERE name = ''ABHI''
   GROUP BY name,country' into v_sales.name, v_sales.country, v_sale.amt;    DBMS_OUTPUT.PUT_LINE(v_sales.name||' '||v_sales.country) ; END ;

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Jul 21 2001 - 16:34:47 CDT