5 The ESQL/C to Oracle Pro*C Converter

This chapter describes the E/SQL to Pro*C converter, its scope, and limitations. It includes the following sections:

Introduction to E/SQL and Pro*C
Syntactical Conversion Issues
Conversion Errors and Warnings
Restrictions
Using Demonstration Code

Introduction to E/SQL and Pro*C

Oracle and Informix Dynamic Server have similar methods of embedding their SQL statements into a third generation language, in this case C (or C++). You run a precompiler that converts the C containing embedded SQL into pure C. High-level embedded SQL directives are replaced by vendor-specific C code. You can use a standard C compiler to compile and link with the vendor libraries to produce an executable.

Oracle Pro*C is easier to write and maintain than a pure C. One reason for this is that the C and embedded SQL are separated in the source code.

The SQL used in Oracle Pro*C files complies with ANSI standards, as are some of the embedded SQL commands and techniques. There are some differences, however, that are resolved manually or by using a tool such as the ESQL/C to Oracle Pro*C Converter, or a combination of both methods.

Using the ESQL/C to Oracle Pro*C Converter

This section provides an example conversion using the ESQL/C to Oracle Pro*C Converter, and describes some common conversions handled automatically by the tool.

Example Capture of ESQL/C Source Files

The following example shows the use of the tool. You capture this code by choosing Action->Capture ESQL/C Source Files in the Migration Workbench.

For more information about the core code, see "Conversion Errors and Warnings" and "Using Demonstration Code" .

Example Code:

 /*
 *  esqlproc1.ec
 *
 *  This program connects to the database, declares and opens a cursor, 
 *  fetches the names, salaries, and commissions of all
 *  salespeople, displays the results, then closes the cursor. 
 */ 

#include <stdio.h>
#include <string.h>
#include <sqlca.h>
#include <stdlib.h>

#define UNAME_LEN      20 
#define PWD_LEN        11 
 
EXEC SQL BEGIN DECLARE SECTION;
    char username[20]="informix";
    char password[20]="inform9";
    char       emp_name[11]; 
    float      salary; 
    float      commission; 
EXEC SQL END DECLARE SECTION;

void sql_error(msg) 
    char *msg;
{ 
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s\n", msg);

    EXEC SQL ROLLBACK;
    exit(EXIT_FAILURE);
} 

void main() 
{ 
 
/* Connect to the database. */ 
 
    EXEC SQL WHENEVER SQLERROR GO TO connecterror;

    EXEC SQL connect to 'turloch@mtg1_tcp' user :username using :password;
    printf("\nConnected to the database as user: %s\n", username); 

    EXEC SQL WHENEVER SQLERROR GO TO declareerror;
/* Declare the cursor. All static SQL explicit cursors
 * contain SELECT commands. 'salespeople' is a SQL identifier,
 * not a (C) host variable.
 */
    EXEC SQL DECLARE salespeople CURSOR FOR 
        SELECT ENAME, SAL, COMM INTO 
          :emp_name, 
          :salary, 
          :commission
            FROM EMP 
            WHERE JOB LIKE 'SALES%'; 

    EXEC SQL WHENEVER SQLERROR GO TO openerror;
/* Open the cursor. */
    EXEC SQL OPEN salespeople; 
 
/* Get ready to print results. */
    printf("\n\nThe company's salespeople are--\n\n");
    printf("Salesperson   Salary   Commission\n"); 
    printf("-----------   ------   ----------\n"); 
 
/* Loop, fetching all salesperson's statistics.
 * Cause the program to break the loop when no more
 * data can be retrieved on the cursor.
 */
    EXEC SQL WHENEVER SQLERROR GO TO fetcherror;
    EXEC SQL WHENEVER NOT FOUND go to breakout; 

    for (;;) 
    { 
        EXEC SQL FETCH salespeople ; 
        printf("%s %9.2f %12.2f\n", emp_name, 
                salary, commission); 
    } 
 
breakout:
/* Close the cursor. */
    EXEC SQL WHENEVER SQLERROR GO TO closeerror;
    EXEC SQL CLOSE salespeople; 
    
    EXEC SQL WHENEVER SQLERROR GO TO freeerror;
    EXEC SQL FREE salespeople; 

    printf("\nArrivederci.\n\n");

    EXEC SQL WHENEVER SQLERROR GO TO disconnecterror;
    EXEC SQL disconnect current; 
    exit(EXIT_SUCCESS); 
 freeerror:
    sql_error("free error--");
 connecterror:
    sql_error("connect error--");
 declareerror:
    sql_error("declare error--");
 openerror:
    sql_error("open error--");
 fetcherror:
    sql_error("fetch error--");
 closeerror:
    sql_error("close error--");
 disconnecterror:
    sql_error("disconnect error--");
}

Oracle Pro*C Conversion

