Pro*C tutorial

In this tutorial, we are going to cover the following topics:

What is Pro*C
Compiling the Pro*C program
Need of Pro*C compiler
Requirements
Directory Structure
Restrictions
Embedded SQL statements
Pro*C Syntax
Host Variables
Datatype Equivalencing
Dynamic vs Static SQL statements
Transactions
Error Handling
Program

What is Pro*C?

Pro*C means creating a program that is embedded with SQL statements. We can access the Oracle database with the help of Pro*C. The C programming language provides the flexibility for data processing by manipulating or retrieving the data from the Oracle database. Therefore, we can say that Pro*C allows C programming language to connect the C program with the Oracle database and do the manipulations according to our requirements.

How can we compile the Pro*C program?

Oracle software provides the PROC compiler (Oracle Precompiler), and this compiler takes the C program having embedded SQL statements.

What is Oracle precompiler?

An oracle precompiler is a programming tool that allows the user to embed the SQL statements in a high-level source program. This compiler takes the source program as input, replacing the embedded SQL statements with the oracle runtime library calls, and this modified program can now compile, link, and execute.

The whole process of compilation of Pro*C program is given below:

First, we create a C program, which contains the SQL statements, and then we save this file with an extension ‘.PC’. Here, ‘.PC’ means that the program is a Pro*C program.
After creating a program, we compile the program by using the PROC compiler, which is provided by the Oracle. The PROC compiler generates .c file with all the SQL statements replaced by the functions which are already pre-defined in the Oracle runtime library.
The file created by the PROC compiler will be compiled again by the C compiler, which is supported by Pro*C. In Windows, the PROC compiler supports Microsoft Visual C++ compiler.
The C compiler will create a .exe file.
Now, finally, we run the .exe file.

Why do we need the Pro*C compiler?

The Pro*C compiler allows you to embed the SQL statements in the C program, and it also provides the required user interface.
Unlike many other development tools, the Pro*C allows you to customize applications. It creates user interfaces that incorporate the latest windowing and mouse technology. Sometimes it is possible that we cannot generate the reports from other development tools, but we can achieve this by retrieving the data from the oracle database.

Requirements

The main requirement of Pro*C is to install the Pro*C software. When we are installing the Oracle database, then we have to make sure that we have selected the Pro*C components. If we have to check whether the installation of oracle contains the Pro*C components is by checking the PRECOMP directory of Oracle.

Directory Structure

When we install the Oracle, a directory structure is created on our hard drive for the Oracle products. The main oracle directory will contain the subdirectories and files which are required for the Pro*C.

When we install the Pro*C in Oracle, then Oracle Universal installer creates a directory known as precomp in the ORACLE_BASEORACLE_HOME directory. This subdirectory, i.e., precomp contains the executable files, library files, and some sample programs which is given below:

precomp Directory Structure

Directory	Description
admin	It contains the configuration files.
demoproc	It contains the sample programs for Pro*C.
demosql	It contains sql scripts for sample programs.
docproc	It contains the documentation files for pro*c.
helpproc	It contains help files for Pro*C.
libmsvc	It contains library files for Pro*C
mesg	It contains message files.
miscproc	It contains miscellaneous files for Pro*C.
public	It contains the header files having .h extension.

Restrictions

All the Windows operating systems can contain the spaces in the files and directory names, but Oracle Pro*C precompiler does not precompile the files, which include space in the file name.

For example, if the file name is the first program.pc, then this file name will be invalid.

Embedded SQL statements

Here, embedded SQL means placing the SQL statements inside the source program. As we house the statements inside the C program, so C program is also known as the host program, and the language we use is known as the host language. The Pro*C provides the ability to embed the SQL statements inside the program.

Embedded SQL statements are of two types:

Executable statements
Directives

Executable statements

Executable statements are the SQL statements that allow you to manipulate the data in the Oracle database. These statements call the Oracle runtime library. It also allows your program to connect to the Oracle database, to define the query, to manipulate the data, and process the transactions. These statements are written where C executable statements can be placed.

Directives

Directives or declarative statements are the SQL statements that neither call the Oracle runtime libraries nor operate on the Oracle data. It is used to declare the Oracle objects, SQL objects. These statements can be written where the C variables can be declared.

Pro*C Syntax

In the C program, all the SQL statements must start with EXEC SQL, and should end with the semicolon ;.We can write the SQL statement anywhere in the program but with one restriction that declarative statements should not come after the executable statements.

