Home » Developer & Programmer » Precompilers, OCI & OCCI » Cursor Identifier using a variable (Oracle Pro*C - Oracle 10.2.0)
Cursor Identifier using a variable [message #283247] Mon, 26 November 2007 08:06 Go to next message
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 #283249 is a reply to message #283247] Mon, 26 November 2007 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Pro*C/C++ Programmer's Guide
Chapter 4 Datatypes and Host Variables
Section Cursor Variables

Regards
Michel
Re: Cursor Identifier using a variable [message #283253 is a reply to message #283247] Mon, 26 November 2007 08:32 Go to previous messageGo to next message
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 #283254 is a reply to message #283253] Mon, 26 November 2007 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't stay with your first idea.
Re-read and study the examples in the link, they perfectly fit what you want to do.

Regards
Michel
Re: Cursor Identifier using a variable [message #283266 is a reply to message #283247] Mon, 26 November 2007 09:57 Go to previous messageGo to next message
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 #283297 is a reply to message #283266] Mon, 26 November 2007 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Nothing comes into my mind now and I don't have the environment to make test and even for this I must have the real code.

Regards
Michel
Re: Cursor Identifier using a variable [message #283429 is a reply to message #283247] Tue, 27 November 2007 02:18 Go to previous messageGo to next message
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 #283439 is a reply to message #283429] Tue, 27 November 2007 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

PCC-02374: SQLCHECK value exceeds command line value
Cause: The value given to the SQLCHECK option in an EXEC ORACLE statement in a program was greater than the value given either on the command line or greater than the default value if no SQLCHECK option was given on the command line. The order of the option values is SEMANTICS>SYNTAX>NONE. When this warning message is issued, the original value of SQLCHECK (the default or the command-line value) stays in effect.
Action: Remove or recode the EXEC Oracle option(s) in the .pc source file to avoid this warning message.

Quote:

PCC-02362: Host variable not declared within SQL DECLARE section
Cause: When MODE=ANSI is specified at precompile time, all host variables must be declared inside Declare Sections. Remember that MODE=ANSI refers to ANSI SQL, not ANSI C.
Action: Add the EXEC SQL BEGIN DECLARE SECTION... EXEC SQL END DECLARE SECTION statements around all host variable declarations.

For cursor error:
Pro*C/C++ Programmer's Guide
Chapter 13 Oracle Dynamic SQL
Section Using Method 4
Subsection Implementing Oracle Method 4

Regards
Michel


Re: Cursor Identifier using a variable [message #283447 is a reply to message #283247] Tue, 27 November 2007 03:11 Go to previous messageGo to next message
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 #283457 is a reply to message #283447] Tue, 27 November 2007 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not the syntax that is in the documentation.
Re-read the last link I posted.

Regards
Michel
Re: Cursor Identifier using a variable [message #283468 is a reply to message #283247] Tue, 27 November 2007 04:49 Go to previous messageGo to next message
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?
Re: Cursor Identifier using a variable [message #283477 is a reply to message #283468] Tue, 27 November 2007 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Why don't work my code with the 'USING DESCRIPTOR' clause?

Because this is not a valid syntax.

Regards
Michel
Re: Cursor Identifier using a variable [message #283568 is a reply to message #283247] Tue, 27 November 2007 08:38 Go to previous messageGo to next message
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
Member
Hey!

In the section of Cursor Variables > Restrictions:
You cannot use cursor variables with dynamic SQL.

Well, i need Dynamic SQL, and i can't use SQL_CURSOR... i guess i can't do what i was trying.

Thanks so much!
Re: Cursor Identifier using a variable [message #283576 is a reply to message #283568] Tue, 27 November 2007 09:01 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback and the link.

Regards
Michel
Previous Topic: about proc++
Next Topic: fwrite
Goto Forum:
  


Current Time: Wed Dec 07 18:23:02 CST 2016

Total time taken to generate the page: 0.10948 seconds