Analytic functions in Pro*c - High Level Programming

quintet · #1 06-03-2008

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...

jim mcnamara · #2 06-03-2008

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:

Code:

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.

quintet · #3 06-05-2008

Hi Jim,

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

My Pro*C version:
Pro*C/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?

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Use of functions	amitrajvarma	Shell Programming and Scripting	1	11-23-2007 03:52 AM
ksh functions	scriptingmani	Shell Programming and Scripting	3	07-06-2007 04:15 AM
functions in	Raom	Shell Programming and Scripting	6	07-21-2006 12:06 AM
Regarding functions	sendhilmani	Shell Programming and Scripting	2	03-24-2006 01:40 AM
FTP functions	hzambra	High Level Programming	3	05-03-2002 10:06 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode