Analytic functions in Pro*c

Applications Programming
1

Hi All,

when I use the following analytic function in sql prompt, i am getting the result

count(emp_no) over (partition by emp_no )

/* select count(emp_no) over (partition by emp_no ) from temp */

but when i use the same analytic function in Pro*c i get the following error

Error at line 160, column 36 in file test.pc
count(emp_no) over (partition by emp_no)
...................................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
PCC-F-02102, Fatal error while doing C preprocessing
rm: sef_msel.c: No such file or directory
make: The error code from the last command is 2.

Can you advice me how to use analytic function in Pro*c?

Thanks...

2

The parser for SQLPLUS is different than the one for Pro*C.
You will have to use embedded PL/SQL define bound variables, then use those to retrieve your PL/SQL cursor eg:

EXEC SQL BEGIN DECLARE SECTION;
      long myarray[500]={0};
EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE
DECLARE 
     other variables go here
BEGIN
select count(emp_no) over (partition by emp_no ) from temp 
     into :my_array;
END;
/

END-EXEC;

You can also declare a SQL_CURSOR, ALLOCATE the cursor, have PL/SQL declare the cursor in embedded pl/sql, then OPEN the cursor in C, close the cursor in C, finally FREE the cursor. Check your docset for examples.

3

Hi Jim,

Thanks for you answer.. but i received the same error again even if i put in a PL/SQL block?

My ProC version:
ProC/C++: Release 9.2.0.6.0 - Production on Thu Jun 5 15:52:25 2008

Error Message:

select count(cln_no) over (partition by cln_no ) from temp
...........................1
PLS-S-00103, Encountered the symbol "(" when expecting one of the following:

, from into bulk

I used the following compiler options:
proc parse=full mode=oracle userid=****/**** sample.pc sqlcheck=semantics
I used the query in the pl/sql block only.
Can you please tell me where i would have went wrong?