Host variables in Pro*C
- 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 within the C program , and it are often referenced by both our program and Oracle.
- The host variables are often placed where the SQL expressions are used, and these variables are declared between the BEGIN DECLARE SECTION and END DECLARE SECTION. Once we write the SQL statements, then the host variables are prefixed with a ':' colon.
- The following is that the list of the C data types that are supported by Oracle:
Data types
- char
- char[n]
- int
- short
- long
- float
- double
- VARCHAR[n]
Pointers
- A pointer variable also can be used as a number variable in SQL statements. Let's understand through an easy example.
int *age;
EXEC SQL SELECT age into :age from student;
Read Also
- In the above code, we declare a pointer variable of integer type, i.e., *age. After declaration, we are executing a get statement during which we are retrieving the worth aged from the scholar table, then we are storing the worth aged during a host variable, i.e., age. The result are going to be stored within the *age, not in age.
Structures
- C structures also are utilized in Pro*C. The member variables of the structure are often treated because the host variables within the host program. once we provide the name of the structure within 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've 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 worth of those two variables during a database by using the insert command.
Arrays
- Arrays are often used as a number variable in SQL embedded statements. Let's understand this through an easy example:
intstudent_number[10];
EXEC SQL INSERT INTO student(student id) VALUES(:student_number);
- In the above code, we've created a single-dimensional array of integer type. We implement the SQL INSERT command, which can insert all the ten tuples in one go.
- Let's check out the opposite example during which we use the two-dimensional array.
charstudent_name[10][7];
EXEC SQL INSERT INTO student(student nme) VALUES(:student_name);
Read Also
- In Pro*C, arrays can only be single-dimensional. But, Pro*C precompiles the above code successfully because it considers the two-dimensional as a single-dimensional array of characters rather than a two-dimensional array of characters.
short indicator_variable;
EXEC SQL SELECT column_name INTO : host_variable:indicator_variable from table_name;
- As we've shown within the above example that we will use the indicator variable during a SELECT statement to work out whether the output host variable contains the NULLs or truncated value.
- The following table shows the possible values of the indicator variable which will 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 would like to make the indicator variable of a number variable during a struct, we will simply do this by creating an indicator variable of every host variable during a struct. To add the name of an indicator variable during a SQL statement, we'd like to write down the name of the indicator variable of a structure that has got to be prefixed with a ':' colon, and must immediately follow the host variable.