To pass the code through the converter choose Action->Convert E/SQL to Pro*C. To generate the files created by this action, and save them on to the file system, choose Action->Generate PRO*C Source Files.

The following example Oracle Pro*C file is generated:

/*
 *  esqlproc1.ec
 *
 *  This program connects to the database, declares and opens a cursor, 
 *  fetches the names, salaries, and commissions of all
 *  salespeople, displays the results, then closes the cursor. 
 */ 

#include <stdio.h>
#include <string.h>
#include <sqlca.h>
#include <stdlib.h>

#define UNAME_LEN      20 
#define PWD_LEN        11 

EXEC SQL BEGIN DECLARE SECTION;
    char username[20]="informix";
    char password[20]="inform9";
    char       emp_name[11]; 
    float      salary; 
    float      commission; 
EXEC SQL END DECLARE SECTION;

void sql_error(msg) 
    char *msg;
{ 
    /* ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives. */
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s\n", msg);
    /* ESQL-CONV-MSG:(ROLLBACK) Statement automatically closes all cursors referenced in a CURRENT OF clause when compilation mode = ORACLE. */
    /* ESQL-CONV-MSG:(ROLLBACK) For more information about extra ROLLBACK option available in Oracle, see the Oracle 8i Users Guide. */
    EXEC SQL ROLLBACK WORK;
    exit(EXIT_FAILURE);
} 

void main() 
{ 
 
/* Connect to the database. */ 
 
    /* ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives. */
    EXEC SQL WHENEVER SQLERROR GOTO connecterror;
 
    /* ESQL-CONV-ERR:(CONNECT) Manual conversion of the username, password and database required. */
    {
      char oracleid = '/';
      EXEC SQL CONNECT :oracleid; 
    }
    printf("\nConnected to the database as user: %s\n", username); 
 
    /* ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives. */
    EXEC SQL WHENEVER SQLERROR GOTO declareerror;
/* Declare the cursor. All static SQL explicit cursors
 * contain SELECT commands. 'salespeople' is a SQL identifier,
 * not a (C) host variable.
 */
    /* ESQL-CONV-MSG:(INTO) Clause removed from cursor definition and integrated into FETCH statement. */
    EXEC SQL 
        DECLARE salespeople CURSOR FOR 
         SELECT ENAME,
                SAL,
                COMM
         FROM EMP
         WHERE JOB LIKE 'SALES%'; 
    /* ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives. */
    EXEC SQL WHENEVER SQLERROR GOTO openerror;
/* Open the cursor. */
    EXEC SQL 
         OPEN salespeople; 
 
/* Get ready to print results. */
    printf("\n\nThe company's salespeople are--\n\n");
    printf("Salesperson   Salary   Commission\n"); 
    printf("-----------   ------   ----------\n"); 
 
/* Loop, fetching all salesperson's statistics.
 * Cause the program to break the loop when no more
 * data can be retrieved on the cursor.
 */
    /* ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives. */
    EXEC SQL WHENEVER SQLERROR GOTO fetcherror;
    /* ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives. */
    EXEC SQL WHENEVER NOT FOUND GOTO breakout; 

    for (;;) 
    { 
        /* ESQL-CONV-MSG:(INTO) Clause originally declared within cursor declaration. */
        EXEC SQL 
             FETCH salespeople
             INTO :emp_name,
                  :salary,
                  :commission; 
        printf("%s %9.2f %12.2f\n", emp_name, 
                salary, commission); 
    } 
 
breakout:
/* Close the cursor. */
    /* ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives. */
    EXEC SQL WHENEVER SQLERROR GOTO closeerror;
    /* ESQL-CONV-MSG:(CLOSE) Statement not required when compilation mode = ORACLE. */
    /* EXEC SQL CLOSE salespeople */ 
    
    /* ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives. */
    EXEC SQL WHENEVER SQLERROR GOTO freeerror;
    /* ESQL-CONV-MSG:(FREE) Statement not required in ORACLE. */
    /* EXEC SQL FREE salespeople; */ 

    printf("\nArrivederci.\n\n");
    /* ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives. */
    EXEC SQL WHENEVER SQLERROR GOTO disconnecterror;
    EXEC SQL COMMIT WORK RELEASE; 
    exit(EXIT_SUCCESS); 
 freeerror:
    sql_error("free error--");
 connecterror:
    sql_error("connect error--");
 declareerror:
    sql_error("declare error--");
 openerror:
    sql_error("open error--");
 fetcherror:
    sql_error("fetch error--");
 closeerror:
    sql_error("close error--");
 disconnecterror:
    sql_error("disconnect error--");
}

Manual Changes to the Oracle Pro*C File

In the "Oracle Pro*C Conversion" example you must add the CONNECT details, as shown in the following table:

Code Generated by ESQL/C to Oracle Pro*C Converter	{ char oracleid = '/'; EXEC SQL CONNECT :oracleid; }
Oracle Pro*C Code	{ EXEC SQL BEGIN DECLARE SECTION; char *oracleid = "examp/examp"; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT :oracleid; }

The executable relies on a database populated by data. For an example about how to populate the database, see "Using Demonstration Code". The following example shows how to produce an executable:

Precompile the code from Oracle Pro*C to a C file using the Oracle Pro*C executable proc esqlproc1.pc.
Compile the C code using a suitable development environment. The Oracle Pro*C example shipped by with Oracle contains project files for the Visual C++ development environment. The project contains details of the libraries and include files required to build a small executable based on Oracle Pro*C. The project file used is %ORACLE_HOME%\precomp\demo\proc\sample.dsp. You have to add the C file esqlproc1.c to the project.

For more information about Oracle Pro*C/C++, see the Pro*C/C++ Precompiler Programmer's Guide.

The following is sample output from the executable:

The company's salespeople are--
Salesperson   Salary   Commission
-----------   ------   ----------
ALLEN        1600.00       300.00
WARD         1250.00       500.00

Arrivederci.

Syntactical Conversion Issues

This section provides information about the Informix Dynamic Server ESQL/C constructs and the equivalent Oracle constructs generated by the Migration Workbench. Examples of how to resolve syntactical conversion issues are provided where relevant. The following constructs are described in detail:

EXEC SQL Statement
INCLUDE Files
UPDATE Statement
ANSI Compliance
Double equal sign in WHERE Clause
OUTER JOIN Syntax
FETCH Clause
Header Files SQLNOTFOUND
CURSOR Declaration
DECLARE CURSOR Statement
FOR UPDATE Option

EXEC SQL Statement

In all programs you replace the dollar ($) sign preceding the SQL sign with EXEC SQL and replace all the dollar signs before host variables with a colon (:). The following table compares the dollar sign in Informix Dynamic Server and the EXEC SQL Statement in Oracle:

Database Language	Example
Informix Dynamic Server ESQL/C	$ SELECT login_no INTO $login_no;
Oracle Pro*C	EXEC SQL SELECT login_no INTO :login_no;

INCLUDE Files

The INCLUDE files for Informix Dynamic Server ESQL/C and Oracle are different., You must replace Informix Dynamic Server ESQL/C INCLUDE files with Oracle INCLUDE files.

UPDATE Statement

The UPDATE statement works similarly in Informix Dynamic Server and Oracle, but they have different syntax. The Oracle syntax is clearer, and the resulting code is easier to maintain. The following table compares the UPDATE statement in Informix Dynamic Server and Oracle:

Database Language	Example
Informix Dynamic Server ESQL/C	EXEC SQL UPDATE employees SET (emp_no, emp_name) = (:emp_no, :emp_name) WHERE emp_no == :old_emp_no;
Oracle Pro*C	EXEC SQL UPDATE employees SET emp_no = :emp_no, emp_name = :emp_name WHERE emp_no = :old_emp_no;

ANSI Compliance

The Oracle precompiler can generate the C code in either ANSI or non-ANSI compliant code. The default is non-ANSI.

Double equal sign in WHERE Clause

Check the WHERE clause in the SELECT, UPDATE, and DELETE statements in Informix Dynamic Server ESQL/C for double equal signs (==). An equal operator can be a single or a double equal sign. Oracle supports the ANSI standard single equal sign. The following table compares the WHERE clause equal operator in the SELECT, UPDATE, and DELETE statements in Informix Dynamic Server and Oracle:

Database Language	Example
Informix Dynamic Server ESQL/C	EXEC SQL SELECT login_no INTO :login_no FROM users WHERE user_name == 'PAM';
Oracle Pro*C	EXEC SQL SELECT login_no INTO :login_no FROM users WHERE user_name='PAM';

OUTER JOIN Syntax

The Informix Dynamic Server ESQL/C OUTER JOIN syntax is different from Oracle Pro*C. The following table compares the OUTER JOIN syntax in Informix Dynamic Server and Oracle:

Database Language	Example
Informix Dynamic Server ESQL/C	EXEC SQL SELECT login_no INTO :login_no FROM users a , OUTER logins b WHERE a.user_name = b.user_name;
Oracle Pro*C	EXEC SQL SELECT login_no INTO :login_no FROM users a, logins b WHERE a.user_name = b.user_name (+);

Database Language

Example

Informix Dynamic Server ESQL/C

EXEC SQL SELECT login_no
INTO :login_no
FROM users a , OUTER logins b
WHERE a.user_name = b.user_name;

Oracle Pro*C

EXEC SQL
SELECT login_no
INTO :login_no
FROM users a, logins b
WHERE a.user_name =
b.user_name (+);

Note:

In Release 10.1.0 of the Migration Workbench, you must manually add the plus sign.

FETCH Clause

