Embedded SQL FETCH

Question

In Oracle Pro*C embedded SQL (Ref: https://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_06sql.htm), I would like to FETCH INTO three host variables: e.g.

EXEC SQL FETCH emp_cursor 
INTO :emp_name, :emp_number, :salary;

In another variation, I would like to use two host variables, as below:

EXEC SQL FETCH emp_cursor 
INTO :emp_name, :emp_number;

Is there any mechanism where I can have a character string declared and manipulated to have the dynamic content as the first example of FETCH with three variables or FETCH with two variables as in the second. Is there any mechanism to have this dynamism? Also, if I would like to fetch 200 rows max, how should these host variables be declared ?

score 0 · Answer 1 · answered May 29 '17 at 16:56

use descriptor. see this link https://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_14ady.htm

for example, you need to use a cursor for that string:

SELECT ename, empno FROM emp ;

you need to create two values (ename, empno) in the descriptor .Those values needs 3 attributes: type,len and data (see table in the link)

//attributes for ename 
int ename_type = 97, ename_len = 30 ;
char ename_data[31] ;

//attributes for empno 
int empno_type = 3, empno_len = 4 ;
int empno_data ;

the complite code is here:

char* dyn_statement = "SELECT ename, empno FROM emp" 
;
int ename_type = 97, ename_len = 30 ;
char ename_data[31] ;
int empno_type = 3, empno_len = 4 ;
int empno_data ;
long SQLCODE = 0 ;
...
main ()
{
/* Place preliminary code, including connection, here. */
...

EXEC SQL ALLOCATE DESCRIPTOR 'out'; //start the descriptor
EXEC SQL PREPARE s FROM :dyn_statement ;
EXEC SQL DECLARE c CURSOR FOR s ;
EXEC SQL DESCRIBE OUTPUT s USING DESCRIPTOR 'out' ;

//pass ename's attributes for DESCRIPTOR
EXEC SQL SET DESCRIPTOR 'out' VALUE 1 TYPE = :ename_type, 
    LENGTH = :ename_len, DATA = :ename_data ;
//pass empno's attributes for DESCRIPTOR
EXEC SQL SET DESCRIPTOR 'out' VALUE 2 TYPE = :empno_type, 
    LENGTH = :empno_len, DATA = :empno_data ;

EXEC SQL WHENEVER NOT FOUND DO BREAK ;
while (SQLCODE == 0) 
{
   EXEC SQL FETCH c INTO DESCRIPTOR 'out' ;
   EXEC SQL GET DESCRIPTOR 'out' VALUE 1 :ename_data = DATA ;
   EXEC SQL GET DESCRIPTOR 'out' VALUE 2 :empno_data = DATA ;
   printf("\nEname = %s Empno = %s", ename_data, empno_data) ;
}
EXEC SQL CLOSE c ;
EXEC SQL DEALLOCATE DESCRIPTOR 'out' ;//close descriptor
...
}

Embedded SQL FETCH

1 Answers1