5
The ESQL/C to Oracle Pro*C Converter

This chapter describes the E/SQL to Pro*C Converter, its scope and some of its limitations in this initial release. The chapter 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 details beyond the core code, refer to "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) Extra ROLLBACK option available in Oracle. 
Please see the Oracle 8i users guide for more information. */
    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:

{
      char oracleid = '/';
      EXEC SQL CONNECT :oracleid; 

{
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. Refer to "Using Demonstration Code" for an example of how to populate the database. The following example shows how to produce an executable:

1. Precompile the code from Oracle Pro*C to a C file using the Oracle Pro*C executable proc esqlproc1.pc.
   
   
2. 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 on Oracle Pro*C/C++ refer to the Pro*C/C++ Precompiler Programmer's Guide.

The following shows sample output from the executable.

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:

$
SELECT login_no
INTO $login_no;

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:

EXEC SQL UPDATE employees
SET (emp_no, emp_name) =
(:emp_no, :emp_name)
WHERE emp_no == :old_emp_no;

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:

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

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:

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

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

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:

EXEC SQL FETCH NEXT cur
INTO :emp_no;

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:

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

EXEC SQL FETCH cur1;

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:

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;

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:

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

/* 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 on Oracle Pro*C including command line options for the preprocessor refer to 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 on stored procedures refer to the "Triggers, Packages, and Stored Procedures" chapter. Additional errors and warnings are explained in "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 lists details of possible error messages.

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

ESQL-CONV-ERR:(EXEC SQL ..) The 
converter will not parse this 
expression correctly.

SPCONV-ERR:(**) Statement 
ignored. Manual conversion 
required.

ESQL-CONV-ERR:(CONNECT) Manual 
conversion of the username, 
password and database required.

ESQL-CONV-ERR:(DATABASE) Manual 
conversion of the username, 
password and database required.

SPCONV-ERR:(ALTER INDEX) 
Statement ignored. Manual 
conversion may be required.

/* SPCONV-MSG:(EXEC SQL ..) The 
converter will not parse this 
expression correctly. */
/**********************

ERROR STATEMENT COMMENTED 
***********************
exec sql <Statement not parsed 
goes here>
*******************************/

/* ESQL-CONV-ERR:(DYNAMIC SQL) 
Conversion not supported in this 
release. Manual conversion may 
be required. */

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.

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

ESQL-CONV-MSG:(CLOSE) Statement 
not required when compilation 
mode = ORACLE.

ESQL-CONV-MSG:(WHENEVER) Oracle 
supports additional directives.

ESQL-CONV-MSG:(INTO) Clause 
removed from cursor definition 
and integrated into FETCH 
statement.

ESQL-CONV-MSG:(DYNAMIC SQL) 
Unsupported in this release. 
Manual conversion may be 
required.

/* SPCONV-MSG:(**) Statement 
passed to DDL file. */

/* SPCONV-WRN:(=>) Oracle 
requires Positional parameter 
notation to precede Named 
parameter notation. Manual 
conversion required.*/

/* ESQL-CONV-MSG:(CLOSE) 
Statement not required when 
compilation mode = ORACLE. */

/* ESQL-CONV-MSG:(COMMIT) 
Statement will automatically 
close all cursors referenced in 
a CURRENT OF clause when 
compilation mode = ORACLE. Other 
cursors are unaffected. */

"/* ESQL-CONV-MSG:(COMMIT) 
Statement will automatically 
close all explicit cursors when 
compilation mode = ORACLE. */

/* SPCONV-MSG:(CONTINUE **) 
Statement emulated using GOTO 
statement and LABEL 
definition.*/

/* SPCONV-MSG:(WITH RESUME) 
Collating results for REF CURSOR 
return.*/

/* SPCONV-MSG:(WITH RESUME) 
Initialising GLOBAL TEMPORARY 
TABLE used to store Procedures 
interim results. */

/* ESQL-CONV-MSG:(SCROLL) Scroll 
cursors not available in Oracle. 
Manual conversion may be 
required. */

/* ESQL-CONV-MSG:(WITH HOLD) 
Unsupported in Oracle. Manual 
conversion may be required. */

/* ESQL-CONV-MSG:(INTO) Clause 
removed from cursor definition 
and integrated into FETCH 
statement. */

/* ESQL-CONV-MSG:(SELECT FIRST 
n) Emulated using FOR clause in 
FETCH statement. Manual 
conversion may be required. */

/* ESQL-CONV-MSG:(MODE=ANSI) 
ANSI compliant variable 
declaration generated. */

/*ESQL-CONV-MSG:(MODE=ORACLE) 
Non ANSI compliant variable 
declaration generated. */

/* SPCONV-MSG:(GLOBAL **) Global 
Variable definition moved to 
globalPkg Package.*/

/* SPCONV-MSG:(DROP DATABASE) 
Statement ignored. */

/* SPCONV-MSG:(DROP **) OMWB_
Emulation.DDL_MANAGER procedure 
used to execute DDL statement.*/

/* ESQL-CONV-MSG:(BEGIN .. END) 
Embedded PL/SQL code block 
generated for Stored Procedure 
call. */

/* ESQL-CONV-MSG:(SELECT) 
Statement illegal as a procedure 
parameter in Oracle. */

/* ESQL-CONV-MSG:(SELECT) 
Statement removed from procedure 
call. */

/* ESQL-CONV-MSG:(*) Manual 
conversion of the generated 
variable TYPE may be required.*/

/* ESQL-CONV-MSG:(INTO) 
Procedure call converted to 
function call as only one value 
returned. */

/* ESQL-CONV-MSG:(**) Statement 
emulated using Oracle FOR syntax 
within cursor declaration. */

/* ESQL-CONV-WRN:(**) Oracle has 
no equivalent cursor action. 
Manual conversion required.*/

/* ESQL-CONV-MSG:(INTO) Clause 
originally declared within 
cursor declaration. */

/* ESQL-CONV-MSG:(FREE) 
Statement not required in 
ORACLE. */

/* ESQL-CONV-MSG:(DYNAMIC SQL) 
Unsupported in this release. 
Manual conversion may be 
required.*/

/* ESQL-CONV-MSG:(GET 
DIAGNOSTICS) Manual conversion 
required.*/

/* SPCONV-MSG:(SUBQUERY) 
Subquery within IF statement 
emulated by using Boolean 
variable.*/

/* SPCONV-MSG:(LOCK TABLE) 
Please see 'Oracle 8i Server SQL 
reference' for details of other 
LOCK options.*/

/* SPCONV-MSG:(WITH RESUME) 
Statement emulated through use 
of GLOBAL TEMPORARY TABLES.*/

/* SPCONV-MSG:(RETURNING) 
Informix RETURNING clause 
parameters converted to Oracle 
OUT parameters.*/

/* ESQL-CONV-MSG:(ROLLBACK) 
Statement expanded to utilise 
Oracle SAVEPOINTS. */

/* ESQL-CONV-MSG:(ROLLBACK) 
Statement automatically closes 
all cursors referenced in a 
CURRENT OF clause when 
compilation mode = ORACLE. */

/* ESQL-CONV-MSG:(ROLLBACK) 
Statement closes all explicit 
cursors when compilation mode = 
ANSI. */

/* ESQL-CONV-MSG:(ROLLBACK) 
Extra ROLLBACK option available 
in Oracle. Please see the Oracle 
8i users guide for more 
information. */

/* ESQL-CONV-MSG:(OUTER) Simple 
OUTER joins may not be fully 
converted. Manual conversion may 
be required. */

/* ESQL-CONV-MSG:(MATCHES) 
Complex search patterns not 
fully converted. Manual 
conversion may be required. */

/* ESQL-CONV-MSG:(NOWAIT) 
Keyword added to emulate 
Informix SET LOCK MODE 
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. */

/* SPCONV-MSG:(SET DEBUG FILE) 
OMWB_emulation.utilities Package 
introduced to mimic Informix 
functionality.*/

/* ESQL-CONV-MSG:(NOT WAIT) 
Option may be emulated by 
implementing the Oracle NOWAIT 
SELECT statement option. */

/* SPCONV-MSG:(SYSTEM) Emulating 
Informix SYSTEM statement by 
using OMWB_emulation.SHELL 
Procedure.*/

/* SPCONV-MSG:(TRACE) OMWB_
emulation.utilities Package 
introduced to mimic Informix 
functionality.*/

/* ESQL-CONV-MSG:(ONLY) No 
equivalent available in Oracle. 
Statement ignored. */

/* SPCONV-MSG:(UPDATE 
STATISTICS) Statement ignored.*/

/*ESQL-CONV-MSG:(WHENEVER) 
Oracle supports additional 
directives. */

/*SPCONV-WRN:(ALTER TABLE) 
Unable to convert ALTER TABLE 
statement. Manual conversion 
required*/

/* SPCONV-WRN:(TEMP TABLE): It 
will be more performant to 
create the temporary table 
separately .*/

/* SPCONV-WRN:(**) Conversion of 
remote Database links not 
supported. Manual conversion 
required. */

/* SPCONV-WRN:(REFERENCES BYTE) 
Converted to Oracle BLOB 
datatype. Restrictions apply.*/

/* SPCONV-WRN:(REFERENCES TEXT) 
Converted to Oracle CLOB 
datatype. Restrictions apply.*/

/* SPCONV-MSG:(DROP **) 
Statement passed to DDL file.*/

/* SPCONV-WRN:(DBINFO) Unable to 
convert function call. Manual 
conversion required.*/

/* SPCONV-WRN:(**) Manual 
conversion required if the 
procedure returns more than one 
value.*/

/* SPCONV-WRN:(EXCEPTION) 
Emulation of Informix Exceptions 
incomplete.*/

/* ESQL-CONV-WRN:(RECOVER) 
Statement Ignored. */

/* SPCONV-WRN:(RETURN) Collating 
results for REF CURSOR return.*/

/* SPCONV-WRN:(FOR READ ONLY) 
Statement Ignored. */

/* SPCONV-WRN:(SYSTEM) Statement 
Ignored. Parse option turned 
off.*/

/* SPCONV-WRN: ( TRACE **) 
Currently not supported. Manual 
conversion required.*/

/* SPCONV-WRN:(MATCHES) 
Converted to an Oracle LIKE, 
BETWEEN or IN statement. Manual 
conversion of the search pattern 
may be required.*/

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 on how to manage multiple contexts in Oracle, refer 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:

1. Precompile the code from Oracle Pro*C to a C file using the Oracle Pro*C executable proc esqlproc1.pc.
   
   
2. 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 on Oracle Pro*C/C++ refer to the Pro*C/C++ Precompiler Programmer's Guide.