Suppose we want to retrieve the student marks from the database based on their id, then its program would be written as:

  {   int marks;  EXEC SQL select marks INTO : marks from student where student_id=6;  printf(“The marks of the student is : %d”, marks);  }  

Preprocessor directives

The preprocessor directives that we can use when we are dealing with Pro*C in C are #include and #if. But, Pro*C does not know about the #define directive. Let’s understand this through a simple scenario which is given below:

  #define employee_id  int salary;  EXEC SQL select salary into : salary from employee where employee_id=5;  

The above code is invalid, as Pro*C does not work with a #define directive.

Host Variables

Host variables are the variables of the host language that are used with the SQL embedded statements. Host variables are a key of communication between the Oracle and C program. These variables are declared similarly as we make the declaration in the C program, and it can be referenced by both our program and Oracle.

The host variables can be placed where the SQL expressions are used, and these variables are declared between the BEGIN DECLARE SECTION and END DECLARE SECTION. When we write the SQL statements, then the host variables are prefixed with a ‘:’ colon.

The following is the list of the C data types that are supported by Oracle:

char
char[n]
int
short
long
float
double
VARCHAR[n]

Pointers

A pointer variable can also be used as a host variable in SQL statements. Let’s understand through a simple example.

In the above code, we declare a pointer variable of integer type, i.e., *age. After declaration, we are executing a SELECT statement in which we are retrieving the value of age from the student table, and then we are storing the value of age in a host variable, i.e., age. The result will be stored in the *age, not in age.

Structures

C structures are also used in Pro*C. The member variables of the structure can be treated as the host variables in the host program. When we provide the name of the structure in the SQL statement, then each host variable must be prefixed with a: colon.

  struct student  {  int student_id;  char name[20];   }s1;  EXEC SQL INSERT INTO stud(student id, name) values(:s1);  

In the above code, we have created a structure named as a student, which contains two variables, i.e., student_id and name. After creating structure, we declare the variable, i.e., s1 of type student. Then, we insert the value of these two variables in a database by using the insert command.

Arrays

Arrays can be used as a host variable in SQL embedded statements. Let’s understand this through a simple example:

  int student_number[10];  EXEC SQL INSERT INTO student(student id) VALUES(:student_number);  

In the above code, we have created a single-dimensional array of integer type. We implement the SQL INSERT command, which will insert all the 10 tuples in one go.

Let’s look at the other example in which we use the two-dimensional array.

  char student_name[10][7];  EXEC SQL INSERT INTO student(student nme) VALUES(:student_name);  

In Pro*C, arrays can only be single-dimensional. But, Pro*C precompiles the above code successfully as it considers the two-dimensional as a single-dimensional array of characters instead of a two-dimensional array of characters.

  short indicator_variable;  EXEC SQL SELECT column_name INTO : host_variable:indicator_variable from table_name;  

As we have shown in the above example that we can use the indicator variable in a SELECT statement to determine whether the output host variable contains the NULLs or truncated value. The following table shows the possible values of the indicator variable that can be given by the Oracle with its description:

Value returned by Oracle	Description
-1	It means that the value of column is NULL, then the value of the host variable will be indeterminate.
0	An intact column value is assigned to the host variable.
>0	Truncated value is assigned to the host variable.
-2	Truncated value is assigned to the host variable.

If we want to create the indicator variable of a host variable in a struct, we can simply do that by creating an indicator variable of each host variable in a struct. To add the name of an indicator variable in a SQL statement, we need to write the name of the indicator variable of a structure that must be prefixed with a ‘:’ colon, and must immediately follow the host variable.

Datatype Equivalencing

It is a very important feature as it adds flexibility to your application. It means that you can customize according to the requirements based on how Oracle interprets the input data and formats the output data.

Oracle contains two types of data types:

Internal datatypes
External datatypes

internal datatype: These data types are used by Oracle to define how data is going to be stored in a column.

External datatype: These data types are used by Oracle to format the output data, and then this output data will be stored in a host variable.

Let’s see how we can equivalence the data type.

On a variable-by-variable basis, we use the var statement for equivalencing. The syntax of the var statement is given below:

For example, we want to retrieve the student names from the student table; then, we need to pass these student names to the function that accepts the C style strings (the last character must be a termination character ‘

Pro*C Tutorial