PROC预编译时加入 COMMON_PARSER=YES 选项，支持预编译分析函数（比如 ROW_NUMBER 和 OVER）

来源：互联网发布：cloudtv for mac 编辑：程序博客网时间：2024/06/07 07:01

示例：

SQL> DESC T1;
Name                                        Null? Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                                 VARCHAR2(10)
DATE1                                                 DATE

SQL> SELECT * FROM T1;
      ID NAME                         DATE1
---------- ------------------------------ ------------------
   101 aaa                               09-SEP-13
   101 bbb                               10-SEP-13
   101 ccc                               11-SEP-13
   102 ddd                               08-SEP-13
   102 eee                               11-SEP-13

下面以 SELECT ID,NAME,DATE1 FROM (SELECT ID,NAME,DATE1,ROW_NUMBER() OVER(partition by ID order by DATE1 desc) as rn FROM T1)T WHERE T.rn=1; 语句为例，在sqlplus提示符下执行结果正常，但在proc程序里预编译就不通过。

在sqlplus提示符下执行：

SQL> SELECT ID,NAME,DATE1 FROM (SELECT ID,NAME,DATE1,ROW_NUMBER() OVER(partition by ID order by DATE1 desc) as rn FROM T1)T WHERE T.rn=1;

      ID NAME                         DATE1
---------- ------------------------------ ------------------
   101 ccc                               11-SEP-13
   102 eee                               11-SEP-13

在proc程序里预编译出错：

Syntax error at line 7541, column 77, file test.pc:
Error at line 7541, column 77 in file test.pc
            EXEC SQL SELECT ID,NAME,DATE1 FROM (SELECT ID,NAME,DATE1,ROW_NUM
BER() OVER(partition by ID order by DATE1 desc) as rn FROM T1)T WHERE T.rn=1;
............................................................................1
PCC-S-02201, Encountered the symbol "(" when expecting one of the following:

, into, from,

Error at line 0, column 0 in file test.pc

解决办法：在预编译命令行上加 COMMON_PARSER=YES 选项即可。