Oracle Analytics inside Cursor

Question

Hello Oracle Experts,

I'm wondering why my "analytics" line inside a "cursor" in Oracle Forms generates an error. This is how it's written:

CURSOR READ_C1_REC IS  
   SELECT  DISTINCT MACH, TCODE,   
     COUNT(ALL TRANS_NO) OVER (PARTITION BY MACH, 
     TCODE ORDER BY MACH, TCODE) TOTALPERMACHPERTCODE  
   FROM  
   (
     SELECT ....
    )  
    ORDER BY MACH, TCODE;  
C1_REC READ_C1_REC%ROWTYPE;

...and this is the error message during compilation:

Error 103 at line 17, column 29
  Encountered the symbol "(" when expecting one of the following:

    , from

..and the error points to the part after the word "OVER" in the analytics.

What could be wrong with my code? Is analytics allowed inside a "cursor"?

:)

Answer 1

The SQL you can use in Forms hasn't progressed in over a decade. Dynamic SQL is the best answer. I think you should look at EXEC_SQL

Answer 2

Oracle Forms has (or at least did at one time) its own PL/SQL engine, and this generally lags behind the database product in terms of the features it supports. It may be that your version of Forms has a PL/SQL engine that pre-dates the introduction of analytic functions.

You have said you cannot create views; can you create packages? If so perhaps you could move the code into a package and call it from Forms.