Pro * C
Pro * C [ pɹoʊˈsiː ] / Pro * C ++ [ -ˈplʌs ˈplʌs ] is a precompiler from Oracle for the programming language C and C ++ . Using the precompiler it is possible to mix SQL expressions and normal C or C ++ source code elements with one another. However, this only applies to DML commands; DDL and DCL commands must still be executed, for example, with the SQL * Plus program. The resulting file is then translated into valid source code so that it can be translated with a conventional compiler . Internally, the SQL expressions embedded in the source code are converted into normal Oracle function calls by the precompiler.
Supported Concepts
With Pro * C it is possible to communicate with a database within a C program in different ways. Compared to approaches such as SQLJ , Pro * C is a very flexible solution for creating database applications. The following are the current properties that a Pro * C compiler supports.
- Embedded SQL , static, requires a precompiler
- PL SQL , static, requires a precompiler
- Host arrays
- Dynamic SQL
Dynamic SQL variants
Dynamic SQL is broken down into different flavors. Depending on the variant, it is possible to execute a real SQL query. The appropriate method is determined with the help of a decision tree .
Method 1
This method makes it possible to write a program that executes dynamic SQL expressions or creates new expressions. Each printout is executed immediately using the EXECUTE IMMEDIATE command. However, the SQL expression must not contain a query ( SELECT expression) or placeholders for input host variables. The following expression is a valid expression for method 1:
'DELETE FROM EMP WHERE DEPTNO = 20' 'GRANT SELECT ON EMP TO scott'
If method 1 is used, the SQL expression to be executed is parsed each time it is executed.
Method 2
This method makes it possible to write a program that executes dynamic SQL expressions or creates new expressions. It is then possible to process the individual SQL expressions using the PREPARE or EXECUTE command. However, the SQL expression must not contain a query (SELECT expression). The number of placeholders for the host variables and their data types must be known at the time of compilation . The following example would correspond to a dynamic SQL expression of this category:
'INSERT INTO EMP (ENAME, JOB) VALUES (:emp_name, :job_title)' 'DELETE FROM EMP WHERE EMPNO = :emp_number'
If method 2 is used, the SQL expression is parsed only once. Nevertheless, the printout can be executed several times with different host variables. SQL DDL expressions such as CREATE or GRANT are executed if these are PREPARE d, i.e. prepared.
Method 3
This method makes it possible to write a program that executes dynamic SQL expressions or creates new expressions. It is then possible to process the individual SQL expressions using the PREPARE command and the cursor commands DECLARE , OPEN , FETCH and CLOSE . The number of select list elements, the number of placeholders for the input host variables and the data types of the input host variables must be known at the time of compilation. The following example corresponds to such an SQL expression:
'SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO' 'SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :dept_number'
Method 4
This method makes it possible to write a program that executes dynamic SQL expressions or creates new expressions. The SQL expression is then processed using descriptors. The number of select list elements, the number of placeholders for the input host variables and the data types of the input host variables can be unknown until the time of execution. The following source code falls into this category:
'INSERT INTO EMP (<unknown>) VALUES (<unknown>)' 'SELECT <unknown> FROM EMP WHERE DEPTNO = 20'
Method 4 is required for dynamic SQL expressions in which the number of select list elements or the number of input host variables is unknown.