Oracle Pro*c Download

[Melissa Alvarado]

ProC 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.

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

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_BASE\ORACLE_HOME directory. This subdirectory, i.e., precomp contains the executable files, library files, and some sample programs which is given below:

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.

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

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.

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.

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.

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.

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.

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.

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:

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.

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.

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 '\0'). To equivalence the host variable with a String external datatype, we use the following code:

The column, i.e., student name, contains 11 characters. As the column student name contains the 11 characters, so we need to allot the 12 characters (11 characters of student name plus terminator character (\0)). We use the STRING data type that provides the interface with the C style strings. Oracle will automatically add the '\0' character.

Till now, we equivalence the built-in data types, i.e., in the above example, we equivalence the char array to the Oracle external datatype (String). We can also equivalence the user-defined data types by using the TYPE command. The syntax of the type statement is given below:

Mainly, static SQL statements are used for fixed applications, but sometimes it is required for a program to create the SQL statements dynamically. To create the dynamic SQL statement, first, we need to store the SQL statement in a string variable. After storing the statement, we use the PREPARE statement to convert the character string into a SQL statement. Finally, we execute the statement by using the EXECUTE statement. Let's understand this scenario through an example.

Oracle Pro*C also follows the concept of the transaction as defined by the SQL standard. A transaction is a series of statements that Oracle uses to either made all the changes permanent or undo all the changes done since the transaction began. We use two statements, i.e., EXEC SQL COMMIT and EXEC SQL ROLLBACK. The EXEC SQL COMMIT statement is used to make all the changes permanent since the transaction began. The EXEC SQL ROLLBACK is used to undo all the changes since the transaction began. If we start the next transaction without writing the statement EXEC SQL COMMIT, then all the changes made during this transaction will be discarded.

C programming provides the in-built error handling mechanism which we use in our source program. Error handling is a mechanism that provides the status of our source program. We need some mechanism that handles the error, so Pro*C contains the two error handling concepts which are given below:

SQLCA (SQL Communication Area) is a data structure or an area used by our program to check for errors. This data structure contains some pre-defined variables used by Oracle. These variables contain the status information of the program, which is passed at the runtime.

When the Whenever statement is executed, then the Oracle will automatically check the SQLCA for the condition mentioned in the whenever statement. If such condition is found in the sqlca, then the action given in the whenever statement will be performed.

In the above screen, we can see that the workspace of our project has been created. At the leftmost side, we can see the hierarchy of our project which contains External Dependencies, Header Files, Resource files, and Source files.

This can be achieved by using the OCI library, but it look like Oracle

have an error in there client code, if you plan to use dynamic date and

"host arrays" (DynamicBind). I have made a JDBC/ODBC like interface for

OCI and C++, and this interface have gained over 1000/s while inserting

rather small records, but its not stable during the Oracle error (wild

pointer), hope this will be fixed in the near future (I have send this

error to Oracle id DK, but only time will show have fast this will be

fixed).

We are using 8.1.5 Oracle ODBC driver and just today we noticed a

problem. We have a multi-threaded NT application where each thread

basically opens a connection to the database and calls a stored

procedure.We noticed that all these threads are getting serialized on

SQLExecDirect() call. Is Oracle ODBC multi-threaded? We were

wondering if we have to set some connection attributes.OCI documentation claims (though we have not tried it yet) that it

supports multi-threading and Oracle ODBC is supposed to have been

implemented using OCI.Could you please shed some light on the matter? We are eager to know

what is the solution to the problem.Any help would be really appreciated.Thanks a lot for your time.regards,

premal

mumbai,

indiaIn article ,

kh...@home.com wrote:

> Folks, I am building a very high performance

> distributed application for the internet. I have a

> minimum requirement of at least 400 transaction/s (profiled on

> low end solaris box).

>

> What are the pros and cons of using ODBC vs pro*c or OCI ?

> I am interested in performance issues/experiences using

> ODBC. How is connection pooling in Oracle ODBC driver ?

> Are there any gotchas with ODBC for high performance

> solutions ?

>

> Can I use Oracle Advanced Queuing with Oracle ODBC ??

>

> Any ideas, suggestions appreciated. I can be mailed directly

> also.

>

> Thanks in advance.

>

> Regards.

> Furkan Khan

>

>

3a8082e126