0 Replies Latest reply: Apr 22, 2013 3:14 PM by Neill_R RSS

    Sql statement works in SQLPLUS but will not pass the checking in PRO*C

    Neill_R
      Hi

      I am in the process of converting my skill in oracle and this time PRO*C from Windows to Linux. I have oracle 11g R2 installed on a UBUNTU (12.04) server and have installed the instant client as described in

      [http://oradim.blogspot.co.uk/2009/09/getting-started-with-oracle-proc-on.html|http://oradim.blogspot.co.uk/2009/09/getting-started-with-oracle-proc-on.html]

      This has a query which will run in SQLPLUS but fails with PRO*C

      :~/Projects/proc/proctest1$ proc INAME=proctest.pc SQLCHECK=NONE ONAME=proctest.c LNAME=proctest.lis LTYPE=long

      Pro*C/C++: Release 11.2.0.3.0 - Production on Mon Apr 22 21:00:18 2013

      Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

      System default option values taken from: /home/neill.rutherford/instantclient_11_2/precomp/admin/pcscfg.cfg

      PCC-W-02109, SQLCHECK=NONE is no longer supported.. using SYNTAX
      Syntax error at line 109, column 27, file proctest.pc:
      Error at line 109, column 27 in file proctest.pc
      (ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc)
      ) AS rn
      ..........................1
      PCC-S-02201, Encountered the symbol "OVER" when expecting one of the following:

      ) * + - / | at, day, hour, minute, month, second, year,

      Syntax error at line 0, column 0, file proctest.pc:
      Error at line 0, column 0 in file proctest.pc
      PCC-S-02201, Encountered the symbol "<eof>" when expecting one of the following:

      ; : an identifier, end-exec, random_terminal

      Error at line 0, column 0 in file proctest.pc
      PCC-F-02102, Fatal error while doing C preprocessing


      if i remove the ROW_NUMBER() line and replace it with say

      1 AS rn

      all works fine



      EXEC SQL WHENEVER NOT FOUND DO break;

      /*
      ** declare and open cursor using analytic function
      ** to get "top 3" in department by highest salary
      */
      EXEC SQL DECLARE emp_cursor CURSOR FOR
           select *
           from
           (select
      department_id,
                first_name,
                last_name,
                salary,
      ROW_NUMBER() OVER (PARTITION BY department_id, ORDER BY salary desc) AS rn
           from employees
           where department_id is not null)
      where rn<=3;

      ~/Projects/proc/proctest1$ sqlplus hr@noiptnsgworcl

      SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 22 21:10:15 2013

      Copyright (c) 1982, 2011, Oracle. All rights reserved.

      Enter password:

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      select *
      from
      (
      select department_id,
      first_name,
      last_name,
      salary,
      row_number() over (partition by department_id
      order by salary desc) rn
      from employees
      where department_id is not null
      )
      13 where rn <= 3
      14 /



      SQL> set linesize 200
      SQL> /

      DEPARTMENT_ID FIRST_NAME     LAST_NAME               SALARY      RN
      ------------- -------------------- ------------------------- ---------- ----------
           10 Jennifer          Whalen               4400      1
           20 Michael          Hartstein               13000      1
           20 Pat          Fay                    6000      2
           30 Den          Raphaely               11000      1
           30 Alexander      Khoo                3100      2
           30 Shelli          Baida               2900      3
           40 Susan          Mavris               6500      1
           50 Adam          Fripp               8200      1
           50 Matthew          Weiss               8000      2
           50 Payam          Kaufling               7900      3
           60 Alexander      Hunold               9000      1

      DEPARTMENT_ID FIRST_NAME     LAST_NAME               SALARY      RN
      ------------- -------------------- ------------------------- ---------- ----------
           60 Bruce          Ernst               6000      2
           60 David          Austin               4800      3
           70 Hermann          Baer                10000      1
           80 John          Russell               14000      1
           80 Karen          Partners               13500      2
           80 Alberto          Errazuriz               12000      3
           90 Steven          King                24000      1
           90 Neena          Kochhar               17000      2
           90 Lex          De Haan               17000      3
           100 Nancy          Greenberg               12008      1
           100 Daniel          Faviet               9000      2

      DEPARTMENT_ID FIRST_NAME     LAST_NAME               SALARY      RN
      ------------- -------------------- ------------------------- ---------- ----------
           100 John          Chen                8200      3
           110 Shelley          Higgins               12008      1
           110 William          Gietz               8300      2

      25 rows selected.