Oracle Pro*c Example to Query the Data from a Table using Cursors

The following example demonestrate how to Query a table using Oracle Pro*c Pre-compiler. In the example I have used the table employees. Following are the Steps.

Create a file CursorExample.pc and past the following contents.

1. #include <stdio.h>  
2. #include <string.h>  
3. #include <stdlib.h>  
4. #include <sqlca.h>  
5. #include <sqlda.h>  
6. #include <sqlcpr.h>  
7. #define NAME_LEN 30  
8. #define ERR_LEN 512  
9.   
10. VARCHAR username[NAME_LEN];  
11. VARCHAR password[NAME_LEN];  
12. VARCHAR database[NAME_LEN];  
13.   
14. /* 
15. ** host variables to hold results of query 
16. */  
17. EXEC SQL BEGIN DECLARE SECTION;  
18. struct {  
19.         int empId;  
20.         VARCHAR firstName[NAME_LEN];  
21.         VARCHAR lastName[NAME_LEN];  
22.         int salary;  
23.         int deptNo;  
24. } empRec;  
25.   
26. struct  
27. {  
28.         short emp_name_ind;  
29.         short sal_ind;  
30.         short comm_ind;  
31. } empRecInd;  
32.   
33. int     empId;  
34. VARCHAR firstName[NAME_LEN];  
35. VARCHAR lastName[NAME_LEN];  
36. int     salary;  
37. int     deptNo;  
38. EXEC SQL END DECLARE SECTION;  
39. /* 
40. ** standard Pro*C error handler function 
41. */  
42. void sql_error(char *msg)  
43. {  
44.         char err_msg[ERR_LEN];  
45.         size_t buf_len, msg_len;  
46.   
47.         EXEC SQL WHENEVER SQLERROR CONTINUE;  
48.   
49.         printf("\n");  
50.   
51.         if (msg)  
52.         {  
53.                 printf("%s\n", msg);  
54.         }  
55.   
56.         buf_len = sizeof (err_msg);  
57.         sqlglm(err_msg, &buf_len, &msg_len);  
58.         printf("%.*s", msg_len, err_msg);  
59.   
60.         EXEC SQL ROLLBACK RELEASE;  
61.   
62.         exit(EXIT_FAILURE);  
63. }  
64.   
65. int main(int argc, char *argv[])  
66. {  
67.         strncpy((char *) username.arr, "username", NAME_LEN);  
68.         username.len = (unsigned short) strlen((char *) username.arr);  
69.   
70.         strncpy((char *) password.arr, "password", NAME_LEN);  
71.         password.len = (unsigned short) strlen((char *) password.arr);  
72.   
73.         strncpy((char *) database.arr, "ip address:port_no/database_name", NAME_LEN);  
74.         database.len = (unsigned short) strlen((char *) database.arr);  
75.   
76.         /* 
77.         ** register the error handler function 
78.         */  
79.         EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error: \n");  
80.   
81.         /* 
82.         * attempt to connect to the database 
83.         */  
84.         EXEC SQL CONNECT :username IDENTIFIED BY :password USING :database;  
85.   
86.         /* 
87.         ** indicate we want to break out of the loop when we get a no data 
88.         ** found message (i.e. at end of fetch) 
89.         */  
90.         EXEC SQL WHENEVER NOT FOUND DO break;  
91.   
92.         /* 
93.         ** declare and open cursor to fetch the employee details 
94.         */  
95.         EXEC SQL DECLARE emp_cursor CURSOR FOR  
96.   
97.         SELECT employee_id,first_name,last_name,salary,department_id  
98.         FROM employees WHERE ROWNUM<=10;  
99.         EXEC SQL OPEN emp_cursor;  
100.   
101.         /* 
102.         ** loop through cursor fetching results 
103.         ** and printing as we go 
104.         */  
105.   
106.         /* 
107.         ** indicate we want to break out of the loop when we get a no data 
108.         ** found message (i.e. at end of fetch) 
109.         */  
110.         EXEC SQL WHENEVER NOT FOUND DO break;  
111.         printf("%-15s%-30s%-30s%-15s%-15s\n", "EmployeeId", "FirstName","LastName", "Salary","DepartmentNo");  
112.         printf("------------------------------------------------------------------------------------------------------\n");  
113.         for (;;)  
114.         {  
115.                 EXEC SQL FETCH emp_cursor  
116.                 INTO :empRec;  
117.   
118.                 empRec.firstName.arr[empRec.firstName.len] = '\0';  
119.                 empRec.lastName.arr[empRec.lastName.len]= '\0';  
120.                 printf("%-15d%-30s%-30s%-15d%-15d\n", empRec.empId, empRec.firstName.arr,empRec.lastName.arr, empRec.salary, empRec.deptNo);  
121.         }  
122.   
123.         /* 
124.         ** clean-up and exit 
125.         */  
126.         EXEC SQL CLOSE emp_cursor;  
127.   
128.         EXEC SQL COMMIT RELEASE;  
129.   
130.         return EXIT_SUCCESS;  
131. }  

Compile the program.

1. $ proc CursorExample.pc  
2.   
3. This will create CursorExample.c file  
4.   
5. Compile the CursorExample.c  
6.   
7. $ gcc -c CursorExample.c -I/$ORACLE_HOME/precomp/public/  
8.   
9. $ gcc CursorExample.o -o CursorExample -I$ORACLE_HOME/precomp/public/ -I$ORACLE_HOME/rdbms/public/ -L$ORACLE_HOME/lib/ -L$ORACLE_HOME/precomp/lib/ -lclntsh  
10.   
11. This will generate an Executable file CursorExample.  
12.   
13. Execute the process by running the following command.  
14.   
15. $ ./CursorExample  
16.   
17. Output  
18. --------  
19.   
20. EmployeeId     FirstName                     LastName                      Salary         DepartmentNo  
21. ------------------------------------------------------------------------------------------------------  
22. 100            Steven                        King                          240001         90  
23. 101            Neena                         Kochhar                       17000          90  
24. 102            Lex                           De Haan                       17000          90  
25. 103            Alexander                     Hunold                        9000           60  
26. 104            Bruce                         Ernst                         6000           60  
27. 105            David                         Austin                        4800           60  
28. 106            Valli                         Pataballa                     4800           60  
29. 107            Diana                         Lorentz                       4200           60  
30. 108            Nancy                         Greenberg                     12008          100  
31. 109            Daniel                        Faviet                        9000           100