Author: Martin T Wirth
This Pro*C/C++ Command Reference is a general reference to the most useful commands in Pro*C/C++. This programming reference shows every essential Pro*C command to build an application for a large enterprise but is not all inclusive. This reference is applicable from Oracle version 8 on up.
Additional and more exotic commands are used for optimization. Contact the author if you have any comments or errata to report. For further information, see the documentation that comes with your version of Oracle.
Click on category boxes to expand the tree of Pro*C/C++ commands on the left.
Why Use Pro*C/C++ ?
JavaBeans and JDBC programming is easier than using Pro*C/C++ but, there are a number of reasons why Pro*C/C++ programming could be a better choice:
To integrate Oracle with native binary applications written in C or C++
Faster loading and start-up than a Java application
Need to process an extremely high number of transactions per second
Need to process extremely large chunks of data all at once
Declarative Statements
Used to Set up the session for transactions.
EXEC SQL BEGIN DECLARE SECTION
Begin declaration of C or C++ variables to be recognized by Pro*C
EXEC SQL BEGIN DECLARES SECTION;
char* username = "database_user_example";
char* password = "database_password_example";
/* Keep the tns string on one line. This is only for illustration.*/
char* tns = "(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.2.4)(PORT = 1313)))
(CONNECT_DATA = (SID=ORACLE_EXAMPLE_SCHEMA)
(SERVICE_NAME = ORACLE_EXAMPLE_SCHEMA.miradigm.com)))";
int dbIsConnected = 0;
typedef struct Skill_s {
int skillid;
char skill[64];
} Skill_t, *Skill_p;
EXEC SQL END DECLARE SECTION;
EXEC SQL END DECLARE SECTION
End declaration of C or C++ variables to be recognized by Pro*C
EXEC SQL BEGIN DECLARE SECTION;
char* username = "database_user_example";
char* password = "database_password_example";
/* Keep the tns string on one line. This is only for illustration.*/
char* tns = "(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.2.4)(PORT = 1313)))
(CONNECT_DATA = (SID=ORACLE_EXAMPLE_SCHEMA)
(SERVICE_NAME = ORACLE_EXAMPLE_SCHEMA.miradigm.com)))";
int dbIsConnected = 0;
typedef struct Skill_s {
int skillid;
char skill[64];
} Skill_t, *Skill_p;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE
To declare a cursor, giving it a name and associating it with a SQL statement or a PL/SQL block.
EXEC SQL DECLARE wg_cursor CURSOR FOR
SELECT g.EMPID, g.SURNAME, g.FIRSTNAME, s.SKILL, w.EXPERIENCE
FROM WISE_GUYS g, WISE_GUY_SKILLS w, SKILLS s
WHERE g.EMPID = w.EMPID,
AND w.SKILLID = s.SKILLID,
AND s.SKILL = :skill,
AND w.EXPERIENCE > :minexperience;
EXEC SQL OPEN wg_cursor;
EXEC SQL INCLUDE
Include standard Oracle header files like the C #include precompiler directive.
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
EXEC SQL INCLUDE ORACA;
EXEC SQL TYPE
To perform user-defined type equivalencing, or to assign an external datatype to a whole class of host variables by equivalencing the external datatype to a user-defined datatype.
struct screen {
short len;
char buff[4002];
};
typedef struct screen graphics;
EXEC SQL TYPE graphics IS VARRAW(4002);
graphics crt; /* host variable of type graphics */
EXEC SQL VAR
To perform host variable equivalencing, or to assign a specific external datatype to an individual host variable, overriding the default datatype assignment. Also has an optional CONVBUFSZ clause that specifies the size of a buffer for character set conversion. The host variable must be previously declared in C or C++.
EXEC SQL BEGIN DECLARE SECTION;
/* ... */
char dept_name[15]; /* default datatype is CHAR */
EXEC SQL VAR dept_name IS STRING; /* reset to STRING */
/* ... */
char buffer[200]; /* default datatype is CHAR */
EXEC SQL VAR buffer IS RAW(200); /* refer to RAW */
/* ... */
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER
To specify the action to be taken when an error or warning results from executing an embedded SQL statement.
Note that the action of the WHENEVER statement is global. See example below for a common problem with this.
EXEC SQL WHENEVER NOT FOUND DO break;
/* A loop must be placed here. */
/* The WHENEVER -- continue must be located at the end of the loop
or else an ANSI C compiler will bomb when it sees a break outside a loop. */
EXEC SQL WHENEVER NOT FOUND continue;
Metadata Executables
EXEC SQL ALTER
Execute the SQL ALTER TABLE command to change column names and datatypes in a table.
/* To add columns to the WISE_GUYS table in the example */
EXEC SQL ALTER TABLE WISE_GUYS ADD (
RANK_ID NUMBER(4),
HOME_PLANET VARCHAR2(63)
);
/* After populating newly added RANK_ID in WISE_GUYS. You can modify it to NOT NULL. */
EXEC SQL ALTER TABLE WISE_GUYS MODIFY (
RANK_ID NUMBER(4) NOT NULL
);
/* After populating newly added RANK_ID in WISE_GUYS,
* you may constrain it to values in the WISE_GUY_RANK table created in the CREATE TABLE example.
* Of course, rows must be inserted into WISE_GUY_RANK first.
*/
EXEC SQL ALTER TABLE WISE_GUYS MODIFY (
RANK_ID NUMBER(4) NOT NULL REFERENCES WISE_GUY_RANK(RANK_ID)
);
EXEC SQL CREATE
Execute the SQL CREATE command to create a new table or user in the current database.
/* Create a table */
EXEC SQL CREATE TABLE WISE_GUY_RANK (
RANK_ID NUMBER(4),
RANK_SUBORDINATE_TO NUMBER(4) REFERENCES WISE_GUY_RANK(RANK_ID),
RANK_NAME VARCHAR2(31),
CONSTRAINT WISE_GUY_RANK_PK PRIMARY KEY (RANK_ID)
);
/* Create a user
* This is typically used to define a database connection for a specific application
* and limit its privileges for the sake of disaster prevention. See GRANT.
*/
EXEC SQL CREATE USER cavalier_application IDENTIFIED BY 'cavsbadpassword';
EXEC SQL DROP
Execute the SQL DROP table command to drop a table from the database.
/* Dropping a table eliminates all of the data and the table description from the database. */
EXEC SQL DROP TABLE WISE_GUY_RANK;
/* Dropping a user eliminates the user and all associated privileges from the database. */
EXEC SQL DROP USER cavalier_application
EXEC SQL GRANT
Execute the SQL GRANT command to grant permissions and identity of a user (usually a specific database connection).
/* Allow cavalier_application to connect, view data, insert rows, and update
* without messing around with metadata or doing a massive and potentially disastrous delete.
*/
EXEC SQL GRANT CONNECT, SELECT, UPDATE, INSERT TO cavalier_application;
/* Of course, if you can trust cavalier_application to practice good design
* and behave well:
*/
EXEC SQL GRANT ANY PRIVILEGE TO cavalier_application;
EXEC SQL RENAME
Execute the SQL RENAME command to rename a table, view, or synonym.
/* suppose you named a table, OFFICERS, and
* Later realized this violated naming standards because it is ambiguous.
* Rename it to reflect its relationships using:
*/
EXEC SQL RENAME OFFICERS WISE_GUY_RANK;
EXEC SQL REVOKE
Execute the SQL REVOKE command to revoke permissions from a user.
/* Upon decommissioning cavalier_application, its privileges must be revoked.
* This keeps the user listed in case there's a future cavalier_application in the works.
* To eliminate the user entirely, use DROP.
*/
EXEC SQL REVOKE CONNECT, SELECT, UPDATE, INSERT FROM cavalier_application;
/* or if you granted ANY PRIVELEGE and want to revoke DELETE and ALTER: */
EXEC SQL REVOKE DELETE, ALTER FROM cavalier_application;
/* or eliminate all privileges without eliminating the user from the database: */
EXEC SQL REVOKE ANY PRIVILEGE FROM cavalier_application;
/* Use DROP if you want to eliminate the user from the database. */
Data Manipulation
EXEC SQL CLOSE
To disable a cursor, freeing the resources acquired by opening the cursor, and releasing parse locks.
Failure to close cursors after use tends to shutdown production when the database hits the MAXCURSORS limit.
wiseguy = (WiseGuys_t*)calloc(row_count, sizeof(WiseGuys_t));
EXEC SQL DECLARE wg_cursor CURSOR FOR
SELECT g.EMPID, g.SURNAME, g.FIRSTNAME, s.SKILL, w.EXPERIENCE
FROM WISE_GUYS g, WISE_GUY_SKILLS w, SKILLS s
WHERE g.EMPID = w.EMPID,
AND w.SKILLID = s.SKILLID,
AND s.SKILL = :skill,
AND w.EXPERIENCE > :minexperience;
EXEC SQL OPEN wg_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;
igrow = -1;
empidprev = -1;
while (igrow < cnt) { EXEC SQL FETCH wg_cursor INTO :empid, :surname INDICATOR :surname_indicator, :firstname INDICATOR :firstname_indicator, :experience, :proc_experience; if (empid != empidprev) { igrow++; wiseguy[igrow].empid = empid; wiseguy[igrow].skillcnt = 0 if (surname_indicator == 0) { surname.arr[surname.len] = '\0'; strcpy(wiseguy[irow].surname, surname.arr); } else { strcpy(wiseguy[irow].surname, ""); } if (firstname_indicator == 0) { firstname.arr[surname.len] = '\0'; strcpy(wiseguy[irow].firstname, firstname.arr); } else { strcpy(wiseguy[irow].firstname, ""); } empidprev = empid; } if (igrow >= 0) {
wiseguy[igrow].wiseguyskill[wiseguy[igrow].skillcnt].experience = experience;
skill.arr[skill.len] = '\0';
strcpy(wiseguy[igrow].wiseguyskill[wiseguy[igrow].skillcnt].skill, skill);
wiseguy[igrow].skillcnt++;
}
}
EXEC SQL WHENEVER NOT FOUND continue;
/* Avoid gnashing of teeth by closing the cursor. */
EXEC SQL CLOSE wg_cursor;
EXEC SQL DELETE
To remove rows from a table or from a view's base table.
EXEC SQL DELETE FROM emp
WHERE deptno = :deptno
AND job = :job;
EXEC SQL DECLARE emp_cursor CURSOR
FOR SELECT empno, comm
FROM emp;
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH c1
INTO :emp_number, :commission;
EXEC SQL DELETE FROM emp
WHERE CURRENT OF emp_cursor;
EXEC SQL FETCH
In Oracle dynamic SQL, to retrieve one or more rows returned by a query, assigning the select list values to host variables.
wiseguy = (WiseGuys_t*)calloc(row_count, sizeof(WiseGuys_t));
EXEC SQL DECLARE wg_cursor CURSOR FOR
SELECT g.EMPID, g.SURNAME, g.FIRSTNAME, s.SKILL, w.EXPERIENCE
FROM WISE_GUYS g, WISE_GUY_SKILLS w, SKILLS s
WHERE g.EMPID = w.EMPID,
AND w.SKILLID = s.SKILLID,
AND s.SKILL = :skill,
AND w.EXPERIENCE > :minexperience;
EXEC SQL OPEN wg_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;
igrow = -1;
empidprev = -1;
while (igrow < cnt) { EXEC SQL FETCH wg_cursor INTO :empid, :surname INDICATOR :surname_indicator, :firstname INDICATOR :firstname_indicator, :experience, :proc_experience; if (empid != empidprev) { igrow++; wiseguy[igrow].empid = empid; wiseguy[igrow].skillcnt = 0 if (surname_indicator == 0) { surname.arr[surname.len] = '\0'; strcpy(wiseguy[irow].surname, surname.arr); } else { strcpy(wiseguy[irow].surname, ""); } if (firstname_indicator == 0) { firstname.arr[surname.len] = '\0'; strcpy(wiseguy[irow].firstname, firstname.arr); } else { strcpy(wiseguy[irow].firstname, ""); } empidprev = empid; } if (igrow >= 0) {
wiseguy[igrow].wiseguyskill[wiseguy[igrow].skillcnt].experience = experience;
skill.arr[skill.len] = '\0';
strcpy(wiseguy[igrow].wiseguyskill[wiseguy[igrow].skillcnt].skill, skill);
wiseguy[igrow].skillcnt++;
}
}
EXEC SQL WHENEVER NOT FOUND continue;
EXEC SQL CLOSE wg_cursor;
EXEC SQL INSERT
To add rows to a table or to a view's base table.
int empid;
VARCHAR surname;
VARCHAR firstname;
strcpy(surname.arr, wg.surname);
surname.len = (short*)strlen(wg.surname);
strcpy(firstname.arr, wg.firstname);
firstname.len = (short*)strlen(wg.firstname);
EXEC SQL SELECT EMPID.NEXTVAL INTO :empid FROM DUAL;
EXEC SQL INSERT INTO WiseGuys ((empid,surname,firstname)VALUES (:empid, :surname, :firstname);
EXEC SQL COMMIT;
EXEC SQL LOCK TABLE
Lock one or more tables in a specified lock mode. For example, the statement in the following section, locks the EMP table in row share mode. Row share locks allow concurrent access to a table; they prevent other users from locking the entire table for exclusive use. Locks are released when the current transaction executes a COMMIT or ROLLBACK.
EXEC SQL LOCK TABLE WISE_GUYS IN ROW SHARE MODE NOWAIT;
EXEC SQL OPEN
To open a cursor, evaluating the associated query and substituting the host variable names supplied by the USING clause into the WHERE clause of the query.
wiseguy = (WiseGuys_t*)calloc(row_count, sizeof(WiseGuys_t));
EXEC SQL DECLARE wg_cursor CURSOR FOR
SELECT g.EMPID, g.SURNAME, g.FIRSTNAME, s.SKILL, w.EXPERIENCE
FROM WISE_GUYS g, WISE_GUY_SKILLS w, SKILLS s
WHERE g.EMPID = w.EMPID,
AND w.SKILLID = s.SKILLID,
AND s.SKILL = :skill,
AND w.EXPERIENCE > :minexperience;
EXEC SQL OPEN wg_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;
igrow = -1;
empidprev = -1;
while (igrow < cnt) { EXEC SQL FETCH wg_cursor INTO :empid, :surname INDICATOR :surname_indicator, :firstname INDICATOR :firstname_indicator, :experience, :proc_experience; if (empid != empidprev) { igrow++; wiseguy[igrow].empid = empid; wiseguy[igrow].skillcnt = 0 if (surname_indicator == 0) { surname.arr[surname.len] = '\0'; strcpy(wiseguy[irow].surname, surname.arr); } else { strcpy(wiseguy[irow].surname, ""); } if (firstname_indicator == 0) { firstname.arr[surname.len] = '\0'; strcpy(wiseguy[irow].firstname, firstname.arr); } else { strcpy(wiseguy[irow].firstname, ""); } empidprev = empid; } if (igrow >= 0) {
wiseguy[igrow].wiseguyskill[wiseguy[igrow].skillcnt].experience = experience;
skill.arr[skill.len] = '\0';
strcpy(wiseguy[igrow].wiseguyskill[wiseguy[igrow].skillcnt].skill, skill);
wiseguy[igrow].skillcnt++;
}
}
EXEC SQL WHENEVER NOT FOUND continue;
EXEC SQL CLOSE wg_cursor;
EXEC SQL SELECT
To retrieve data from one or more tables, views, or snapshots, assigning the selected values to host variables.
EXEC SQL SELECT COUNT(ID) INTO :row_count
FROM WISE_GUYS
WHERE g.EMPID = w.EMPID,
AND w.SKILLID = s.SKILLID,
AND s.SKILL = :skill,
AND w.EXPERIENCE > :minexperience;
EXEC SQL TRUNCATE
To permanently delete all rows from a table without the possibility of ROLLBACK while leaving the table description in place.
This is a dangerous command that can blow away a lot of data. Use with caution preferably only on temporary tables.
EXEC SQL UPDATE
To change existing values in a table or in a view's base table.
int empid;
VARCHAR surname;
VARCHAR firstname;
strcpy(surname.arr, wg.surname);
surname.len = (short*)strlen(wg.surname);
strcpy(firstname.arr, wg.firstname);
firstname.len = (short*)strlen(wg.firstname);
EXEC SQL UPDATE WISE_GUYS SET
SURNAME = :surname,
FIRSTNAME = :firstname
WHERE empid =:wg.empid
EXEC SQL COMMIT;
Transaction Control
EXEC SQL COMMIT
To end your current transaction, making permanent all its changes to the database and optionally freeing all resources and disconnecting.
/* To merely commit the data without ending the session: */
EXEC SQL COMMIT;
/* To commit, end the session, and free all resources: */
EXEC SQL COMMIT WORK RELEASE;
EXEC SQL ROLLBACK
To undo work done in the current transactio
EXEC SQL ROLLBACK;
EXEC SQL ROLLBACK TO SAVEPOINT sp5;
EXEC SQL SAVEPOINT
To identify a point in a transaction to which you can later roll back.
EXEC SQL SAVEPOINT sp5;
Dynamic SQL
EXEC SQL DESCRIBE
To populate an Oracle descriptor with information about a dynamic SQL statement or PL/SQL block.
EXEC SQL PREPARE my_statement FROM :my_string;
EXEC SQL DECLARE emp_cursor FOR
SELECT empno, ename, sal, comm
FROM emp
WHERE deptno = :dept_number;
EXEC SQL DESCRIBE BIND VARIABLES FOR my_statement
INTO bind_descriptor;
EXEC SQL OPEN emp_cursor
USING bind_descriptor;
EXEC SQL DESCRIBE SELECT LIST FOR my_statement
INTO select_descriptor;
EXEC SQL FETCH emp_cursor
INTO select_descriptor;
EXEC SQL EXECUTE
In Oracle dynamic SQL, to execute a DELETE, INSERT, or UPDATE statement or a PL/SQL block that has been previously prepared with an embedded SQL PREPARE statement
EXEC SQL PREPARE my_statement
FROM :my_string;
EXEC SQL EXECUTE my_statement
USING :my_var;
EXEC SQL PREPARE
To parse a SQL statement or PL/SQL block specified by a host variable and associate it with an identifier.
EXEC SQL PREPARE my_statement
FROM :my_string;
EXEC SQL EXECUTE my_statement
USING :my_var;
Session Control
EXEC SQL ALTER SESSION
To change session attributes, do concurrent transactions with more than one schema at once, and close connections with the additional schema.
/* To do a distributed transaction with another (remote) database*/
EXEC SQL ALTER SESSION ADVISE COMMIT;
EXEC SQL INSERT INTO employees@remote
VALUES (8002, 'Juan', 'Fernandez', 'juanf@hr.com', NULL,
TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 'SA_CLERK', 3000,
NULL, 121, 20);
EXEC SQL ALTER SESSION ADVISE ROLLBACK;
EXEC SQL DELETE FROM employees@local WHERE employee_id = 8002;
EXEC SQL COMMIT;
/* To close a database link */
EXEC SQL UPDATE jobs@remote SET min_salary = 3000 WHERE job_id = 'SH_CLERK';
EXEC SQL COMMIT;
EXEC SQL ALTER SESSION CLOSE DATABASE LINK remote;
/* To change the date format */
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
/* To change the ISO currency symbol to the ISO currency symbol for the territory America */
EXEC SQL ALTER SESSION SET NLS_ISO_CURRENCY = America;
EXEC SQL SELECT TO_CHAR( SUM(salary), 'C 999G999D99') Total INTO :total FROM employees;
printf("Total is: %s\n", total);
/* Total is: USD 694,900.00 */
/* Here's a function that changes the language */
void dbFrenchSelect() {
EXEC SQL ALTER SESSION SET NLS_LANGUAGE = FRENCH;
/* Session modifiee. */
EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error: dbFrenchSelect()", ONERR_EXIT);
EXEC SQL SELECT EMP_ID FROM BOGUS_NONEXISTENT_TABLE;
/* Morticia, that error message is French!
ORA-00942: Table ou vue inexistante
*/
}
EXEC SQL CONNECT
To log on to a database.
EXEC SQL BEGIN DECLARE SECTION;
char* username = "database_user_example";
char* password = "database_password_example";
/* Keep the tns string on one line. This is only for illustration.*/
char* tns = "(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.2.4)(PORT = 1313)))
(CONNECT_DATA = (SID=ORACLE_EXAMPLE_SCHEMA)
(SERVICE_NAME = ORACLE_EXAMPLE_SCHEMA.miradigm.com)))";
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :tns;
Embedded PL/SQL
EXEC SQL ALLOCATE
To allocate a cursor variable to be referenced in a PL/SQL block, or to allocate space in the object cache.
EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR emp_cv;
struct{ ... } emp_rec;
EXEC SQL END DECLARE SECTION;
EXEC SQL ALLOCATE :emp_cv;
EXEC SQL EXECUTE
BEGIN
OPEN :emp_cv FOR SELECT * FROM emp;
END;
END-EXEC;
for (;;) {
EXEC SQL FETCH :emp_cv INTO :emp_rec;
/*...*/
}
EXEC SQL ARRAYLEN
To pass an input host array to a PL/SQL block. Pro*C/C++ Precompiler uses the declared dimension of the host array, which can be very large. When processing the entire array is not necessary, use ARRAYLEN to limit cycles.
The ARRAYLEN statement must appear after the declarations of the host array and the dimension.
float bonus[100];
int dimension;
EXEC SQL ARRAYLEN bonus (dimension);
/* populate the host array */
/*...*/
dimension = 25; /* set smaller array dimension */
EXEC SQL EXECUTE
DECLARE
TYPE NumTabTyp IS TABLE OF REAL
INDEX BY BINARY_INTEGER;
median_bonus REAL;
FUNCTION median (num_tab NumTabTyp, n INTEGER)
RETURN REAL IS
BEGIN
-- compute median
END;
BEGIN
median_bonus := median(:bonus, :dimension);
--...
END;
END-EXEC
EXEC SQL CALL
Call a stored procedure.
/* Suppose we create a stored procedure such as: */
EXEC SQL CREATE OR REPLACE PACKAGE BODY mathpkg AS
FUNCTION fact(n IN INTEGER) RETURN INTEGER AS
BEGIN
IF (n <= 0) THEN RETURN 1; ELSE RETURN n * fact(n - 1); END IF; END fact; END mathpkg; END-EXEC. /* To use this in a Pro*C/C++ application via the CALL statement */ int num, fact; EXEC SQL CALL mathpkg.fact(:num) INTO :fact; /* The stored procedure does not have to be created using Pro*C. This works just fine with pre-existing stored procedures created with other tools such as Oracle SQL*Plus or SQL Developer */ EXEC SQL EXECUTE To embed an anonymous PL/SQL block in a Pro*C/C++ program. EXEC SQL EXECUTE /* Start the anonymous PL/SQL block after this */ BEGIN SELECT ename, job, sal INTO :emp_name:ind_name, :job_title, :salary FROM emp WHERE empno = :emp_number; IF :emp_name:ind_name IS NULL THEN RAISE name_missing; END IF; END; END-EXEC; END-EXEC To designate the end of an embedded anonymous PL/SQL block in a Pro*C/C++ program. EXEC SQL EXECUTE BEGIN SELECT ename, job, sal INTO :emp_name:ind_name, :job_title, :salary FROM emp WHERE empno = :emp_number; IF :emp_name:ind_name IS NULL THEN RAISE name_missing; END IF; END; END-EXEC; /* Mark the end of the anonymous PL/SQL block with this */ Pro*C Quick Start Tutorial and Example All Pro*C sessions follow these basic steps: Declare data receptacles for the native C or C++ instance. Connect to the database. Process Transactions: Select: Declare a cursor to perform the query Open the cursor Fetch rows Close the cursor Insert: Get the next key value Insert Commit Update: Update Commit End the database connection Eaxmple - Tables WISE_GUYS Column EMPID SURNAME FIRSTNAME Type NUMBER VARCHAR2(63) VARCHAR2(31) 1 Wirth Martin 2 Kirk James 3 Spock 4 Jones Tom 5 Weston Sophie 6 Mukerjee Raj 7 Uhura Nyota 8 Troi Deanna 9 Scott Montgomery 10 Rand Janice Example Database 3rd Normal Form WISE_GUY_SKILLS Column EMPID SKILLID EXPERIENCE Type NUMBER NUMBER DECIMAL(4,1) 1 1 15.0 1 2 15.0 1 3 9.0 1 4 9.0 1 9 21.0 3 1 2.2 3 2 1.3 3 3 5.7 3 4 11.6 2 9 10.1 4 5 6.0 5 5 12.0 6 5 4.9 7 5 2.0 7 7 7.2 7 8 5.4 8 6 22.0 9 9 25.0 10 5 6.0 10 9 4.0 SKILLS Column SKILLID SKILL Type NUMBER VARCHAR2(63) 1 C 2 C++ 3 Pro*C 4 Java 5 Administration 6 Psychology 7 Linguistics 8 Communications 9 Engineering #include
#include "log.h"; /* Where log(char*) would be located to log activities and errors. */
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
EXEC SQL INCLUDE ORACA;
extern void sqlglm();
EXEC SQL BEGIN DECLARE SECTION;
char* username = "database_user_example";
char* password = "database_password_example";
/* Keep the tns string on one line. This is only for illustration.*/
char* tns = "(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.2.4)(PORT = 1313)))
(CONNECT_DATA = (SID=ORACLE_EXAMPLE_SCHEMA)
(SERVICE_NAME = ORACLE_EXAMPLE_SCHEMA.miradigm.com)))";
int dbIsConnected = 0;
typedef struct Skill_s {
int skillid;
char skill[64];
} Skill_t, *Skill_p;
typedef struct WiseGuySkill_s {
int empid;
int skillid;
char skill[64];
float experience;
} WiseGuySkill_t, *WiseGuySkill_p;
typedef struct WiseGuy_s {
int empid;
char surname[64];
char firstname[32];
int skillcnt;
WiseGuySkill_t wiseguyskill[32];
} WiseGuy_t, *WiseGuy_p;
WiseGuys_t* wiseguy;
EXEC SQL END DECLARE SECTION;
void sql_error(const char* errmsg, int handling) {
FILE* fd;
char msg[255];
char logmsg[4096];
char logbuf[256];
char stmt[4096];
size_t stmtlen;
size_t fclen;
size_t buflen,msglen;
buflen = sizeof(msg);
sqlglm(msg, &buflen, &msglen);
memset(logmsg, '\0', 4096);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
if (strncmp(errmsg, "Oracle Error:",12) == 0) sprintf(logbuf, "%s\n", errmsg);
strcat(logmsg, logbuf);
sprintf(logbuf, "%.*s\n", msglen, msg);
strcat(logmsg, logbuf);
sprintf(logbuf, "At or near line number %d:\n", oraca.oraslnr);
strcat(logmsg, logbuf);
sprintf(logbuf, "%s\n",oraca.orastxt.orastxtc);
strcat(logmsg, logbuf);
sprintf(logbuf, "Cache-------------------------------------------------------------\n");
strcat(logmsg, logbuf);
sprintf(logbuf, "Cursors: open = %d required=%d MAXOPENCURSORS=%d\n",
oraca.oracoc, oraca.oramoc, oraca.orahoc);
strcat(logmsg, logbuf);
sprintf(logbuf, "Cache Reassignments = %d\n", oraca.oranor);
strcat(logmsg, logbuf);
sprintf(logbuf, "SQL Parses = %d\n", oraca.oranpr);
strcat(logmsg, logbuf);
sprintf(logbuf, "SQL Executions = %d\n", oraca.oranex);
strcat(logmsg, logbuf);
log(logmsg);
exit(1);
}
void dbSelect(float minexperience, const char* skillname) {
int cnt;
int igrow;
int iskillrow;
int empid;
int empidprev;
VARCHAR surname[64];
VARCHAR firstname[32];
VARCHAR skill[64];
float experience;
short surname_indicator;
short firstname_indicator;
EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error: dbSelect()", ONERR_EXIT);
skill.len = (short*)strlen(skillname);
strcpy(skill.arr, skillname);
EXEC SQL SELECT COUNT(ID) INTO :cnt
FROM WISE_GUYS
WHERE g.EMPID = w.EMPID,
AND w.SKILLID = s.SKILLID,
AND s.SKILL = :skill,
AND w.EXPERIENCE > :minexperience;
cnt++;
wiseguy = (WiseGuys_t*)calloc(cnt, sizeof(WiseGuys_t));
EXEC SQL DECLARE wg_cursor CURSOR FOR
SELECT g.EMPID, g.SURNAME, g.FIRSTNAME, s.SKILL, w.EXPERIENCE
FROM WISE_GUYS g, WISE_GUY_SKILLS w, SKILLS s
WHERE g.EMPID = w.EMPID,
AND w.SKILLID = s.SKILLID,
AND s.SKILL = :skill,
AND w.EXPERIENCE > :minexperience;
EXEC SQL OPEN wg_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;
igrow = -1;
empidprev = -1;
while (igrow < cnt) { EXEC SQL FETCH wg_cursor INTO :empid, :surname INDICATOR :surname_indicator, :firstname INDICATOR :firstname_indicator, :experience, :proc_experience; if (empid != empidprev) { igrow++; wiseguy[igrow].empid = empid; wiseguy[igrow].skillcnt = 0 if (surname_indicator == 0) { surname.arr[surname.len] = '\0'; strcpy(wiseguy[irow].surname, surname.arr); } else { strcpy(wiseguy[irow].surname, ""); } if (firstname_indicator == 0) { firstname.arr[surname.len] = '\0'; strcpy(wiseguy[irow].firstname, firstname.arr); } else { strcpy(wiseguy[irow].firstname, ""); } empidprev = empid; } if (igrow >= 0) {
wiseguy[igrow].wiseguyskill[wiseguy[igrow].skillcnt].experience = experience;
skill.arr[skill.len] = '\0';
strcpy(wiseguy[igrow].wiseguyskill[wiseguy[igrow].skillcnt].skill, skill);
wiseguy[igrow].skillcnt++;
}
}
EXEC SQL WHENEVER NOT FOUND continue;
EXEC SQL CLOSE wg_cursor;
}
void dbInsert(WiseGuy_t wg) {
int empid;
VARCHAR surname;
VARCHAR firstname;
EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error: dbInsert()", ONERR_EXIT);
strcpy(surname.arr, wg.surname);
surname.len = (short*)strlen(wg.surname);
strcpy(firstname.arr, wg.firstname);
firstname.len = (short*)strlen(wg.firstname);
EXEC SQL SELECT EMPID.NEXTVAL INTO :empid FROM DUAL;
EXEC SQL INSERT INTO WiseGuys ((empid,surname,firstname)VALUES (:empid, :surname, :firstname);
EXEC SQL COMMIT;
}
void dbUpdate(WiseGuy_t wg) {
int empid;
VARCHAR surname;
VARCHAR firstname;
EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error: dbUpdate()", ONERR_EXIT);
strcpy(surname.arr, wg.surname);
surname.len = (short*)strlen(wg.surname);
strcpy(firstname.arr, wg.firstname);
firstname.len = (short*)strlen(wg.firstname);
EXEC SQL UPDATE WISE_GUYS SET
SURNAME = :surname,
FIRSTNAME = :firstname
WHERE empid =:wg.empid
EXEC SQL COMMIT;
}
void dbConnectError() {
char msg[255];
char stmt[4096];
size_t stmtlen;
size_t fclen;
size_t buflen,msglen;
buflen = sizeof(msg);
sqlglm(msg, &buflen, &msglen);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
log("Database Connect Failure: %s\n", msg);
}
void dbConnect() {
EXEC ORACLE OPTION (ORACA=YES);
oraca.orastxtf = ORASTFERR;
EXEC SQL WHENEVER SQLERROR DO dbConnectError();
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :tns;
}
void dbConnectEnd() {
EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error: dbConnectEnd()", ONERR_EXIT);
EXEC SQL COMMIT WORK RELEASE;;
}
void main(int argc, char** argv) {
dbConnect();
dbSelect(5.0, "Pro*C");
dbConnectEnd();
}
No comments:
Post a Comment