Visit The New, Modern Unix Linux Community


Analytic functions in Pro*c


 
Thread Tools Search this Thread
Top Forums Programming Analytic functions in Pro*c
# 1  
Analytic functions in Pro*c

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:
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.
# 3  
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?

Last edited by quintet; 06-05-2008 at 12:01 PM..

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #381
Difficulty: Medium
Solaris 9 is registered as UNIX 03 compliant on 32-bit and 64-bit x86 (X86-64) and SPARC systems
True or False?

8 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Deleting unused kdb+ analytic files in RedHat

Hello guys :) I am new to using Unix and was hoping somebody could help me. Essentially, I am trying to clean out my database. For example, I have a directory filled with 100s of analytics and I want to know if they are used by any other analytics or if they are used by the front end user... (17 Replies)
Discussion started by: Michael37
17 Replies

2. Shell Programming and Scripting

How to execute functions or initiate functions as command line parameters for below requirement?

I have 7 functions those need to be executed as command line inputs, I tried with below code it’s not executing function. If I run the ./script 2 then fun2 should execute , how to initiate that function I tried case and if else also, how to initiate function from command line if then... (8 Replies)
Discussion started by: saku
8 Replies

3. Programming

Functions

Hi All, Can any one help me. I am calling in a function2 with string as parameter from function1, the function1 gives 3 values. how i get the 3 values from funciton2 to function1. i have to give a return or something. thanks in advance. (2 Replies)
Discussion started by: uday.sena.m
2 Replies

4. Shell Programming and Scripting

i think i need functions ?

Hi, im making a little script but need some help Code i have so far is read -p 'Bot Nickname:' ecnick read -p 'Bot Username:' ecusername read -p 'Bot Realname:' ecrealname read -p 'Your Email:' ecemail echo '' echo Your bots nickname is set to $ecnick echo Your bots username is set to... (2 Replies)
Discussion started by: Gemster
2 Replies

5. UNIX for Dummies Questions & Answers

Help with functions

Hi, I am exploring with defining functions in my BASH shell scripts. However, I am bit confused about how to pass parameters to my functions. I was under the impression that you must do something like the following: Define a function called "sample_function": function sample_function {... (3 Replies)
Discussion started by: msb65
3 Replies

6. Shell Programming and Scripting

Use of functions

Hi my shell is tcsh can I have functions in my shell scripting? Is the below shell script correct. Can I have two functions and call one of them as required. ---------- echo "functions" f1 f1 () { echo "hello" } f2 () (1 Reply)
Discussion started by: amitrajvarma
1 Replies

7. Shell Programming and Scripting

Regarding functions

Hi, I have a function or script like this. show() { echo "Hi" } | tee -a log show This creates a logfile and prints Hi in it. Now when I try to do the same for sql like this: show() { sqlplus -s scott/tiger<<! select * from details; ! } | tee -a log show Then it gives me a... (2 Replies)
Discussion started by: sendhilmani
2 Replies

8. UNIX for Dummies Questions & Answers

domain logon problem - FreeBSD PDC w/ win2k pro and winxp pro

this is the seventh problem i'm having with samba. for some reason, i cannot logon to the domain. i've created user accounts... and i was able to establish a connection between the samba server (my PDC) and my workstations by logging in as "root." however now when i try to logon it gives... (5 Replies)
Discussion started by: xyyz
5 Replies

Featured Tech Videos