| Cursor Identifier using a variable [message #283247] |
Mon, 26 November 2007 08:06  |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
Hello!
I'm using Oracle Pro*C - Oracle 10.2.0, in a HP-UX B.11.11.
Normally, the sintax for a DECLARE CURSOR statement is like that:
EXEC SQL DECLARE cursor_id CURSOR FOR (statement)
I'd like to make a function that receive the 'cursor_id' as a input parameter.
I can't use neither Host nor C variables to specify the 'cursor_id'.
Are there any possibility to do this?
Thanks!
|
|
|
|
|
|
| Re: Cursor Identifier using a variable [message #283253 is a reply to message #283247] |
Mon, 26 November 2007 08:32  |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
Thanks Michel!
I think it doesn't resolve my trouble.
I want to make something like that:
void cursor_function(char *curname)
{
EXEC SQL DECLARE curname CURSOR FOR....
OPEN ....
FETCH ....
}
Really the cursor's name is different in each call to function.
Can i name the Cursor (or Sql_Cursor) with the *curname variable??
Thanks!
|
|
|
|
|
|
| Re: Cursor Identifier using a variable [message #283266 is a reply to message #283247] |
Mon, 26 November 2007 09:57 |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
You're right!
But, still helping me, please!
In that function, i do this sentences:
int function (SQLDA *sqlda, char *query, -SQL_CURSOR cursor-)
{
// I copy query into a Host var. called strq
// ...
EXEC SQL PREPARE S FROM :strq;
// ...
EXEC SQL DECLARE cursor_name CURSOR FOR S;
// ...
EXEC SQL DESCRIBE BIND VARIABLES for S INTO sqlda_tmp;
// ...
EXEC SQL OPEN cursor_name USING DESCRIPTOR sqlda_tmp;
// ...
EXEC SQL DESCRIBE SELECT LIST FOR S INTO sqlda;
// ...
EXEC SQL FETCH cursor_name USING DESCRIPTOR sqlda;
// ...
}
If i use SQL_CURSOR:
I wouldn't use procedures on server side.
Can i do 'USING DESCRIPTOR' to open and fetch my SQL_CURSOR?
In the doc, two ways are specified :
Embebbed SQL:
EXEC ORACLE OPTION (select_error=no);
EXEC SQL SELECT CURSOR (Select statement) INTO :sql_cursor FROM dual;
EXEC ORACLE OPTION (select_error=yes);
Anonymous PL/SQL:
EXEC SQL EXECUTE
BEGIN
OPEN :sql_cursor FOR 'Select statement'
END;
But, if i use 'USING DESRIPTOR', precompiler crashes:
EXEC SQL EXECUTE
BEGIN
OPEN :cursor FOR S USING DESCRIPTOR sqlda_tmp
END;
// ...
EXEC SQL FETCH cursor_name USING DESCRIPTOR sqlda;
Have you some idea, please?
Thanks a lot!
|
|
|
|
|
|
| Re: Cursor Identifier using a variable [message #283429 is a reply to message #283247] |
Tue, 27 November 2007 02:18 |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
Hello again!
I'll try to do it fine now.
Well, i'm using SQL_CURSOR now, like Michel tolds me yesterday.
But i don't want to use neither packages nor procedures on the server side. I want to do all operations from program code.
Besides, to OPEN and FETCH the SQL_CURSOR, i need to do it with the USING DESCRIPTOR clause.
Like the Oracle's doc. explain, i think i can do it this way:
EXEC ORACLE OPTION (SQLCHECK=SEMANTICS);
EXEC SQL EXECUTE
BEGIN
open :sql_cursor for Stmt using DESCRIPTOR sqlda_tmp;
END;
END-EXEC;
But the precompiler give me that errors:
Pro*C/C++: Release 10.2.0.3.0 -
Production on Mar Nov 27 09:04:04 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Valores de la opción por defecto del sistema tomados de: /oracle/product/10.2.0/db/precomp/admin/pcscfg.cfg
Advertencia en la línea 126, columna 25, archivo file.pc:
EXEC ORACLE OPTION (SQLCHECK=SEMANTICS);
........................1
PCC-W-02374, El valor de SQLCHECK excede el valor de la línea de comandos
Error semántico en la línea 130, columna 14, archivo file.pc:
open :cursor for S using DESCRIPTOR sqlda_tmp;
.............1
PCC-S-02362, Variable del host no declarada en la sección SQL DECLARE
open :cursor for S using DESCRIPTOR sqlda_tmp;
...........................................1
PLS-S-00103, Se ha encontrado el símbolo "SQLDA_TMP" cuando se
esperaba uno de los siguientes:
. ( , * @ % & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
El símbolo "." ha sido sustituido por "SQLDA_TMP" para continuar.
Error semántico en la línea 129, columna 8, archivo file.pc:
BEGIN
.......1
PCC-S-02347, PL/SQL ha encontrado errores de sintaxis
*** Error exit code 1
Stop.
*** Error exit code 1
Stop.
*** Error exit code 1
Stop.
The english description of the errors:
PCC-W-02374: SQLCHECK value exceeds command line value
PCC-S-02362: Host variable don't declared in the section SQL DECLARE.
PLS-S-00103: Encountered the symbol "SQLDA_TMP" when expecting one of the following...
PCC-S-02347: PL/SQL found syntax errors
I have fine declared the SQL_CURSOR.
If i change the first line of the code for this:
EXEC ORACLE OPTION (SQLCHECK=FULL);
I'll have similar errors.
I don't know what i'm doing wrong. Can anybody help me, please?
Thanks!
|
|
|
|
|
|
| Re: Cursor Identifier using a variable [message #283447 is a reply to message #283247] |
Tue, 27 November 2007 03:11 |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
Hello!
I have already fine declared the SQL_CURSOR, and modified the pcscfg.cfg file to don't give me the SQLCHECK error.
Now, my code is the same:
EXEC SQL PREPARE S FROM :query;
/*...*/
EXEC SQL DESCRIBE BIND VARIABLES for S INTO sqlda_tmp;
/*...*/
EXEC ORACLE OPTION (SQLCHECK=SEMANTICS);
EXEC SQL EXECUTE
BEGIN
open :cursor for S using DESCRIPTOR sqlda_tmp;
END;
END-EXEC;
I still have this errors:
Error en la línea 132, columna 44 del archivo file.pc
open :cursor for S using DESCRIPTOR sqlda_tmp;
...........................................1
PLS-S-00103, Se ha encontrado el símbolo "SQLDA_TMP" cuando se esperaba uno de l
os siguientes:
. ( , * @ % & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
El símbolo "." ha sido sustituido por "SQLDA_TMP" para continuar.
Error semántico en la línea 131, columna 8, archivo file.pc:
BEGIN
.......1
PCC-S-02347, PL/SQL ha encontrado errores de sintaxis
*** Error exit code 1
Stop.
'sqlda_tmp' works fine before the OPEN clause.
I think the PL/SQL block is correctly.
Some idea? Thanks.
|
|
|
|
|
|
| Re: Cursor Identifier using a variable [message #283468 is a reply to message #283247] |
Tue, 27 November 2007 04:49 |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
This last link seems not work ver fine with SQL_CURSOR.
However, i think my error is this:
If i do:
EXEC SQL EXECUTE
BEGIN
OPEN :cursor for :query;
END;
END-EXEC;
I got no error. But if i do:
EXEC SQL EXECUTE
BEGIN
OPEN :cursor for :query using DESCRIPTOR sqlda_tmp;
END;
END-EXEC;
i got this error:
Error en la línea 149, columna 49 del archivo file.pc
OPEN :cursor for :strq using DESCRIPTOR sqlda_tmp;
................................................1
PLS-S-00103, Se ha encontrado el símbolo "SQLDA_TMP" cuando se esperaba uno de los siguientes:
. ( , * @ % & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
El símbolo "." ha sido sustituido por "SQLDA_TMP" para continuar.
Error semántico en la línea 148, columna 5, archivo file.pc:
BEGIN
....1
PCC-S-02347, PL/SQL ha encontrado errores de sintaxis
*** Error exit code 1
I'm looking in the links you gave me, but i can't find the solution.
In the section Cursor Variables - Open a Cursor Variable i see:
To open a cursor using a PL/SQL anonymous block in your Pro*C/C++ program,
you define the cursor in the anonymous block. For example:
sql_cursor emp_cursor;
int dept_num = 10;
...
EXEC SQL EXECUTE
BEGIN
OPEN :emp_cursor FOR SELECT ename FROM emp
WHERE deptno = :dept_num;
END;
END-EXEC;
...
But there don't explain about USING DESCRIPTOR clause.
In the last link, Implementing Oracle Method 4, this sentences don't work fine with SQL_CURSOR.
Sorry, but i'm confused. Why don't work my code with the 'USING DESCRIPTOR' clause?
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
