Pro*C Command Reference

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

Set up the session for transactions.

Metadata Executables

Transaction Processing

EXEC SQL BEGIN DECLARE SECTION

Begin 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 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

SQLERROR

SQLWARNING

continue

GOTO label

STOP

DO function

DO break

DO CONTINUE

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;

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. */

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 TRUNCATE WISE_GUY_TEMPORARY_TABLE;

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;

TEMPLATE

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 transaction.
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;

EXEC SQL SET TRANSACTION

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;

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;

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 Command Reference