The Informix Dynamic Server ESQL/C FETCH clause allows the NEXT keyword in the statement. The following table compares the FETCH clause in Informix Dynamic Server and Oracle:

Database Language	Example
Informix Dynamic Server ESQL/C	EXEC SQL FETCH NEXT cur INTO :emp_no;
Oracle Pro*C	EXEC SQL FETCH cur1 INTO :emp_no;

Header Files SQLNOTFOUND

Informix Dynamic Server ESQL/C header files define SQLNOTFOUND. In Oracle, you must explicitly define SQLNOTFOUND as either +100 (ANSI mode) or +1403 (Oracle mode) depending on the mode being used in the Oracle precompiler.

CURSOR Declaration

In Informix Dynamic Server ESQL/C, the CURSOR can be declared with WITH HOLD options, so that a CURSOR is not closed by COMMIT or ROLLBACK. This does not comply with the ANSI standard, but Oracle supports it provided you select the MODE=ORACLE precompiler option. For this reason, you modify the program to COMMIT after closing the CURSOR.

DECLARE CURSOR Statement

Informix Dynamic Server ESQL/C DECLARE CURSOR statements can have INTO clauses. You can specify the host variables in which to fetch the data in the DECLARE CURSOR statement, and then use the cursor name in the FETCH statement. This does not comply with the ANSI standard, and is not supported by Oracle. As a result, you change all DECLARE statements with INTO clauses to have the INTO clause in the FETCH statement. The following table compares the DECLARE CURSOR statement in Informix Dynamic Server and Oracle:

Database Language	Example
Informix Dynamic Server ESQL/C	FETCH statement. EXEC SQL DECLARE CURSOR cur1 FOR SELECT login_no INTO :login_no FROM users WHERE user_name = 'PAM'; EXEC SQL FETCH cur1;
Oracle Pro*C	EXEC SQL DECLARE CURSOR cur1 FOR SELECT login_no FROM users WHERE user_name='PAM'; EXEC SQL FETCH cur1 INTO :login_no;

Database Language

Example

Informix Dynamic Server ESQL/C

FETCH statement.
EXEC SQL DECLARE CURSOR cur1 FOR
SELECT login_no
INTO :login_no
FROM users
WHERE user_name = 'PAM';

EXEC SQL FETCH cur1;

Oracle Pro*C

EXEC SQL
DECLARE CURSOR cur1 FOR
SELECT login_no
FROM users
WHERE user_name='PAM';

EXEC SQL FETCH cur1
INTO :login_no;

FOR UPDATE Option

Informix Dynamic Server ESQL/C cannot lock individual rows. To prevent a row from being modified, the existing code must declare a cursor with the FOR UPDATE option, open the cursor, fetch it, and then close it. Oracle can lock a selected row by using the FOR UPDATE option, without requiring an explicit cursor declaration. You must change the logic of some programs to take advantage of the Oracle method. The following table compares the FOR UPDATE option in Informix Dynamic Server and Oracle:

Database Language	Example
Informix Dynamic Server ESQL/C	EXEC SQL DECLARE CURSOR cur1 FOR SELECT login_no INTO :login_no FROM users WHERE user_name = 'PAM'; FOR UPDATE; EXEC SQL OPEN cur1; EXEC SQL FETCH cur1; EXEC SQL CLOSE cur1;
Oracle Pro*C	EXEC SQL SELECT login_no INTO :login_no FROM users WHERE user_name = 'PAM' FOR UPDATE;

Database Language

Example

Informix Dynamic Server ESQL/C

EXEC SQL DECLARE CURSOR cur1 FOR
SELECT login_no
INTO :login_no
FROM users
WHERE user_name = 'PAM';
FOR UPDATE;
EXEC SQL OPEN cur1;
EXEC SQL FETCH cur1;
EXEC SQL CLOSE cur1;

Oracle Pro*C

EXEC SQL
SELECT login_no
INTO :login_no
FROM users
WHERE user_name = 'PAM'
FOR UPDATE;

Application Conversion Issues

Oracle and Informix Dynamic Server use temporary tables. The difference being that Oracle creates temporary tables once, and the data is kept separate between sessions. You must manually create temporary tables in Oracle, and separate from the application. The Migration Workbench marks instances of this detected by the converter as errors.

The following table compares the TEMP TABLE statement option in Informix Dynamic Server and Oracle:

Database Language	Example
Informix Dynamic Server ESQL/C	EXEC SQL CREATE TEMP TABLE tab2 (fname CHAR(15), lname CHAR(15)) WITH NO LOG;
Oracle Pro*C	/* SPCONV-WRN:(TEMP TABLE): It will be more performant to create the temporary table separately. */ EXEC SQL CREATE GLOBAL TEMPORARY TABLE tab2(fname CHAR(15), lname CHAR(15)) ON COMMIT PRESERVE ROWS;

Database Language

Example

Informix Dynamic Server ESQL/C

