This discussion is archived
0 Replies Latest reply: Apr 22, 2013 1:14 PM by Neill_R RSS

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

Neill_R Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points