An Example For Creating Cursor In Oracle Pro*C

This program connects to ORACLE, declares and opens a cursor, fetches the names, salaries, and commissions of all sales people, displays the results, then closes the cursor. 
The following shows a query that uses an explicit cursor, selecting data into a host structure:

#include <stdio.h>
#include <sqlca.h>

#define UNAME_LEN      20 
#define PWD_LEN        40 
 
/*
 * Use the precompiler typedef'ing capability to create
 * null-terminated strings for the authentication host
 * variables. (This isn't really necessary--plain char *'s
 * does work as well. This is just for illustration.)
 */
typedef char asciiz[PWD_LEN]; 

EXEC SQL TYPE asciiz IS STRING(PWD_LEN) REFERENCE; 
asciiz     username; 
asciiz     password; 

struct emp_info 
{ 
    asciiz     emp_name; 
    float      salary; 
    float      commission; 
}; 


/* Declare function to handle unrecoverable errors. */ 
void sql_error(); 


main() 
{ 
    struct emp_info *emp_rec_ptr; 

/* Allocate memory for emp_info struct. */ 
    if ((emp_rec_ptr = 
        (struct emp_info *) malloc(sizeof(struct emp_info))) == 0)
    { 
        fprintf(stderr, "Memory allocation error.n"); 
        exit(1); 
    } 
 
/* Connect to ORACLE. */ 
    strcpy(username, "SCOTT"); 
    strcpy(password, "TIGER"); 
 
    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
 
    EXEC SQL CONNECT :username IDENTIFIED BY :password; 
    printf("nConnected to ORACLE as user: %sn", username); 
 
/* Declare the cursor. All static SQL explicit cursors
 * contain SELECT commands. 'salespeople' is a SQL identifier,
 * not a (C) host variable.
 */
    EXEC SQL DECLARE salespeople CURSOR FOR 
        SELECT ENAME, SAL, COMM 
            FROM EMP 
            WHERE JOB LIKE 'SALES%'; 
 
/* Open the cursor. */
    EXEC SQL OPEN salespeople; 
 
/* Get ready to print results. */
    printf("nnThe company's salespeople are--nn");
    printf("Salesperson   Salary   Commissionn"); 
    printf("-----------   ------   ----------n"); 
 
/* Loop, fetching all salesperson's statistics.
 * Cause the program to break the loop when no more
 * data can be retrieved on the cursor.
 */
    EXEC SQL WHENEVER NOT FOUND DO break; 

    for (;;) 
    { 
        EXEC SQL FETCH salespeople INTO :emp_rec_ptr; 
        printf("%-11s%9.2f%13.2fn", emp_rec_ptr->emp_name, 
                emp_rec_ptr->salary, emp_rec_ptr->commission); 
    } 
 
/* Close the cursor. */
    EXEC SQL CLOSE salespeople; 
 
    printf("nArrivederci.nn");

    EXEC SQL COMMIT WORK RELEASE; 
    exit(0); 
} 



void 
sql_error(msg) 
char *msg;
{ 
    char err_msg[512];
    int buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("n%sn", msg);

/* Call sqlglm() to get the complete text of the
 * error message.
 */
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    printf("%.*sn", msg_len, err_msg);

    EXEC SQL ROLLBACK RELEASE;
    exit(1);
}