EXEC SQL CREATE TEMP TABLE tab2 (fname CHAR(15), lname CHAR(15)) WITH NO LOG;

Oracle Pro*C

/* SPCONV-WRN:(TEMP TABLE): It will be more performant to create the temporary table separately. */    
EXEC SQL CREATE GLOBAL TEMPORARY TABLE tab2(fname CHAR(15), lname CHAR(15)) ON COMMIT PRESERVE ROWS;

The Oracle Pro*C Preprocessor

For more information about Oracle Pro*C including command line options for the preprocessor, see the Pro*C/C++ Precompiler Programmer's Guide.

Conversion Errors and Warnings

The ESQL/C to Oracle Pro*C Converter shares most of the errors and warnings it generates with the stored procedure parser. For more information about stored procedures, see Chapter 3, "Triggers, Packages, and Stored Procedures". For more information about additional errors and warnings, see "ESQL/C to Oracle Pro*C Converter Errors" and ESQL/C to Oracle Pro*C Warnings.

ESQL/C to Oracle Pro*C Converter Errors

The cause of the Informix Dynamic Server ESQL/C to Oracle Pro*C converter errors require manual investigation and correction by the user. Table 5-1, "ESQL to Oracle Pro*C Converter Errors" lists details of possible error messages.

Note:

In Table 5-1, "ESQL to Oracle Pro*C Converter Errors" SPCONV refers to errors shared with the stored procedure parser. ESQL refers to errors specific to the embedded SQL parser.

Table 5-1 ESQL to Oracle Pro*C Converter Errors

Error Message	Description
ESQL-CONV-ERR:(EXEC SQL ..) The converter will not parse this expression correctly.	The converter failed to understand this statement. It places it in a comment. You must manually convert it.
SPCONV-ERR:(**) Statement ignored. Manual conversion required.	The (**) statement (for example the dynamic PUT statement) is not automatically converted. You must manually convert it. Most Set statements also require manual conversion.
ESQL-CONV-ERR:(CONNECT) Manual conversion of the username, password and database required.	The CONNECT string changes when you move it from Informix Dynamic Server to Oracle.
ESQL-CONV-ERR:(DATABASE) Manual conversion of the username, password and database required.	The CONNECT string changes when you move it from Informix Dynamic Server to Oracle.
SPCONV-ERR:(ALTER INDEX) Statement ignored. Manual conversion may be required.	The ALTER statement clause is unlikely to occur in ESQL/C and Oracle Pro*C. If it occurs, you must manually add it.
/* SPCONV-MSG:(EXEC SQL ..) The converter will not parse this expression correctly. / /******************* ERROR STATEMENT COMMENTED ******************* exec sql <Statement not parsed goes here> *****************************/	The converter has failed on this statement and has continued with the next statement.
/* ESQL-CONV-ERR:(DYNAMIC SQL) Conversion not supported in this release. Manual conversion may be required. */	Release 9.2.0 of the Migration Workbench does not support Dynamic SQL. However, some commands do work in ANSI mode.

ESQL/C to Oracle Pro*C Warnings

Warning messages are for information purposes and may not require intervention. Table 5-2 lists possible warning messages.

Note:

In Table 5-2 SPCONV refers to errors shared with the stored procedure parser. ESQL refers to warnings specific to the embedded SQL parser.

Table 5-2 ESQL/C to Oracle Pro*C Warnings

