Pro*c Datatype | Pro*c Datatype Equivalencing
- It is a really important feature because it adds flexibility to your application.
- It means you'll customize consistent with the wants supported how Oracle interprets the input file and formats the output data.
Oracle contains two sorts of data types:
DataTypes
- Internal datatypes
- External datatypes
- Internal Datatype:
- These data types are employed by Oracle to define how data goes to be stored during a column.
- External Datatype:
- These data types are employed by Oracle to format the output data, then this output data are going to be stored during a host variable.
Let's see how we will equivalence the info type. On a variable-by-variable basis, we use the var statement for equivalencing. The syntax of the var statement is given below:
EXEC SQL VAR <host_variable> IS <data_type> [(<length>)];
- For example, we would like to retrieve the student names from the scholar table; then, we'd like 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 subsequent code:
char names[12];
EXEC SQL VAR names IS STRING(12);
- The column, i.e., student name, contains 11 characters. because the column student name contains the 11 characters, so we'd like to allot the 12 characters (11 characters of student name plus terminator character (\0)). We use the STRING data type that gives 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., within the above example, we equivalence the char array to the Oracle external datatype (String).we will also equivalence the user-defined data types by using the sort command. The syntax of the sort statement is given below:
EXEC SQL TYPE <user-defined_datatype> IS <type_name> [<length>][Reference]
Dynamic vs Static SQL statements
- Mainly, static SQL statements are used for fixed applications, but sometimes it's required for a program to make the SQL statements dynamically. To make the dynamic SQL statement, first, we'd like to store the SQL statement during 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.
Read Also
char *s = "INSERT INTO student VALUES(12, 'Peter', 3)";
EXEC SQL PREPARE q FROM :s;
EXEC SQL EXECUTE q; Transactions
Transactions
- Oracle Pro*C also follows the concept of the transaction as defined by the SQL standard. A transaction may be 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 employed to form all the changes permanent since the transaction began.
- The EXEC SQL ROLLBACK is employed to undo all the changes since the transaction began.
- If we start subsequent transaction without writing the statement EXEC SQL COMMIT, then all the changes made during this transaction are going to be discarded.