Warning	Description
ESQL-CONV-MSG:(CLOSE) Statement not required when compilation mode = ORACLE.	Oracle mode is the default Oracle Pro*C setting so the close statement is not required. It places it in a comment.
ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives.	An informational message to note that the Oracle WHENEVER statement has additional options that may be of use.
ESQL-CONV-MSG:(INTO) Clause removed from cursor definition and integrated into FETCH statement.	A reminder that the INTO clause has moved, as shown in the syntax of the "DECLARE CURSOR Statement" example.
ESQL-CONV-MSG:(DYNAMIC SQL) Unsupported in this release. Manual conversion may be required.	Most commands for dynamic SQL statements are similar in Oracle and Informix Dynamic Server. The generated SQL statements should be similar, but the converter makes no attempt to convert the dynamic SQL statements, however simple commands are converted.
/* SPCONV-MSG:(*) Statement passed to DDL file. /	Some commands may be executed before running the new Oracle Pro*C application. For example, you create Oracle temporary tables before running the application. The data is saved separately each time a session is run.
/* SPCONV-WRN:(=>) Oracle requires Positional parameter notation to precede Named parameter notation. Manual conversion required.*/	This warning indicates complications and variations in the CALL statement syntax.
/* ESQL-CONV-MSG:(CLOSE) Statement not required when compilation mode = ORACLE. */	You should close a cursor before reopening it. However, if you specify the Oracle mode (default), you do not need to close the cursor. Choosing the oracle mode can increase performance.
/* ESQL-CONV-MSG:(COMMIT) Statement will automatically close all cursors referenced in a CURRENT OF clause when compilation mode = ORACLE. Other cursors are unaffected. */	Statement automatically closes all cursors included in a CURRENT OF clause when compiled in Oracle mode.
"/* ESQL-CONV-MSG:(COMMIT) Statement will automatically close all explicit cursors when compilation mode = ORACLE. */	Informational message about the behaviors of cursors on COMMIT. For more information about COMMIT, see the Oracle Pro*C documentation.
/* SPCONV-MSG:(CONTINUE *) Statement emulated using GOTO statement and LABEL definition./	An informational message about how Oracle emulates Informix Dynamic Server behavior.
/* SPCONV-MSG:(WITH RESUME) Collating results for REF CURSOR return.*/	An informational message referring to the use of REF CURSOR. For more information about REF CURSOR, see "Triggers, Packages, and Stored Procedures".
/* SPCONV-MSG:(WITH RESUME) Initialising GLOBAL TEMPORARY TABLE used to store Procedures interim results. */	An informational message about the use of temporary tables in the emulation of the Informix Dynamic Server WITH RESUME option.
/* ESQL-CONV-MSG:(SCROLL) Scroll cursors not available in Oracle. Manual conversion may be required. */	Oracle does not have SCROLL cursors. You can manually move the data from a cursor into a PL/SQL table.
/* ESQL-CONV-MSG:(WITH HOLD) Unsupported in Oracle. Manual conversion may be required. */	The WITH HOLD option is not available in Oracle. You must manually convert it.
/* ESQL-CONV-MSG:(INTO) Clause removed from cursor definition and integrated into FETCH statement. */	An informational message. Move the INTO clause to the FETCH statement in Oracle.
/* ESQL-CONV-MSG:(SELECT FIRST n) Emulated using FOR clause in FETCH statement. Manual conversion may be required. */	An informational message. Additional declare cursor facilities not available in Oracle.
/* ESQL-CONV-MSG:(MODE=ANSI) ANSI compliant variable declaration generated. */	`EXEC SQL BEGIN DECLARE SECTION;` and `EXEC SQL END DECLARE SECTION;` statements added.
/ESQL-CONV-MSG:(MODE=ORACLE) Non ANSI compliant variable declaration generated. /	Declare section header and footer not required in Oracle mode.
/* SPCONV-MSG:(GLOBAL *) Global Variable definition moved to globalPkg Package./	Global variables moved to the OMWB_EMULATION user package GLOBALPKG.
/* SPCONV-MSG:(DROP DATABASE) Statement ignored. */	Oracle databases are seldom dropped in embedded SQL. If a database is dropped it is ignored.
/* SPCONV-MSG:(DROP *) OMWB_Emulation.DDL_MANAGER procedure used to execute DDL statement./	Release 9.2.0 of the Migration Workbench does not support DROP ** statements. These statements are ignored.
/* ESQL-CONV-MSG:(BEGIN .. END) Embedded PL/SQL code block generated for Stored Procedure call. */	An informational message describing how Oracle code contains an embedded PL/SQL code block.
/* ESQL-CONV-MSG:(SELECT) Statement illegal as a procedure parameter in Oracle. */	Statement illegal as a procedure parameter in Oracle.
/* ESQL-CONV-MSG:(SELECT) Statement removed from procedure call. */	Statement removed from procedure call.
/* ESQL-CONV-MSG:() Manual conversion of the generated variable TYPE may be required./	SELECT statement in Informix Dynamic Server converted into a SELECT variable, which may have the wrong type. Manual conversion may be required.
/* ESQL-CONV-MSG:(INTO) Procedure call converted to function call as only one value returned. */	An Informix Dynamic Server procedure returning one value converts to an Oracle function. PL/SQL functions must return a value into a variable. For example, `a:= func(); just func();` creates an error.
/* ESQL-CONV-MSG:(*) Statement emulated using Oracle FOR syntax within cursor declaration. /	An informational message describing emulation in Oracle.
/* ESQL-CONV-WRN:(*) Oracle has no equivalent cursor action. Manual conversion required./	Oracle does not have various cursor actions. You can manually move the data from a cursor into a PL/SQL table.
/* ESQL-CONV-MSG:(INTO) Clause originally declared within cursor declaration. */	The INTO clause was originally in the DECLARE section but was moved to the FETCH statement.
/* ESQL-CONV-MSG:(FREE) Statement not required in ORACLE. */	EXEC SQL FREE CURSORID statement placed in a comment.
/* ESQL-CONV-MSG:(DYNAMIC SQL) Unsupported in this release. Manual conversion may be required.*/	Release 9.2.0 of the Migration Workbench does not support Dynamic SQL, but simple SQL and simple ANSI dynamic SQL are supported. Convert substitution variables to `:var1`.
/* ESQL-CONV-MSG:(GET DIAGNOSTICS) Manual conversion required.*/	This call is significantly different between Oracle and Informix Dynamic Server. You must manually convert it.
/* SPCONV-MSG:(SUBQUERY) Subquery within IF statement emulated by using Boolean variable.*/	An informational message describing emulation in Oracle.
/* SPCONV-MSG:(LOCK TABLE) See 'Oracle 8i Server SQL reference' for details of other LOCK options.*/	An informational message. For more information, see the Oracle 8i Server SQL documentation.
/* SPCONV-MSG:(WITH RESUME) Statement emulated through use of GLOBAL TEMPORARY TABLES.*/	Oracle does not support the WITH RESUME statement. It is emulated using temporary tables. Manual conversion may be required.
/* SPCONV-MSG:(RETURNING) Informix RETURNING clause parameters converted to Oracle OUT parameters.*/	If an Informix Dynamic Server function has more than one returning parameter, these are converted into Oracle OUT parameters.
/* ESQL-CONV-MSG:(ROLLBACK) Statement expanded to utilise Oracle SAVEPOINTS. */	An informational message describing how the ROLLBACK statement is used to go to the previous SAVE'POINT, if that parser option is used.
/* ESQL-CONV-MSG:(ROLLBACK) Statement automatically closes all cursors referenced in a CURRENT OF clause when compilation mode = ORACLE. */	An informational message describing how cursors are closed in Oracle mode.
/* ESQL-CONV-MSG:(ROLLBACK) Statement closes all explicit cursors when compilation mode = ANSI. */	An informational message describing how cursors are closed in ANSI mode.
/* ESQL-CONV-MSG:(ROLLBACK) Extra ROLLBACK option available in Oracle. See the Oracle8i Users Guide for more information. */	An informational message. For more information, see the Oracle 8i Server SQL documentation.
/* ESQL-CONV-MSG:(OUTER) Simple OUTER joins may not be fully converted. Manual conversion may be required. */	Simple OUTER joins may not be automatically converted.
/* ESQL-CONV-MSG:(MATCHES) Complex search patterns not fully converted. Manual conversion may be required. */	An informational message describing how complex search patterns are not fully converted. Manual conversion may be required.
/* ESQL-CONV-MSG:(NOWAIT) Keyword added to emulate Informix SET LOCK MODE statement. */	Informix Dynamic Server sets NOWAIT in a SET statement, but Oracle places it in the SELECT FOR UPDATE statement.
/* ESQL-CONV-MSG:(GROUP BY) Oracle does not enable literal numbers to be used in the GROUP BY clause. Manual conversion may be required. */	Replace `GROUP BY 1,3;` with `GROUP BY col1, col3;`
/* SPCONV-MSG:(SET DEBUG FILE) OMWB_emulation.utilities Package introduced to mimic Informix functionality.*/	Debugging enabled by use of `the OMWB_EMULATION.UTILITIES` package.
/* ESQL-CONV-MSG:(NOT WAIT) Option may be emulated by implementing the Oracle NOWAIT SELECT statement option. */	Informix Dynamic Server sets NOWAIT in a SET statement, but Oracle places it in the SELECT FOR UPDATE statement.
/* SPCONV-MSG:(SYSTEM) Emulating Informix SYSTEM statement by using OMWB_emulation.SHELL Procedure.*/	`The OMWB_EMULATION`.`SHELL` procedure can emulate the system command, along with a small C program.
/* SPCONV-MSG:(TRACE) OMWB_emulation.utilities Package introduced to mimic Informix functionality.*/	Trace facilities are provided by the `OMWB_EMULATION.UTILITIES.DEBUG()`procedure.
/* ESQL-CONV-MSG:(ONLY) No equivalent available in Oracle. Statement ignored. */	ONLY statement is not supported in Oracle. You must manually convert it.
/* SPCONV-MSG:(UPDATE STATISTICS) Statement ignored.*/	UPDATE statistics statement is not supported in Oracle. You must manually convert it.
/ESQL-CONV-MSG:(WHENEVER) Oracle supports additional directives. /	An informational message.
/SPCONV-WRN:(ALTER TABLE) Unable to convert ALTER TABLE statement. Manual conversion required/	The ALTER TABLE options used do not directly convert to Oracle syntax. You must manually convert it.
/* SPCONV-WRN:(TEMP TABLE): It will be more performant to create the temporary table separately .*/	Remove the CREATE TABLE statement and run separately, before running the Oracle Pro*C application. Oracle temporary tables hold per-session information but they cannot be separately created for each session.
/* SPCONV-WRN:(*) Conversion of remote Database links not supported. Manual conversion required. /	Use Oracle database links to simulate remote database links. For more information about using Oracle database links, see the Oracle9i documentation.
/* SPCONV-WRN:(REFERENCES BYTE) Converted to Oracle BLOB datatype. Restrictions apply.*/	Informix Dynamic Server BLOB and CLOB data type support differs from Oracle BLOB and CLOB data type support. Manual conversion required. In this example, the BYTE data type is converted to BLOB.
/* SPCONV-WRN:(REFERENCES TEXT) Converted to Oracle CLOB datatype. Restrictions apply.*/	Informix Dynamic Server BLOB and CLOB data type support differs from Oracle BLOB and CLOB data type support. Manual conversion required. In this example, the TEXT data type is converted to CLOB.
/* SPCONV-MSG:(DROP *) Statement passed to DDL file./	One-off statements that are best used in a SQL script.
/* SPCONV-WRN:(DBINFO) Unable to convert function call. Manual conversion required.*/	Some DBINFO calls cannot be directly converted to Oracle.
/* SPCONV-WRN:(*) Manual conversion required if the procedure returns more than one value./	An informational message.
/* SPCONV-WRN:(EXCEPTION) Emulation of Informix Exceptions incomplete.*/	For more information about this error message, see the Informix Dynamic Server documentation.
/* ESQL-CONV-WRN:(RECOVER) Statement Ignored. */	RECOVER table statement is ignored.
/* SPCONV-WRN:(RETURN) Collating results for REF CURSOR return.*/	Results collated into temporary table are selected out in a single result set.
/* SPCONV-WRN:(FOR READ ONLY) Statement Ignored. */	Default Oracle behavior is FOR READ ONLY.
/* SPCONV-WRN:(SYSTEM) Statement Ignored. Parse option turned off.*/	The SYSTEM emulation option switched off so SYSTEM is ignored. You must manually convert it.
/* SPCONV-WRN: ( TRACE *) Currently not supported. Manual conversion required./	Some TRACE options are not converted by the converter. For an overview of the Oracle tracing facilities, see the Oracle documentation.
/* SPCONV-WRN:(MATCHES) Converted to an Oracle LIKE, BETWEEN or IN statement. Manual conversion of the search pattern may be required.*/	The MATCHES statement is not precisely converted to Oracle. You must manually convert the search pattern.

Restrictions

There are some restrictions with the Informix Dynamic Server ESQL/C to Oracle Pro*C Converter. The following converter restrictions apply:

Renaming Reserved Words

The converter adds an underscore after reserved words so EXEC SQL INCLUDE datetime.h becomes EXEC SQL INCLUDE datetime_.h, which in this case is not useful. This behavior is useful where a column name is an Oracle reserved word and the Migration Workbench schema conversion renames the column by adding an underscore. Then COLUMN YEAR becomes YEAR_ in both the embedded SQL and schema creation part of the Migration Workbench.

Header Files

There may be differences between header files used in Informix Dynamic Server ESQL/C and Oracle Pro*C. You must remove Informix Dynamic Server specific files and replace them with the Oracle equivalent.

Using Multiple Connections For Different Transactions

The converter does not support multiple connections. It replaces more complicated Informix Dynamic Server CONNECT statements with a simple Oracle CONNECT statement. For information about how to manage multiple contexts in Oracle, see the following commands in the Oracle Pro*C documentation:

EXEC SQL CONTEXT ALLOCATE :ctx;
EXEC SQL CONTEXT USE :ctx;
EXEC SQL CONTEXT FREE :ctx;

Using Demonstration Code

To create a user in Oracle, enter the following commands:

>sqlplus SYSTEM/MANAGER
SQL>CREATE USER examp IDENTIFIED BY examp;
SQL>GRANT CONNECT, RESOURCE TO examp;
SQL>CONNECT examp/examp
SQL>CREATE TABLE emp (      EMPNO NUMBER(4), 
                        ENAME VARCHAR2(10),
                        JOB VARCHAR2(9),
                        MGR NUMBER(4),
                        HIREDATE DATE, 
                        SAL NUMBER(7,2), 
                        COMM NUMBER(7,2), 
                        DEPTNO NUMBER(2));
SQL>INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        SYSDATE, 1600,  300, 30);
SQL>INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        SYSDATE, 1250,  500, 30);
SQL>INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        SYSDATE,  2975, NULL, 20);
SQL>INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        SYSDATE, 5000, NULL, 10);
SQL>COMMIT;

The executable relies on a database populated by data. The following example shows how to produce an executable:

Precompile the code from Oracle Pro*C to a C file using the Oracle Pro*C executable proc esqlproc1.pc.
Compile the C code using a suitable development environment. The Oracle Pro*C example shipped by with Oracle contains project files for the Visual C++ development environment. The project contains details of the libraries and include files required to build a small executable based on Oracle Pro*C. The project file used is %ORACLE_HOME%\precomp\demo\proc\sample.dsp. You have to add the C file esqlproc1.c to the project.

For more information about Oracle Pro*C/C++, see the Pro*C/C++ Precompiler Programmer's Guide.