Skip Headers
Oracle® Migration Workbench Reference Guide for Informix Dynamic Server Migrations
Release 10.1.0 for Microsoft Windows 98/2000/NT/XP and Linux x86
Part No. B16022-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

3 Triggers, Packages, and Stored Procedures

This chapter compares the Informix Dynamic Server and Oracle triggers, packages, and stored procedures. Informix Dynamic Server stores triggers and stored procedures with the server. Oracle stores triggers and stored subprograms with the server. Oracle has three different kinds of stored subprograms: functions, stored procedures, and packages. For more information about all of these objects, see the PL/SQL User's Guide and Reference. This chapter includes the following:

Triggers

Triggers provide a way of executing PL/SQL code on the occurrence of specific database events. For example, you can maintain an audit log by setting triggers to fire when insert or update operations are carried out on a table. The insert and update triggers add an entry to an audit table whenever the table is altered.

The actions that Informix Dynamic Server triggers perform are constrained to multiple insert, update, delete, and execute procedure clauses; whereas, Oracle allows triggers to execute arbitrary PL/SQL code. Oracle triggers are similar to stored procedures in that they can contain declarative, execution, and exception handling code blocks.

Additionally, Oracle enables triggers to be invoked by many events other than table insert, update and delete operations. However, there are restrictions.

For more information about trigger restrictions, see the Oracle9i Application Developer's Guide - Fundamentals.

Mapping Triggers

All Informix Dynamic Server trigger types have an equivalent Oracle trigger type. The converter takes the optional WHEN clause in Informix Dynamic Server and converts it to an IF clause. This is shown in the following example:

Informix Dynamic Server SPL

create trigger t_traffic 
update of comments 
on msg_traffic 
referencing new as new 
for each row 
when (new.msg_id>10000) 
 (update msg_traffic set msg_traffic.update_dt = CURRENT year to fraction(3) 
  where (((msg_id = new.msg_id ) AND (msg_source = new.msg_source ) ) 
  AND (sub_msg_id = new.sub_msg_id ) ) ); 

Oracle PL/SQL

CREATE OR REPLACE TRIGGER t_traffic 
BEFORE UPDATE OF comments ON msg_traffic 
REFERENCING NEW as new_ FOR EACH ROW 
BEGIN 
DECLARE 
ItoO_selcnt         NUMBER; 
ItoO_rowcnt         NUMBER; 
BEGIN 
 IF :new_.msg_id > 10000 THEN 
 UPDATE msg_traffic 
 SET msg_traffic.update_dt  =  SYSDATE 
 WHERE ( ( ( msg_id = :new_.msg_id ) 
 AND ( msg_source = :new_.msg_source ) ) 
 AND ( sub_msg_id = :new_.sub_msg_id ) ); 
 END IF; 
  END; 
END; 

Informix Dynamic Server declares triggers on a per table basis with BEFORE and AFTER triggers held together in a single trigger declaration. In Oracle, the BEFORE and AFTER triggers are declared separately. Therefore, the convertor creates multiple Oracle triggers when parsing Informix Dynamic Server per table trigger code.

In the initial release, the Oracle triggers display one after the other in the same text area. The Oracle triggers require manual intervention to build on the Oracle destination database.

Mutating Tables

When you are using Oracle, the trigger or function may cause a mutating table error. This causes you to receive the following error message while executing the trigger:

ORA-04091: table SCOTT.Emp_tab is mutating, trigger/function may not see it.

If you receive this error, you need to manually alter the trigger so that the per row information is stored in an interim PL/SQL table. It is then copied into the destination table after the per row triggers have been fired. For more information about containing tables, see the following Web site:

http://otn.oracle.com/tech/migration/workbench/htdocs/mutating.htm

Packages

Packages are PL/SQL constructs that enable the grouping of related PL/SQL objects, such as procedures, variables, cursors, functions, constants, and type declarations. Informix Dynamic Server does not support the package construct.

A package can have two parts: a specification and a body. The specification defines a list of all objects that are publicly available to the users of the package. The body defines the code that is used to implement these objects, such as, the code behind the procedures and functions used within the package.

The general PL/SQL syntax for creating a package specification is:

CREATE [OR REPLACE] PACKAGE package_name {IS | AS}
  procedure_specification
..function_specification
..variable_declaration
..type_definition
..exception_declaration
..cursor_declaration
END [package_name];

The general PL/SQL syntax for creating a package body is:

CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}
..procedure_definition
..function_definition
..private_variable_declaration
..private_type_definition
..cursor_definition
[BEGIN
  executable_statements
[EXCEPTION
..exception_handlers]]
END [package_name];

The package body is optional. If the package contains only variable, cursor and type definitions then the package body is not required.

As the package specification is accessible to users of the package, it can be used to define global variable definitions within PL/SQL.

The Migration Workbench automatically creates packages during the conversion process for the following reasons:

For more information about package creation, see the following sections:

For more information about package creation and use, see the PL/SQL User's Guide and Reference.

Stored Procedures

Stored procedures provide a powerful way to code application logic that can be stored on the server. Informix Dynamic Server and Oracle both use stored procedures. Oracle also uses an additional type of subprogram called a function.

The language used to code stored procedures is a database-specific procedural extension of SQL. In Oracle it is PL/SQL and in Informix Dynamic Server it is Informix Dynamic Server Stored Procedure Language (SPL). These languages differ considerably. However, most of the individual SQL statements and the procedural constructs, such as if-then-else, are similar in both languages.


Note:

The PL/SQL procedure examples included in the document are the actual output of the Migration Workbench. They are longer than the source Informix Dynamic Server SPL procedures because they are converted to emulate SPL functionality. When the PL/SQL procedures are written for equivalent Oracle functionality, the Output code is shorter.

The PL/SQL procedures, which the Migration Workbench generates, add appropriate comments to indicate the manual conversion required. In general, the Migration Workbench deals with the Informix Dynamic Server constructs in one of the following ways:

The following sections provide a comparison of Informix Dynamic Server and Oracle:

NULL as an Executable Statement

In some cases within stored procedure code, it may be necessary to indicate that no action should be taken. To accomplish this in Oracle, the NULL statement is used. Unlike Informix Dynamic Server, Oracle treats the NULL statement as executable within a PL/SQL code block. In Oracle the NULL statement does not perform an action. Instead, it forms a syntactically legal statement that serves as a placeholder.

Oracle places a NULL statement into PL/SQL code in the following situations:

  • When converting a CONTINUE statement within a FOR, FOREACH, or WHILE LOOP construct is encountered.

  • When encountering an unsupported SPL statement.

For information about how the converter uses NULL statements, see the following sections:

Parameter Passing

An Informix Dynamic Server stored procedure contains the following logical parts:

  1. Procedure name

  2. Parameters area

  3. Returning section

  4. Statement block

  5. Document section

  6. With listing directive

Parts two and three define how data is passed to and from a stored procedure. Part two ties data values that are passed by the client to variable names.

Part three is optional. It defines a listing of the data types that the stored procedure returns to the client or calling environment.

The following example demonstrates parts one, two and three: the Informix Dynamic Server stored procedure code for the procedure name, parameters area, and the returning section.

Informix Dynamic Server SPL

/* Procedure name */
CREATE PROCEDURE bal_enquiry( 
/* The Parameters area */
cust_id   NUMBER,
account_num NUMBER) 
/* The Returning section */
RETURNING NUMBER;

Unlike Informix Dynamic Server, Oracle does not require the use of a Returning section. Instead, Oracle passes values to the stored procedure and from the stored procedure by using IN, OUT or IN OUT parameter modes.

In a similar way to Informix Dynamic Server, PL/SQL parameters within Oracle can have default values assigned to them.

Oracle Parameter Passing Modes

The modes for Oracle formal parameters are IN, OUT, or IN OUT. If a mode is not specified for a parameter, it defaults to the IN mode. Table 3-1 describes parameter modes within Oracle.

Table 3-1 Parameter Passing Modes in Oracle

Mode Description
IN The value of the parameter is passed into the procedure when the procedure is invoked. It is similar to read-only
OUT Any value the parameter has when it is called is ignored. When the procedure finishes, any value assigned to the parameter during its execution is returned to the calling environment. It is similar to write-only
IN OUT This mode is a combination of both IN and OUT. The value of the parameter can be passed into the procedure when the procedure is invoked. It is then manipulated within the procedure and returned to the calling environment. It is similar to read-write

Input Parameters

Informix Dynamic Server uses all parameters defined within the parameters area to pass values into the stored procedure. These parameters cannot pass data back to the client. If a default value is included for each variable, clients that execute the procedure do not have to send data to the procedure. Each parameter within the parameters area can, therefore, be converted to a functionally equivalent Oracle IN parameter. An example of an Informix Dynamic Server SPL procedure definition and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

CREATE PROCEDURE informix.update_bal(
cust_id INT,
amount  INT DEFAULT 1)

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE "INFORMIX".update_bal(
cust_id	_IN        NUMBER,
amount_IN         NUMBER DEFAULT 1) AS
BEGIN
cust_id           NUMBER := cust_id_IN;
amount            NUMBER := amount_IN;

Output Parameters

You use the Informix Dynamic Server returning section to define a list of data types to be returned to the client. If you use a returning section, the type and number of data values listed after the RETURN statement must match what was declared in the returning clause. The RETURN statement only sends one set of results back to the calling environment. If multiple contiguous sets of results need to be returned then you can add the WITH RESUME keywords.

If you use the WITH RESUME keywords, after the RETURN statement executes, the next invocation of the procedure starts at the statement that directly follows the RETURN statement.

If a procedure is defined using a WITH RESUME clause, a FOREACH loop within the calling procedure or program must call the procedure. In Informix Dynamic Server, a procedure returning more than one row or set of values is called a cursory procedure.

In effect, Informix Dynamic Server stored procedures have to be invoked repeatedly should multiple values need to be passed back to the calling environment. So n invocations returns n sets of contiguous singleton results.

If the Informix Dynamic Server stored procedure does not contain a WITH RESUME clause, it has been designed to be invoked only once and, optionally, send singleton values back to the calling environment.

In this case, all returning section parameters are converted to be OUT parameters within the generated Oracle PL/SQL code.

If a WITH RESUME statement is present within the Informix Dynamic Server stored procedure, then the Migration Workbench uses each returning clause parameter to build a global temporary table to store the procedures interim results. The Migration Workbench then uses this temporary table to build and return a populated cursor to the calling environment.

For more information about the strategy the Migration Workbench employs to convert the Informix Dynamic Server returning section to PL/SQL, see the following sections:

Individual SPL Statements

Both Informix Dynamic Server and Oracle use a database-specific procedural extension of SQL as their procedural language. However, the languages are not common so it is necessary that Migration Workbench emulates Informix Dynamic Server functionality that is not found in Oracle within the converted stored procedure PL/SQL code.

The following statements or constructs have to be, to a varying degree of complexity, emulated within the generated Oracle PL/SQL code:

Returning Section

The Informix Dynamic Server returning section is used to define the list of data types being returned to the client. The way the Migration Workbench converts the Returning section is determined by whether the RETURN WITH RESUME statement resides within the Informix Dynamic Server stored procedure. The Migration Workbench converts the returning section using one of the following methods:

Informix Dynamic Server Procedures Containing no WITH RESUME Clause

If only one parameter is specified in the Informix Dynamic Server returning section and the procedure contains no WITH RESUME clause, then Migration Workbench converts the procedure to an Oracle FUNCTION. An example of a procedure returning one value in Informix Dynamic Server and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

CREATE PROCEDURE "informix".add_category(
name like Recipecategory.category_name, 
desc like recipeCategory.category_desc)
RETURNING integer;

Oracle PL/SQL

CREATE OR REPLACE FUNCTION informix.add_category(
	name_IN                Recipecategory.category_name%TYPE,
	desc__IN               recipeCategory.category_desc%TYPE) 
RETURN NUMBER AS

If multiple returning parameters are defined within the Informix Dynamic Server returning section and the procedure contains no WITH RESUME clause, Migration Workbench converts each returning parameter to an Oracle OUT parameter. An example of a procedure returning multiple singleton values and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

CREATE PROCEDURE "root".ocsa_list_total(sp_order_id INT)
    RETURNING DECIMAL(9,4), DECIMAL(9,4),
              DECIMAL(9,4), DECIMAL(10,4);
/* Other statements, one of which is of type
   RETURN <decimal>, <decimal>, <decimal>, <decimal>; */

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE root.ocsa_list_total(
	sp_order_id_IN         NUMBER,
	/* SPCONV-MSG:(RETURNING) Informix RETURNING clause parameters converted to Oracle OUT parameters. */
	OMWB_outParameter1  OUT NUMBER,
	OMWB_outParameter2  OUT NUMBER,
	OMWB_outParameter3  OUT NUMBER,
	OMWB_outParameter4  OUT NUMBER) AS

Informix Dynamic Server Procedures Containing a WITH RESUME Clause

The method used to pass sets of results back to the client in Oracle differs considerably from the one used in Informix Dynamic Server.

Oracle stored procedures are only ever invoked once in order to return multiple sets of results and therefore PL/SQL does not contain any such WITH RESUME construct

Multiple sets of data are returned to the calling environment through the use of OUT or IN OUT parameters of type REF CURSOR. This cursor variable is similar to the user-defined record type and array type. The cursor stored in the cursor variable is like any other cursor. It is a reference to a work area associated with a multi-row query. It denotes both the set of rows and a current row in that set. The cursor referred to in the cursor variable can be opened, fetched from, and closed just like any other cursor. Since it is a PL/SQL variable, it can be passed into and out of procedures like any other PL/SQL variable.

If the Informix Dynamic Server stored procedure contains a WITH RESUME clause, the procedure is classed as a cursory procedure, a procedure that returns a result set. Each parameter defined within the procedures returning section is then used to construct a global temporary table uniquely associated with the procedure. This global temporary table is then used to store the procedures interim results.

The following Informix Dynamic Server code causes the converter to create a temporary table named get_slistTable. This table is then used to store the interim results of the procedure.

Informix Dynamic Server SPL

CREATE PROCEDURE "root".get_slist(
v_uid like PHPUser.user_id, 
v_listid like ShoppingList.list_id)
returning integer, char(75), char(255);

/* Other stored procedure statements one of which is of type 
   RETURN <integer>, <char>, <char> WITH RESUME                                           */

END PROCEDURE;

Oracle PL/SQL temp table Definition

CREATE GLOBAL TEMPORARY TABLE get_slistTable(
/* The first column 'col00' is used to create an ordered
    SELECT statement when populating the REF CURSOR
    OUT parameter to the procedure */
col00  NUMBER,
col01  NUMBER,
col02  CHAR(75),
col03  CHAR(255))
ON COMMIT DELETE ROWS;

The converter then adds an OUT parameter whose type is derived from a packaged WEAK REF CURSOR type to the PL/SQL stored procedure parameter list. For example:

CREATE OR REPLACE PROCEDURE root.get_slist(
	v_uid_IN               informix.PHPUser.user_id%TYPE,
	v_listid_IN            
informix.ShoppingList.list_id%TYPE,
/* The following cursor is added to the procedure by the converter */
	OMWB_ret_cv            OUT
AS

Using a cursor variable in this way in PL/SQL emulates the Informix Dynamic Server cursory procedure. The main difference from Informix Dynamic Server SPL is that the PL/SQL procedure is invoked only once and it returns a cursor variable containing the complete set of results.

For more information, see the following:

DOCUMENT Clause

The DOCUMENT clause enables a synopsis or description of the Informix Dynamic Server stored procedure to be detailed. The text contained after the DOCUMENT keyword is inserted into the Informix Dynamic Server sysprocbody system catalogue during the procedures compilation. This text can then be queried by the users of the stored procedure. Oracle PL/SQL has no such DOCUMENT clause.

The Migration Workbench converts the Informix Dynamic Server DOCUMENT clause to a multi-line comment within the PL/SQL stored procedure. This is demonstrated by the following example:

Informix Dynamic Server SPL

create procedure "informix".min_two(first integer, scd integer) 
returning integer; 
 if (first < scd) then 
  return first; 
 else 
  return scd; 
 end if; 
end procedure 
DOCUMENT 'The following procedure accepts two INTEGER values and returns the smallest of the two.'; 

Oracle PL/SQL

CREATE OR REPLACE FUNCTION informix.min_two( 
 first_IN               NUMBER, 
 scd_IN                 NUMBER) RETURN NUMBER AS 

/* 
'The following procedure accepts two INTEGER values and returns the smallest of the two.' 
*/ 

first               NUMBER(10) := first_IN; 
scd                 NUMBER(10) := scd_IN; 
ItoO_selcnt         NUMBER; 
ItoO_rowcnt         NUMBER; 

BEGIN 
 IF ( first < scd ) THEN 
  RETURN first; 
 ELSE 
  RETURN scd; 
 END IF; 
END min_two;
 

GLOBAL Variable Declarations

Informix Dynamic Server enables the definition of GLOBAL variables by using the GLOBAL keyword within the variable declaration. For example:

Informix Dynamic Server SPL

DEFINE GLOBAL gl_var INT;

This specifies that the GLOBAL variable gl_var is available to other procedures running within the same session. The first declaration of the GLOBAL variable establishes it within the Informix Dynamic Server global environment. Subsequent definitions of the same GLOBAL variable, within other procedures, are ignored.

The first procedure to define the GLOBAL variable can also set its initial value through the use of the DEFAULT clause. For example:

Informix Dynamic Server SPL

DEFINE GLOBAL gl_var INT DEFAULT 20;

If another stored procedure has already defined the GLOBAL variable within the global environment, the DEFAULT clause is ignored.

Therefore, if two procedures define the same GLOBAL variable with different DEFAULT values, the procedure executed first within the current session is the one that sets the GLOBAL variable's initial value.

Informix Dynamic Server GLOBAL variables can be emulated in Oracle by defining the variables within a package.

Variables defined within a package specification are available to the users of the package. The package specification emulates the per-session Informix Dynamic Server global environment.

Two Informix Dynamic Server procedures and the converted equivalent in Oracle are as follows.

Informix Dynamic Server SPL

CREATE PROCEDURE proc01()
		DEFINE GLOBAL gl_var INT DEFAULT 10;
	LET gl_var = gl_var + 1;
END PROCEDURE;

CREATE PROCEDURE proc02()
	DEFINE GLOBAL gl_var INT DEFAULT 20;
	LET gl_var = gl_var - 5;
END PROCEDURE;

Oracle PL/SQL Package

CREATE OR REPLACE PACKAGE informix.globalPkg AS
  gl_var  NUMBER;
END globalPkg;

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE informix.proc01 AS
BEGIN
  IF(globalPkg.gl_var IS NULL) THEN
    globalPkg.gl_var := 10; /* Only set default if value is NULL */
  ENDIF;
  globalPkg.gl_var := globalPkg.gl_var +1;
END proc01;

CREATE OR REPLACE PROCEDURE informix.proc02 AS
BEGIN
  IF(globalPkg.gl_var IS NULL) THEN
    globalPkg.gl_var := 20; /* Only set default if value is NULL */
  ENDIF;
  globalPkg.gl_var := globalPkg.gl_var -5;
END proc02;

In the previous example, if proc01 is executed first, the procedure checks if the value of the globalPkg.gl_out packaged variable is NULL. As this is the first time the package has been initialized, the variable contains a NULL value, therefore proc01 sets the value of the globalPkg.gl_var variable to 10 before adding 1 to the value within the statement block. If proc02 is then executed, the procedure again checks to see if the globalPkg.gl_var packaged variable has a NULL value. As proc01 has previously set this variable (initially to 10 and then to 11), the boolean IF statement condition within proc02 IF(globalPkg.gl_var IS NULL) does not return true and the value of 20 is not set. proc02 then subtracts 5 from the current value of the variable, setting its final value to 6.

If proc02 is executed first, it checks if the value of the globalPkg.gl_out variable is NULL. As this is the first time the package has been initialized, the variable contains a NULL value, therefore proc02 sets the value of the globalPkg.gl_out variable to 20 before subtracting 5 from the value within the statement block. If proc01 is then executed, the procedure again checks to see if the globalPkg.gl_out variable has a NULL value. As proc02 has previously set this variable (initially to 20 and then to 15), the boolean IF statement condition IF(INFORMIX.gl_var IS NULL) returns false, therefore, the value of 10 is not set. proc01 then adds 1 to the current value of the variable, setting its final value to 16.

Both the converted procedures reflect the same functionality found within the original Informix Dynamic Server procedures.

LIKE and MATCHES Comparison Conditions

Informix Dynamic Server uses the LIKE and MATCHES comparison conditions to test for matching character strings. Oracle has only one of these pattern-matching constructs, the LIKE clause. The Informix Dynamic Server and Oracle LIKE clauses are functionally identical and so no conversion of the original pattern is required.

The Informix Dynamic Server specific MATCHES clause works in a similar way to the LIKE clause. The only difference between the two types of clause is in the range of pattern-matching wildcard characters available for use. A comparison of the Informix Dynamic Server MATCHES and Oracle LIKE wildcard operators are displayed in tables Table 3-2 and Table 3-3.

Table 3-2 Informix Dynamic Server SPL MATCHES Clause Wildcard

Wildcard Description
*
Matches 0 or more characters
? Matches any single character.
\
Removes the special significance of the next character used.
[..] Matches any of the enclosed characters.
^ When used within the [..] wildcard operator, it matches any character not specified within the [..] character range

Table 3-3 Oracle PL/SQL LIKE Clause Wildcards

Wildcard Description
%
Matches 0 or more characters
_
Matches any single character

If the [..] pattern matching operator is not used within the original pattern, the Migration Workbench takes one of the following actions when it encounters a MATCHES clause:

  • The MATCHES keyword is converted to the Oracle LIKE keyword.

  • All ? characters within the original pattern are converted to functionally equivalent _ characters.

  • All * characters within the original pattern are converted to functionally equivalent % characters.

If the [..] pattern matching operator is used within the original pattern and a character range is specified, the Migration Workbench converts each MATCHES clause that it encounters to a BETWEEN clause.

If the [..] pattern matching operator is used within the original pattern and no character range has been specified, the Migration Workbench converts each MATCHES clause it encounters to an Oracle IN clause.

The following table presents example Informix Dynamic Server MATCHES clauses and the converted Oracle equivalent:

MATCHES Statements Conversion Results
MATCHES '[A-Z]	'
BETWEEN 'A' AND 'Z'
MATCHES '[abcdefg]'	'
IN ('a','b','c','d','e','f','g')
MATCHES '*tennis*	'
LIKE '%tennis%'
MATCHES '?ennifer*	'
LIKE '_ennifer%'
MATCHES '[^qwerty]	'
NOT IN ('q','w','e','r','t','y')
MATCHES '[^a-z]	
NOT BETWEEN 'a' AND 'z'

If the Migration Workbench can not fully convert an Informix Dynamic Server MATCHES clause, it takes the following actions:

  1. Generates a warning within the converted PL/SQL stored procedure code.

  2. Converts the Informix Dynamic Server MATCHES keyword to the PL/SQL LIKE keyword.

  3. The original pattern remains unchanged.

It is therefore necessary for you to manually convert any search pattern not handled by the Migration Workbench.

FOR LOOP Constructs

Informix Dynamic Server allows a number of FOR LOOP constructs that Oracle does not support. The most difficult of these to convert to Oracle is a FOR LOOP that mixes RANGE and EXPRESSION LISTs within the same iteration definition. In PL/SQL, it is necessary to split each defined iteration range into its own unique FOR LOOP or functionally equivalent PL/SQL code block.

In the following example, the converter splits the original Informix Dynamic Server FOR LOOP construct into four functionally equivalent PL/SQL code blocks. One PL/SQL code block for each iteration range defined within the Informix Dynamic Server FOR LOOP construct. An example of an Informix Dynamic Server FOR LOOP construct and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

CREATE PROCEDURE forloop_example()
	DEFINE iterator_var, j INT;
	LET j = 10;
	FOR iterator_var IN (
		/* A range definition */
		1 TO 20 STEP 2,
 		/* a SELECT statement */
		(SELECT aval from atable where avalid = j),
		/* An expression range definition */
		j+10 TO j-20,
		/* A singleton value */
		1000)
  		INSERT INTO testtable VALUES(iterator_var);
	END FOR;
END PROCEDURE;

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE forloop_example AS
iterator_var        NUMBER(10);
j                   NUMBER(10);
ItoO_selcnt         NUMBER;
ItoO_rowcnt         NUMBER;

CURSOR cursor1 IS 
	SELECT aval 
	FROM atable 
	WHERE avalid = j;
BEGIN
	j := 10; 
/* A range definition */
	iterator_var := 1;
	LOOP
		INSERT INTO testtable 
		VALUES(iterator_var);
		iterator_var := iterator_var + 2;
		EXIT WHEN (iterator_var >= 20);
	END LOOP; 
/* A SELECT statement */	
FOR cursor1Record IN cursor1 LOOP
		iterator_var := cursor1Record.aval;
		INSERT INTO testtable 
		VALUES(iterator_var);
	END LOOP; 
/* An expression range definition */	
FOR iterator_var IN j + 10 .. j - 20 LOOP 		
		INSERT INTO testtable 
		VALUES(iterator_var);
	END LOOP;
/* A singleton value */     
iterator_var := 1000;
INSERT INTO testtable 
	VALUES(iterator_var);
END forloop_example;

FOREACH LOOP Constructs

An Informix Dynamic Server FOREACH LOOP is the equivalent of a PL/SQL cursor. When an Informix Dynamic Server FOREACH statement executes, the database server:

  1. Declares and implicitly opens a cursor.

  2. Obtains the first row from the query contained within the FOREACH LOOP or it obtains the first set of values returned by the procedure.

  3. Assigns each variable in the variable list the value of the corresponding value from the active set that the SELECT statement or called cursory procedure returns.

  4. Executes the statement block.

  5. Fetches the next row from the SELECT statement or procedure on each iteration and repeats steps 3, 4, and 5.

  6. Terminates the loop when it finds no more rows that satisfy the SELECT statement or when no more data is returned from the procedure. The implicit cursor is closed when the loop terminates.

Within Informix Dynamic Server, FOREACH statements can be one of following types:

FOREACH .. SELECT .. INTO Statement

The Migration Workbench emulates FOREACH .. SELECT .. INTO statement in PL/SQL by converting the Informix Dynamic Server FOR EACH SELECT statement into a cursor definition. Then it iterates over the cursor contents, assigning the values within the current cursor row to the original list of variables defined within the SELECT INTO statement. Migration Workbench repeats this process until no more data is found. An example of a FOREACH..SELECT..INTO statement and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

DEFINE name VARCHAR2(30);
DEFINE address VARCHAR2(255);
FOREACH SELECT ename, eaddress INTO name, address FROM emp
  INSERT INTO mailing_list VALUES(name, address);
END FOREACH;

Oracle PL/SQL

/* Define original variables */
name VARCHAR2(30);
address VARCHAR2(255);

/* Declare a cursor using the original SELECT statement 
   Notice how the converter has now named the cursor within 
   PL/SQL */
CURSOR cursor1 IS
	SELECT ename, eaddress
	FROM emp;
BEGIN
	/* Open the previously declared (now) named cursor */
	OPEN cursor1;
	/* Iterate over the cursor contents */
	LOOP
		/* Fetch the values of the cursor's current row 
	   into the original variables */
		FETCH cursor1
		INTO name, 
     address;
		/* Exit the LOOP when no more data found */
		EXIT WHEN cursor1%NOTFOUND;
		/* The original statement block */
		INSERT INTO mailing_list 
VALUES(name, 
       address);
	END LOOP;
	/* Close the cursor */
	CLOSE cursor1;
END;

FOREACH CURSOR Statement

An Informix Dynamic Server FOREACH statement can contain an explicitly named cursor. This enables the use of the WHERE CURRENT OF clause within the statement block contained within the FOREACH construct. The Informix Dynamic Server FOREACH cursor statement is converted to PL/SQL in a similar way to the FOREACH.. SELECT .. INTO statement. The named cursor is defined within the PL/SQL procedure, opened, and the contents iterated over until no more data is found. A FOREACH CURSOR statement and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

CREATE PROCEDURE "informix".update_list
DEFINE name VARCHAR(30);
DEFINE address VARCHAR(255);
FOREACH namedCursor FOR
  SELECT ename, eaddress INTO name, address FROM emp
  INSERT INTO mailing_list VALUES(name, address);
  IF(ename="McAllister") THEN
    UPDATE emp SET sal = sal + 2000 WHERE CURRENT OF namedCursor;
    CONTINUE FOREACH;
  END IF
  UPDATE emp SET sal = sal + 1000 WHERE CURRENT OF namedCursor;
END FOREACH
END PROCEDURE

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE "informix".update_list AS

name                VARCHAR2(30);
address             VARCHAR2(255);
ItoO_selcnt         NUMBER;
ItoO_rowcnt         NUMBER;

CURSOR namedCursor IS 
	SELECT ename,
	       eaddress 
	FROM emp FOR UPDATE;
BEGIN
OPEN namedCursor;
LOOP
		FETCH namedCursor 
INTO name,
address;
EXIT WHEN namedCursor%NOTFOUND;
INSERT INTO mailing_list 
VALUES(name,
       address); 
	IF ( ename = 'McAllister' ) THEN
			UPDATE emp 
	    			SET sal  =  sal + 2000 
			    WHERE CURRENT OF namedCursor; 
			    /* SPCONV-MSG:(CONTINUE FOREACH) Statement emulated using GOTO     
       statement and LABEL definition. */
			    GOTO Continue_ForEach1;
		    END IF; 
		UPDATE emp 
		SET sal  =  sal + 1000 
		WHERE CURRENT OF namedCursor;
		<<Continue_ForEach1>>
		NULL;
	END LOOP;
	CLOSE namedCursor;
END update_list;

For more information about translating Informix Dynamic Server CONTINUE statements, see RAISE EXCEPTION Statements.

FOREACH Execute Procedure Statement

If a FOREACH execute statement is encountered by the convertor, it assumes the procedure being called is a cursory procedure. As cursory procedures are automatically converted to utilize PL/SQL REF CURSORS, the procedure being called always return a REF CURSOR as it's last parameter. This cursor variable contains the full set of results returned by the called stored procedures.

The Informix Dynamic Server FOREACH EXECUTE statement can be emulated by iterating over the contents of the cursor variable returned by the converted cursory procedure.

The following shows an example of the Informix Dynamic Server FOREACH EXECUTE statement repeatedly executing a cursory procedure bar() until no more results are returned and the converted equivalent in Oracle:

Informix Dynamic Server SPL

FOREACH EXECUTE PROCEDURE bar(100,200) INTO i
  INSERT INTO tab2 VALUES(i);
END FOREACH

Oracle PL/SQL

/* DEFINE a cursor variable of the correct type */
OMWB_cv1 OMWB_emulation.globalPkg.RCT1;

/* Cursor variable added to the call to procedure bar() */
bar(100,200,OMWB_cv1); 
/* Iterate over the cursor contents */
LOOP
  /* FETCH the contents into the original variable */
  FETCH OMWB_cv1 
  INTO i;
  /* EXIT the LOOP when no more data found */
  EXIT WHEN OMWB_cv1%NOTFOUND;
  /* execute statement block */
  INSERT INTO tab2 VALUES(i);
END LOOP;

Compound LET Statements

Informix Dynamic Server uses the LET statement to assign values to variables. PL/SQL only allows simple assignments that assign a single value to a single variable. Informix Dynamic Server SPL allows compound assignments that assign values to two or more variables within the same statement.

In order to convert compound LET statements into functionally equivalent PL/SQL code, the converter splits the Informix Dynamic Server compound assignment statement into logically equivalent simple assignment statements.

An example of both Informix Dynamic Server simple assignments and compound assignments and the converted equivalent in Oracle are as follows:

Informix Dynamic Server SPL

/* Simple assignment */
LET a = 10;				
/* Compound assignment */
LET b,c = 20,30;      			

Oracle PL/SQL

/* Simple assignment conversion*/
a := 10;
/* Compound assignment conversion*/
b := 20;
c := 30;

The two original Informix Dynamic Server LET statements have been converted into three logically equivalent PL/SQL statements. One PL/SQL statement for every variable used within both Informix Dynamic Server LET statements. Informix Dynamic Server also enables SELECT statements and PROCEDURE calls to assign values to variables within a LET statement.

Using SELECT Statements in LET Assignment Statements

Informix Dynamic Server enables the use of a SELECT statement as part of the LET statement assignment list.

The following shows an example of an Informix Dynamic Server SELECT statement as part of a LET statement assignment list and the converted equivalent in PL/SQL:

Informix Dynamic Server SPL

LET enum = (SELECT empnum FROM emp WHERE empname = "McAllister");

Oracle PL/SQL

SELECT empnum INTO enum FROM emp WHERE empname = 
'
McAllister
'
;

Calling Procedures in LET Assignment Statements

Informix Dynamic Server enables the use of a procedure call within a LET statement. The procedure may return more than one value into a list of variables.

An example of an Informix Dynamic Server procedure call that returns three values into the variables a, b, and c and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

LET a,b,c = someProc(100,200);

Oracle PL/SQL

someProc(100, 200, OMWB_outparameter1 => a, OMWB_outparameter2 => b, OMWB_outparameter3 => c);

The someProc procedure is converted to pass these values back as Oracle OUT parameters. These OUT parameters are explicitly named:

OMWB_outparameter<number>

Thus, if the original Informix Dynamic Server stored procedure returned n values, the converter adds n OUT parameters to the converted stored procedure, sequentially named OMWB_outparameter1 .. OMWB_outparametern.

An example of an Informix Dynamic Server LET statement that assigns a value to only one variable and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

LET a = anotherProc(200);

In the above example, the converter assumes that the procedure being called has been converted to a function within PL/SQL and convert the statement to read:

Oracle PL/SQL

a := anotherProc(200);

For more information about named and positional parameter passing notation, see the following:

Converting CONTINUE Statements

An Informix Dynamic Server CONTINUE statement is used to start the next iteration of the innermost FOR, FOREACH or WHILE loop. When a CONTINUE statement is encountered, the rest of the statements contained within the innermost LOOP of the innermost TYPE are skipped and execution continues at the next iteration of the LOOP.

Oracle PL/SQL does not contain a CONTINUE statement so Migration Workbench emulates the statement by using a PL/SQL LABEL definition and a code branching GOTO statement. This label is defined as the penultimate statement within the converted looping constructs statement block. As PL/SQL requires the statement directly following a label definition to be executable, Migration Workbench adds a NULL statement directly after the inserted label definition. The END LOOP PL/SQL statement is declarative, not executable, whereas, the NULL statement within PL/SQL is executable.

An example of an Informix Dynamic Server CONTINUE statement and its converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

CREATE PROCEDURE continue_test()
define indx  INT;
FOR indx IN 1 TO 10
  IF(indx = 5) THEN 
    CONTINUE FOR;
  END IF
  INSERT INTO tab VALUES(indx) ;
END FOR

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE continue_test AS
indx INTEGER;
BEGIN
  FOR indx IN 1 .. 10
    IF(indx = 5) THEN 
      /* The original Informix CONTINUE statement has been 
          replaced by a PL/SQL GOTO statement*/
      GOTO FOR_LABEL1;
    END IF
    /* Original statement block */
    INSERT INTO tab VALUES(indx) ;
    /* The following label definition are placed at the end of the 
        LOOP constructs statement block*/
    <<FOR_LABEL1>>
    /* Label definitions have to be followed by an executable
       statement. As PL/SQL treats the END LOOP statement as
       being declarative, a NULL statement is placed after
       the label definition. NULL statements within PL/SQL are 
       classed as being executable */ 
    NULL;
  END LOOP;
END;

Converting RETURN WITH RESUME Statements

Informix Dynamic Server enables procedures to return multiple sets of results by the inclusion of the WITH RESUME keywords after the RETURN statement. An Informix Dynamic Server procedure of this type is called a cursory procedure.

The result set returned by an Informix Dynamic Server cursory procedure is emulated within Oracle by adding a REF CURSOR variable to the parameter list of the converted PL/SQL procedure.

This cursor variable stores the complete set of results returned from the stored procedure.

An Oracle temporary table is used to return an identical set of results in an identical order within the PL/SQL procedure as would have been returned in the original Informix Dynamic Server procedure. This temporary table stores the interim results in an ordered sequence.

In the following Informix Dynamic Server example, the procedure returns every continuous integer value between 1 and 100, except the values between 49 and 61, in ascending order to the parent procedure or calling environment.

To successfully emulate the order that these results are returned in Informix Dynamic Server, the Migration Workbench creates a GLOBAL TEMPORARY TABLE specifically to store the interim procedure results. The Migration Workbench then converts the Informix Dynamic Server RETURN WITH RESUME statement to INSERT results into this temporary table. The Migration Workbench then uses the temporary table to populate the cursor returned to the calling environment.

An example of a RETURN WITH RESUME statement and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

CREATE PROCEDURE resume_test() RETURNING NUMBER;
indx INT;
FOR indx = 1 to 100 LOOP
  IF(indx > 49 and indx < 61) THEN
    CONTINUE FOR;
  END IF
  RETURN indx WITH RESUME;
END FOR;
END resume_test;

Oracle PL/SQL temporary table DDL statement

CREATE GLOBAL TEMPORARY TABLE resume_testTable(
/* The first column 'col00' is used to create an ordered 
    SELECT statement when populating the REF CURSOR
    OUT parameter to the procedure */
col00  NUMBER,
col01  NUMBER)
ON COMMIT DELETE ROWS;

Oracle PL/SQL Converted Procedure

CREATE OR REPLACE PROCEDURE resume_test(
/* Define the cursor used to pass back the complete list
   of results to the calling environment as an OUT    
   parameter  */
OMWB_ret_cv OUT OMWB_emulation.globalPkg.RCT1)
AS
indx INTEGER;
/* A counter is automatically added by the converter.    
   This is used to INSERT a sequential set of results 
   into the GLOBAL TEMPORARY TABLE resume_testTable. */ 
OMWB_resume_testSeq INTEGER := 0;
BEGIN
/* Clear the temporary table of old results at the start    
   of the procedure */
DELETE FROM resume_testTable;
FOR indx IN 1 .. 100 LOOP
  IF(indx > 49 and indx < 61) THEN
    /* CONTINUE statement emulated by using a GOTO
       statement and LABEL definition */
    GOTO FOR_LABEL1;
  END IF;
  /* Return with resume statement converted to INSERT the
     return data into this procedures GLOBAL TEMPORARY 
     TABLE. 
     The OMWB_resume_testSeq variable is used in order to 
     create a continuous sequence of values when ordering 
     the results for insertion into the return cursor 
     OMWB_ret_cv */
  INSERT INTO resume_testTable
  VALUES(OMWB_resume_testSeq,
         indx);
  /* Now we increment the sequence variable ready for the 
     next converted RETURN WITH RESUME statement */
  OMWB_resume_testSeq := OMWB_resume_testSeq + 1;
  /* Label definition used by the GOTO statement above */
  <<FOR_LABEL1>>
  NULL;
END LOOP;
/* The temporary table is then used to populate the 
   REF CURSOR we return to the calling environment. 
   The first column is used to return the results from 
   the select statement in an ordered fashion and is 
   never made part of the return data */
  OPEN OMWB_ret_cv FOR
  SELECT col01
  FROM resume_testTable
  ORDER BY col00;
END resume_test;

When the PL/SQL procedure in this example is called, it deletes past results from the associated temporary table of the procedure using the DELETE FROM syntax. For example:

Oracle PL/SQL

DELETE FROM resume_testTable;

The table is now void of results and ready for use within the procedure. The Informix Dynamic Server RETURN WITH RESUME statement is then converted to INSERT results into this temporary table. An INTEGER variable called:

OMWB_<procedure name>Seq

This is automatically added to the variable declaration section within the stored procedure. This variable is used to insert an ordered sequence number into the first column of the resume_testTable table.

To populate the cursor variable designed to return the results to the calling environment, the converter then adds an OPEN CURSOR .. FOR .. SELECT statement as the last executable line of the procedure. At this stage of the procedures execution, the temporary table is populated with a full set of results.

The first column of the temporary table is used within the ORDER BY section of the last SELECT statement to populate the cursor rows with the ordered temporary table data. The procedure completes execution and the populated cursor is returned to the calling environment.

Built-in Functions

Some built-in functions within Informix Dynamic Server are not available in Oracle. These functions are emulated within Oracle using the utilities package. Migration Workbench automatically creates this package within the destination Oracle database. It contains a suite of PL/SQL stored functions and procedures that mimic the functionality of the following Informix Dynamic Server built-in procedures:

  • HEX

  • DAY

  • MONTH

  • WEEKDAY

  • YEAR

  • MDY

  • TRACE

The Migration Workbench creates a new user within the destination Oracle database. The user name is OMWB_emulation and the password is oracle. This OMWB_emulation users schema stores the utilities package. To enable access to this package to all database users, the Migration Workbench executes the following statement:

Oracle PL/SQL

GRANT EXECUTE ON OMWB_emulation.utilities TO PUBLIC;

Every time the stored procedure converter encounters a reference to one of the unsupported built-in functions within the Informix Dynamic Server SPL code, it generates a reference to the equivalent emulation function within the OMWB_emulation users utilities package. An example of a SPL statement converted to reference the OMWB_emulation.utilities.HEX emulation function within Oracle is as follows:

Informix Dynamic Server SPL

LET a = HEX(255);

Oracle PL/SQL

a := OMWB_emulation.utilities.HEX(255);

With the exception of the Informix Dynamic Server TRACE function, all emulation functions have the same names as their Informix Dynamic Server counterpart. The TRACE statement is converted to reference a procedure named DEBUG within the OMWB_emulation.utilities package.


Caution:

It is imperative that you test the utilities package and all functions and procedures within before implementation in a production environment.

Converting the SYSTEM Statement

The SYSTEM statement enables operating system commands to be executed from within an Informix Dynamic Server stored procedure. For example:

Informix Dynamic Server SPL

SYSTEM ' ls -al /tmp/salary_upgrades > /tmp/salary_upgrades/totals.out';

Oracle does not have any such SYSTEM statement so it is necessary to emulate the Informix Dynamic Server SYSTEM functionality by using an Oracle external procedure. This external procedure is written in C and compiled into an executable. A stored procedure named SHELL is then associated with a call to the executable.

In essence, a call to the associated PL/SQL stored procedure actually invokes the compiled executable resident on the file system. This binary executable then performs the operating system command passed into the SHELL stored procedure. You need to manually compile this executable before emulation of the Informix Dynamic Server SYSTEM command can commence.

The Migration Workbench creates a placeholder PL/SQL stored procedure named SHELL within the OMWB_Emulation users schema. It then converts each Informix Dynamic Server SYSTEM statement to reference this placeholder procedure. For example, the previous SYSTEM statement is converted into the following PL/SQL code:

Oracle PL/SQL

OMWB_Emulation.SHELL(' ls -al /tmp/salary_upgrades >
/tmp/salary_upgrades/totals.out');

This placeholder procedure currently contains no executable code, it is a stub created within the destination database so that any procedure containing references to it does not fail compilation.

Oracle invalidates a stored procedure if any other stored procedure it references is itself invalid. Therefore, the stub procedure is required until the set-up tasks have been performed. If the stub procedure is invoked prior to the set-up tasks being performed, the string containing the operating system command is not executed.

Set-Up Tasks for Configuring the SHELL Procedure

In order to configure the SHELL procedure so that it executes the operating system command, you should first perform the following set-up tasks on the destination server:


Note::

The following set-up tasks are specific to Windows NT.

  1. Download and install the free Borland C++ compiler from the Web site at:

http://www.borland.com

  1. Create the file shell.c:

       ==============begin shell.c=================
       #include <windows.h>
       #include <stdio.h>
       #include <stdlib.h>
    
       void __declspec(dllexport) sh(char *);
       void sh(char *cmd)
       {
       system(cmd);
       }
    
       ============end shell.c======================
    
    
  2. Create a test program shell_run.c:

       =============begin shell_run.c===============
    
       void __declspec(dllimport)ch (char*);
    
       int main(int argc, char *argv[])
       {
       sh(argv[1]);
       return 0;
       }
    
       ============end shell_run.c==================
    
    
  3. Create and run shell_compile.bat that compiles and link shell.c and shell_run_c:

       ============begin shell_compile.bat =========
    
    bcc32 -WD shell.c -ID:\Borland\BCP55\include -LD:\Borland\BCP55\lib
    implib shell.lib shell.dll 
    bcc32 shell_run.c shell.lib -e shell_run.exe
    
       ============end shell_compile.bat ===========
    
    
  4. Test shell.dll by issuing the following command on the DOS prompt:

     C:\> shell_run "any operating system command"
    
    
  5. Configure the destination databases listener.ora and tnsnames.ora files for external procedures.

    For the configuration of external procedures, you need to define a tnsnames.ora entry: extproc_connection_data.

    When the server references an external-procedure, it looks into the tnsnames.ora file to find the listener address. The alias used is the hard-coded extproc_connection_data value. This alias contains the address of the listener process and the SID for the extproc agent. With this info, the server contacts the listener and the listener spawns the new extproc-process.

    Add the following entry to the tnsnames.ora file:

     EXTPROC_CONNECTION_DATA =
       (DESCRIPTION =
         (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
         )
         (CONNECT_DATA =
           (SID = PLSExtProc_817)
           (PRESENTATION = RO)
         )
       )
    
    

    Configure the listener.ora file, add an SID_DESC entry similar to the

    following:

     SID_LIST_LISTENER =
       (SID_LIST =
         (SID_DESC =
           (SID_NAME = PLSExtProc_817)
           (ORACLE_HOME = <ORACLE_HOME>)
           (PROGRAM = extproc)
         )
        )
    
    
  6. Create the external library and replace the stub OMWB_Emulation.SHELL wrapper procedure using SQL*Plus:

       SQL> create library shell_lib is 'shell.dll';
       SQL> create or replace procedure OMWB_emulation.SHELL (
       cmd IN varchar2)
       as external
       library shell_lib
       name "_sh"
       language C
       parameters (
       cmd string);
       /
    
    
  7. Test the external library from the SQL*Plus command line:

     SQL> exec shell('any operating system command');
    
    

The external procedure executes all operating system commands using Oracle permissions. For example, the following statement creates the hello.txt file within the /home/myname directory:

OMWB_emulation.SHELL('echo "Hello" > /home/myname/hello.txt'); 

The hello.txt file is owned by Oracle. To reassign the file to another user, you should alter the call to the SHELL procedure. For example:

OMWB_emulation.SHELL('echo "Hello" > /home/myname/hello.txt; chown myname hello.txt'); 

Converting TRACE Statements

The Informix Dynamic Server TRACE statement is used to control the generation of debugging output. The TRACE statement sends output to the file specified by the SET DEBUG FILE statement. Tracing within Informix Dynamic Server prints the current values of the following items:

  • Variables

  • Procedure arguments

  • Return values

  • SQL error codes

  • ISAM error codes

The Informix Dynamic Server TRACE statement can also be used to print expressions to the debug file using the syntax: TRACE expression. For example:

Informix Dynamic Server SPL

TRACE "This is a trace statement and is written out to the debug log";

All statements are traced within Informix Dynamic Server by the issue of the TRACE ON command. This implies that all statements and procedure calls are traced, such as the value of all variables before they are used and the return values of procedure calls. The Informix Dynamic Server statement TRACE OFF is used in order to turn tracing off. The TRACE <expression> statement can still be used even if the TRACE OFF statement has been issued.

The Migration Workbench only supports the conversion of the Informix Dynamic Server TRACE <expression> statement. All other TRACE statements cause the converter to flag a warning and output the original TRACE statement within the PL/SQL code as a single line comment along with an accompanying executable NULL statement. An example of an unsupported TRACE statement and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

TRACE PROCEDURE;

Oracle PL/SQL

/* SPCONV-WRN:(TRACE PROCEDURE) Statement not converted. Manual conversion required. */
--TRACE PROCEDURE;
NULL;

The TRACE <expression> statement is emulated using the DEBUG stored procedure resident within the utilities package. The DEBUG stored procedure is generated automatically by the Migration Workbench.

The DEBUG stored procedure enables the logging of debug messages to the console window using the DBMS_OUTPUT package, a table within the database or, using the UTL_FILE package, a flat file stored locally on the file system. The supplied DEBUG stored procedure logs messages to a table called debug_table by default.

The Migration Workbench converts all Informix Dynamic Server TRACE <expression> statements to reference the DEBUG stored procedure. For example:

Informix Dynamic Server SPL

TRACE "This is a trace statement and is written out to the debug log";

Oracle PL/SQL

OMWB_emulation.utilities.DEBUG('This is a trace statement and is written out to the debug log');

Informix Dynamic Server TRACE <expression> statements are used to build a log of systematic debug information. Because of this, converted TRACE expression statements can become a powerful quality assurance monitor. You can compare the logs produced by the original Informix Dynamic Server TRACE statements against the logs built by the converted statements within the destination Oracle database. This may aid in the unit testing of each converted stored procedure.

For a code sample of the complete utilities package, see Appendix A, "Code Samples".

Set Up Tasks for the DEBUG Procedure

The DEBUG procedure is designed by default to log messages to the debug_table resident under the OMWB_emulation user's schema. The following shows the DDL statement that the Migration Workbench uses to construct the debug_table:

Oracle PL/SQL

CREATE TABLE debug_table(
log_date       DATE,
log_user       VARCHAR(100),
log_message    VARCHAR(4000))

The Migration Workbench automatically creates and executes the appropriate database grants on this table. Therefore, in order to use the OMWB_emulation.utilities.DEBUG procedure, immediate set-up tasks are not necessary.

If you want to log all DEBUG messages to a flat file, you should first create a UTL_FILE_DIR entry within the init.ora initialization file of the destination Oracle database.

This init.ora parameter defines a list of directories into which the UTL_FILE package can write. The directories specified have to reside on the database servers local file system.

In the init.ora file, each accessible directory is stipulated by a line such as

utl_file_dir = D:\Oracle\Migration\Debug

The previous line enables the UTL_FILE package to write to files present within the D:\Oracle\Migration\Debug directory. Access to files within subdirectories is forbidden. You must explicitly define each directory within the init.ora file.

Using the DEBUG Procedure

After have added the UTL_FILE_DIR entries to the init.ora initialization file, you need to configure the DEBUG procedure. To do this, you alter the value of the following utilities package variables:

  • utilities.DebugOut

  • utilities.DebugFile:

  • utilities.DebugDir

The utilities.DebugOut variable is an integer value that indicates whether to log trace messages to a flat file, the console windrow, or a table within the database. You can set this variable programmatically within stored procedures by including the following line of PL/SQL code:

Oracle PL/SQL

OMWB_Emulation.utilities.DebugOut := <variable value>;

The variable value can be one of the following:

  • A value of 1 instructs the DEBUG procedure to log all converted trace messages to a file. The filename used is specified by the value of the utilities.DebugFile variable. The value of the utilities.DebugDir variable specifies the directory where this file is located.

  • A value of 2 instructs the DEBUG procedure to log all converted trace messages to the console window.

  • Any other value instructs the DEBUG procedure to log messages to a table named debug_table resident under the OMWB_Emulation users schema.

If the DEBUG procedure has been configured to log trace messages to a file, the value of the utilities.DebugFile variable determines the filename. You can set this variable programmatically within stored procedures by including the following:

OMWB_Emulation.utilities.DebugFile := <variable value>;

The value for this variable has to be a string expression that evaluates to a legal operating system filename. For more information about the utilities.DebugFile variable, see SET DEBUG FILE Statement.

If the procedure has been configured to log trace messages to a file, the variable value of the utilities.DebugDir variable determines the directory where the file is created. You can set this variable programmatically within stored procedures by including the following:

OMWB_Emulation.utilities.DebugDir := <variable value>;

The value for this variable has to be a string expression that evaluates to a legal operating system file path. The file path has to exist at runtime or an error is raised. Additionally, this file path must have a matching UTL_FILE_DIR entry.

For example, in order to configure a stored procedure to log converted trace messages to a file named procA.out within the D:\logs directory, include the following lines within the stored procedure code:

utilities.DebugOut := 1;
utilities.DebugFile := 'procA.out';
utilities.DebugDir := 'D:\logs\';

Alternatively, in order to log messages to the console window, include the following:

utilities.DebugOut := 2;

In order to log converted trace messages to the debug_table, set the utilities.DebugOut variable to any value except 1 or 2. Therefore, any one of the following three values is legal:

utilities.DebugOut := 3;
utilities.DebugOut := 300000;
utilities.DebugOut := NULL;

SET DEBUG FILE Statement

Informix Dynamic Server uses the SET DEBUG FILE statement to indicate the file where TRACE messages are logged. The Migration Workbench emulates the Informix Dynamic Server TRACE statement by using the utilities.DEBUG procedure. This PL/SQL stored procedure offers an option that enables you to log debug messages to a flat file stored locally on the file system.

If the DEBUG procedure has been configured to log messages to a file then the converted SET DEBUG FILE statement determines the name of the file within the destination Oracle database.

The following shows an example of an Informix Dynamic Server SET DEBUG FILE statement:

Informix Dynamic Server SPL

SET DEBUG FILE TO 'errorlog.out'; 

The Migration Workbench converts this statement by setting the name of the file written to by the utilities.DEBUG procedure to errorlog.out. The converted SET DEBUG FILE statement sets the value of a variable named DebugFile defined within the utilities package. The following shows the converted PL/SQL code:

Oracle PL/SQL

OMWB_Emulation.utilities.DebugFile := 'errorlog.out';

The filename stipulated within the Informix Dynamic Server SET DEBUG FILE statement may also contain a file path, for example

Informix Dynamic Server SPL

SET DEBUG FILE TO 'D:\informix\audit\errorlog.out';

If this is the case, the converter extracts the file path and use it to set the value of a variable named utilities.DebugDir also defined within the utilities package. For example, the preceding SET DEBUG FILE statement is converted into the following lines:

Oracle PL/SQL

OMWB_Emulation.utilities.DebugFile := 'errorlog.out';
OMWB_Emulation.utilities.DebugDir := 'D:\informix\audit\';

For further information about the DEBUG package, see Converting TRACE Statements. For a code sample of the utilities package, see Appendix A, "Code Samples".

BEGIN WORK Statement

Informix Dynamic Server uses the BEGIN WORK statement to start a transaction. The Migration Workbench converts this statement into a named PL/SQL savepoint. The BEGIN WORK statement and its equivalent in Oracle are as follows:

Informix Dynamic Server SPL

BEGIN WORK;

Oracle PL/SQL

SAVEPOINT SP1;

Savepoints within Oracle are used to mark a place within a transaction. After the savepoint is defined, it is possible to rollback to it using the ROLLBACK WORK statement.

The Migration Workbench automatically generates a savepoint name of the form SP<integer>.The integer value starts at 1 and increments each time a new BEGIN WORK statement is converted. Using savepoints in this way enables finer transaction control within the Oracle stored procedure. It is recommended that you manually convert the generated stored procedure to take full advantage of the nested savepoint capabilities within Oracle. For more information about Oracle savepoints, see the PL/SQL User's Guide and Reference.

ROLLBACK WORK Statement

Informix Dynamic Server uses the ROLLBACK WORK statement to undo any of the changes made since the beginning of the transaction. The Oracle ROLLBACK statement acts in an identical way. However, only part of the transaction need be undone. To achieve this Oracle SAVEPOINT definitions within the PL/SQL stored procedure code are used.

The Migration Workbench automatically converts Informix Dynamic Server BEGIN WORK statements into Oracle SAVEPOINTs. These savepoints are then integrated into the conversion of the original Informix Dynamic Server ROLLBACK WORK statement. An example of the ROLLBACK WORK and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

BEGIN WORK
INSERT INTO student VALUES(300, 'Tara', 'Finn');
INSERT INTO major VALUES(300, 1237);
ROLLBACK WORK;

Oracle PL/SQL

SAVEPOINT SP1;
INSERT INTO student 
VALUES(300, 
       'Tara', 
       'Finn');
INSERT INTO major 
VALUES(300, 
       1237);
ROLLBACK TO SAVEPOINT SP1;

SELECT Statements as Conditions

Informix Dynamic Server allows you to use SELECT statements within an IF statement condition. Oracle does not enable you to use SELECT queries as conditions in this way. In order to emulate this Informix Dynamic Server statement, the Migration Workbench automatically generates a Boolean variable within the PL/SQL code. It then sets the value of this Boolean variable within a SELECT.. FROM DUAL statement that incorporates the original SELECT statement within the WHERE clause.

DUAL is a table automatically created by Oracle along with the data dictionary. DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value 'X'. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once.

An Informix Dynamic Server example of a SELECT statement used as a condition and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

IF EXISTS (SELECT content_id
           FROM slistcontent
           WHERE list_id = sp_list_id
           AND thing_id = new_thing)
THEN
  /* statement block */
END IF;

Oracle PL/SQL

/* SPCONV-MSG:(SUBQUERY) Subquery within IF statement emulated by using Boolean variable. */
OMWB_tempBoolVar1 := FALSE;
SELECT TRUE INTO OMWB_tempBoolVar1 FROM DUAL
WHERE EXISTS (SELECT content_id 
			FROM slistcontent 
			WHERE list_id = sp_list_id 
			AND thing_id = new_thing);
IF(OMWB_tempBoolVar1) THEN
/* statement block */
END IF;

The Migration Workbench automatically adds the Boolean variable OMWB_tempBoolVar1 to the generated PL/SQL code. The value of this variable is then set by the SELECT .. FROM DUAL statement that contains the original Informix Dynamic Server SELECT statement as part of the WHERE clause. The Boolean variable added by the converter is then used within the IF condition.

Exception Blocks

Informix Dynamic Server exception blocks are declared prior to the statement block they encapsulate. Oracle exception blocks are declared at the end of the statement block they encapsulate. This causes the Migration Workbench to transfer the converted exception handling code to the bottom of the statement block within the generated PL/SQL code.

If the exception block have been defined with the keywords WITH RESUME, the following warning is also output within the generated PL/SQL code:

Informix Dynamic Server SPL

/* SPCONV-WRN:(WITH RESUME) Oracle has no such construct. Manual conversion required. */

The converter automatically maps the following Informix Dynamic Server error numbers to Oracle predefined exceptions. When the convertor encounters any Informix Dynamic Server error number not presented within the following table, it outputs the error number as a comment within the generated PL/SQL stored procedure and indicate that manual conversion of the exception block is required.

Informix Dynamic Server Error Number Oracle Predefined Exception
-239 DUP_VAL_ON_INDEX
100 NO_DATA_FOUND
-259 INVALID_CURSOR
-415 VALUE_ERROR
-1213 INVALID_NUMBER
-1214 VALUE_ERROR
-1215 VALUE_ERROR
-1348 ZERO_DIVIDE
-248 TOO_MANY_ROWS

The following shows an example of an Informix Dynamic Server stored procedure that defines one exception block to catch multiple errors and it's converted equivalent in Oracle PL/SQL:

Informix Dynamic Server SPL

CREATE PROCEDURE "root".add_slist_thing(
v_uid like PHPUser.user_id, 
v_lstid like ShoppingList.list_id, 
v_thgid like Thing.thing_id, 
v_cntdesc like SListContent.content_desc)
RETURNING smallint;
	BEGIN
		on exception in (-239, -310)
			return -2;
		end exception;

		insert into listcontent
		values (v_lstid, v_uid, v_thgid, v_cntdesc);
		let returnCode = upd_slist_date(v_lstid, v_uid);
		return returncode;
	END
END PROCEDURE;

Oracle PL/SQL

CREATE OR REPLACE FUNCTION root.add_slist_thing(
	v_uid_IN               PHPUser.user_id%TYPE,
	v_lstid_IN             ShoppingList.list_id%TYPE,
	v_thgid_IN             Thing.thing_id%TYPE,
	v_cntdesc_IN           SListContent.content_desc%TYPE) RETURN NUMBER AS

v_uid               PHPUser.user_id%TYPE := v_uid_IN;
v_lstid             ShoppingList.list_id%TYPE := v_lstid_IN;
v_thgid             Thing.thing_id%TYPE := v_thgid_IN;
v_cntdesc           SListContent.content_desc%TYPE := v_cntdesc_IN;
ItoO_selcnt         NUMBER;
ItoO_rowcnt         NUMBER;

BEGIN
	BEGIN
	INSERT INTO listcontent 
	VALUES(v_lstid,
	       v_uid,
	       v_thgid,
	       v_cntdesc); 
	returnCode := upd_slist_date ( v_lstid , v_uid ); 
	RETURN returncode;
	EXCEPTION
		/* SPCONV-WRN:(EXCEPTION) Could not convert 1 Informix error number to a predefined Oracle exception. Manual conversion required. */
		WHEN DUP_VAL_ON_INDEX THEN  /* Not Converted : -310 */
			RETURN - 2;
	END;
END add_slist_thing;

RAISE EXCEPTION Statements

The Informix Dynamic Server RAISE EXCEPTION statement is used to simulate the generation of an error message. It passes program control to the execution handler that is designed to explicitly catch the raised exception. The execution of the stored procedure can then continue.

If the RAISE EXCEPTION statement is encountered within the Informix Dynamic Server stored procedure, it is converted into a call to the built-in Oracle RAISE_APPLICATION_ERROR function. This function enables the raising of errors containing user defined messages. The following shows an example of the RAISE EXCEPTION statement and its conversion to an Oracle PL/SQL RAISE_APPLICATION_ERROR function call:

Informix Dynamic Server SPL

RAISE EXCEPTION -208, 0, 'Cannot insert. Required datafile ' || datafilename || ' missing. insert_seq_proc procedure';

Oracle PL/SQL

RAISE_APPLICATION_ERROR(-299999, /* Informix error number : -208, 0 */ "Cannot insert. Required datafile ' || datafilename || ' missing. insert_seq_proc procedure");

The following is an abbreviated syntax of the Oracle RAISE_APPLICATION_ERROR function:

Oracle PL/SQL Syntax

RAISE_APPLICATION_ERROR(error number, error message);

Where the error number is a number between -20000 and -20999 and error message the text associated with this error. An additional keep errors parameter is also available. For more information about this parameter, see the PL/SQL User's Guide and Reference.

The original error number used within the Informix Dynamic Server RAISE EXCEPTION statement is output as a comment within the call to RAISE_APPLICATION_ERROR.

The Informix Dynamic Server RAISE EXCEPTION statement is always used in conjunction with an ON EXCEPTION statement. The RAISE EXCEPTION statement simulates an error condition and program control passes to the ON EXCEPTION exception handler designed to catch the error condition raised.

An example of an exception block and the converted equivalent in Oracle is as follows:

Informix Dynamic Server SPL

ON EXCEPTION IN (-208)
  DELETE FROM students;
END EXCEPTION;

IF(clear_table=1)THEN
  RAISE EXCEPTION -208, 0, 'No datafile';
END IF;

Oracle PL/SQL

BEGIN
  IF(clear_table=1)THEN
    RAISE_APPLICATION_ERROR(-20999, 
                            /* Informix error number : -208, 0 */ 
                           "No datafile"
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DELETE FROM students;
END

The converted Informix Dynamic Server exception block is still resident within the PL/SQL code but the converted RAISE EXCEPTION statement now calls the built-in RAISE_APPLICATION_ERROR function instead of calling the embedded exception block originally defined for it. The Oracle RAISE_APPLICATION_ERROR statement also terminates the execution of the stored procedure and returns to the calling routine. However, the execution of the Informix Dynamic Server stored procedure continues.

Using the RAISE_APPLICATION_ERROR function in this way changes the execution flow and error handling functionality of the converted PL/SQL stored procedure. Therefore, manual conversion of the procedure is usually required.

For more information, see the following sections:

Error Handling within Stored Procedures

Oracle PL/SQL checks each SQL statement for errors before proceeding to the next statement. If an error occurs, control immediately jumps to an exception handler. This prevents you from having to check the status of every SQL statement. For example, if a SELECT statement does not find any rows in the database, an exception is raised and the code to deal with this error is executed.

Informix Dynamic Server has similar error handling capabilities to Oracle. Blocks of exception handler code resident within the SPL stored procedure catch any errors raised by the database server during execution of the stored procedure code.

Informix Dynamic Server error handlers, unlike Oracle error handlers, can continue execution of the stored procedure after the error occurs. This fundamental difference has immediate implications for the conversion process.

While Informix Dynamic Server SPL exception blocks can be translated into syntactically correct PL/SQL, the execution flow of the PL/SQL stored procedure differs to a considerable extent should an error occur. The Oracle server terminates execution of the stored procedure, while the Informix Dynamic Server server resumes execution of the stored procedure.

In order to successfully convert Informix Dynamic Server SPL exception blocks to functionally equivalent PL/SQL, you must manually convert the generated PL/SQL code.

If you have to maintain control within the executable commands section of the PL/SQL stored procedure, you should use IF statements to check for possible errors before they occur.

After conversion, it is recommended that you re-write large or complex stored procedures in a more modular way so that each stored procedure performs one task and contains all the DML statements required to perform that task. Placing task related DML statements into logical units enables greater control over both the transaction model and the error model. This leads to the production of a more re-usable, maintainable, and stable PL/SQL code base.

For more information about the strategy employed by the Migration Workbench in the conversion of Informix Dynamic Server exception blocks to PL/SQL, see Exception Blocks.

DDL Statements in SPL Code

Informix Dynamic Server enables certain DDL statements to reside within stored procedure code. Oracle does not support the direct inclusion of DDL statements within PL/SQL code. Oracle offers two ways to dynamically execute DDL statements: an internal DBMS package named DBMS_SQL (available since Oracle 7.1) and Native Dynamic SQL (available since Oracle 8i).

As the DBMS_SQL package does not support new Oracle8 data types, the Oracle Migration Workbench uses Native Dynamic SQL to execute any DDL statement present within the original Informix Dynamic Server SPL code. This is accomplished by offering a DDL_Manager stored procedure. The Migration Workbench automatically creates this stored procedure in the destination Oracle database under the OMWB emulation users schema.

When the converter encounters a DDL statement within the Informix Dynamic Server stored procedure, the resulting PL/SQL code uses the DDL_Manager procedure to dynamically execute the DDL statement. For example, the following Informix Dynamic Server DDL statement is converted into a call to the DDL_Manager PL/SQL stored procedure:

Informix Dynamic Server SPL

alter table pushprefs modify (preferences_value char(100));

Oracle PL/SQL

/* SPCONV-MSG:(ALTER TABLE) OMWB_Emulation.DDL_MANAGER procedure used
to execute DDL statement. */
 OMWB_Emulation.DDL_Manager('ALTER TABLE pushprefs MODIFY (
preferences_value  CHAR(100) )');

The DDL_Manager procedure is created with invokers_rights permissions. This means that any person who executes the procedure executes any DDL statement within their own schema and not the schema that the DDL_Manager procedure resides within, in this case, the OMWB_Emulation user's schema. For more information about the invokers rights model, see the PL/SQL User's Guide and Reference.

A code listing of the DDL_Manager procedure is as follows:

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE DDL_Manager(
ddl_statement varchar)
AUTHID CURRENT_USER IS
BEGIN
  EXECUTE IMMEDIATE ddl_statement;
EXCEPTION
  WHEN OTHERS THEN
  RAISE;
END DDL_Manager;

It is recommended that you check all DDL statement strings passed to the DDL_Manager procedure for errors before the creation of the encapsulating procedure in the destination Oracle database.

Informix Dynamic Server DDL statements that are not dispatched to the DDL_Manager procedure for execution are explained in the following sections:

Creating Temporary Tables

The Migration Workbench converts temporary tables to Oracle global temporary tables. Unlike Informix Dynamic Server temporary tables, Oracle temporary table structures are persistent across sessions, therefore the converted CREATE TEMP TABLE statement is only ever executed once within the Oracle database.

When the converter encounters an Informix Dynamic Server CREATE TEMPORARY TABLE <table name> statement, it generates the DDL to create an equivalent Oracle global temporary table. It then inserts a PL/SQL DELETE FROM <table name> statement into the converted stored procedure. This ensures that the table is void of data before it is used within the PL/SQL code. The CREATE GLOBAL TEMPORARY TABLE DDL statement generated by the converter is executed before the stored procedure is created in the destination Oracle database. This ensures that referential integrity constraints are met during the creation of the stored procedure within the destination Oracle database.

An example of an Informix Dynamic Server CREATE TABLE statement and the generated Oracle DDL statement that is executed before the stored procedure is created within the destination Oracle database is as follows:

Informix Dynamic Server SPL

CREATE TEMP TABLE temp_table AS
  SELECT emp_num, emp_name 
  FROM emp;

Oracle PL/SQL

CREATE GLOBAL TEMP TABLE temp_table AS
   SELECT emp_num, 
          emp_name 
   FROM emp
ON COMMIT PRESERVE ROWS;

Additionally, the following DELETE FROM statement appears within the converted PL/SQL code.

Oracle PL/SQL

DELETE FROM temp_table;

The previous statement that appears within the converted PL/SQL code clears the temp table of all data. This leaves the Oracle table in a state consistent with the original Informix Dynamic Server table at this point within the procedures execution.

DROP TABLE Statements

When the Migration Workbench converts Informix Dynamic Server temporary tables to Oracle temporary tables, any DROP TABLE statement within an Informix Dynamic Server stored procedure becomes redundant within the converted PL/SQL code. Oracle temporary tables are created once. The definition is persistent across sessions although the data held within the tables is not persistent.

The following actions occurs when a DROP TABLE statement is encountered by the stored procedure converter.

  • A warning message outputs to the log window. If you selected the Display parser warnings option from the Parser Options tab within the Migration Workbench, a warning message is placed into the converted PL/SQL code.

  • The original Informix Dynamic Server DROP TABLE statement is displayed within the converted PL/SQL code as a single line comment.

  • An executable NULL statement is also added to the PL/SQL code.

The following shows the DROP TABLE statement and the converted equivalent in Oracle:

Informix Dynamic Server SPL

DROP TABLE temp_table;

Oracle PL/SQL

/* SPCONV-WRN:(DROP TABLE) Statements never passed to the DDL_Manager procedure. */
--DROP TABLE temp_table;
NULL

Using Keywords as Identifiers

Informix Dynamic Server SPL allows keywords to be used as identifiers. This can cause ambiguous SQL statements and unreadable SPL code. An example of a keyword used as an identifier is as follows:

Informix Dynamic Server SPL

SELECT ordid INTO order FROM table1;

The keyword order is used in this context as a variable name.

Oracle does not enable keywords to be used as identifiers. All keywords within Oracle are reserved. This eradicates ambiguous PL/SQL code. The preceding Informix Dynamic Server SELECT statement is not syntactically valid within PL/SQL and produces a compilation error within the destination Oracle database.

In order to convert Informix Dynamic Server SPL into syntactically correct PL/SQL, the stored procedure parser needs to recognize keywords used in the context of an identifier in an Informix Dynamic Server SPL statement. The Migration Workbench parser handles this by adding a trailing underscore character to the identifier name. The following table illustrates how the Migration Workbench appends an underscore to the Informix Dynamic Server SPL reserved word order:

Code Type Example
Informix Dynamic Server SPL
SELECT ordid INTO order FROM table1;
Oracle PL/SQL
SELECT ordid INTO order_ FROM table1;

The Migration Workbench stored procedure converter does not support any of the following list of Informix Dynamic Server keywords as identifiers:

  • INTO

  • WHERE

  • HAVING

  • FROM

  • END: * NEW *

  • LET

  • IF

  • ELSE

  • TRUNC

  • WITH

  • RESUME

  • RETURN

  • INSERT

  • TRIM

  • UPPER

  • LENGTH

  • GLOBAL

  • LIKE

  • NULL

  • OUTER

  • DBINFO

  • WEEKDAY

  • SELECT

  • FOREACH

  • CALL

  • UPDATE

  • DELETE

  • CASE

If the converter encounters an unsupported keyword when an identifier is expected, one of the following actions occurs:

  • Parsing process fails. This causes an error message to be generated within the Log window. An example error message is shown as follows:

    SPCONV-ERR[23]:(UPDATE) Encounterd the word UPDATE when expecting one of the following.
    
    
  • Produces syntactically incorrect PL/SQL code. This causes the PL/SQL stored procedure to fail compilation within the destination Oracle database.

Oracle recommends that keyword/identifier issues are removed from the original Informix Dynamic Server stored procedure code before you initiate the conversion process. You can manually edit the stored procedure text within the Informix Dynamic Server Source Model of the Migration Workbench.

Issues with Converting SPL Statements

The Migration Workbench parser may not convert some SPL statements to PL/SQL code. Generally, this happens when the statement functionality cannot be replicated in PL/SQL, if the statement is unnecessary within the PL/SQL code, or if the statement requires manual conversion by the DBA. The following list of statements are currently not supported:

  • DBINFO('sqlca.sqlerrd1')

  • DBINFO(DBSPACE, number)

  • All SET statements with the exception of SET DEBUG FILE

When the parser encounters any unsupported statement, it takes the following actions:

  1. A parser warning (SPCONV-WRN) is produced within the Log window.

  2. If you have selected the Display parser Warnings parser option for the current procedure, the converter places a warning message within the PL/SQL stored procedure text in the form of a comment.

  3. The original Informix Dynamic Server statement is added to the PL/SQL text as a comment.

  4. An executable NULL; statement is added to the PL/SQL text.

An example of and unsupported SET statement and the converted equivalent is as follows:

Informix Dynamic Server SPL

SET ISOLATION TO DIRTY READ

Oracle PL/SQL

/* SPCONV-ERR:(SET) Statement ignored. Manual conversion may be required. */
--SET ISOLATION TO DIRTY READ
NULL;

Oracle Migration Workbench Multibyte Issue

If the Informix Dynamic Server database you are migrating uses a multibyte character set, there may be unnecessary extra spaces in the plain text of the stored procedures of the Informix Dynamic Server system catalog table sysprocbody. The Migration Workbench loads all the data from the Informix system catalog, including any extra spaces in the stored procedures.

When the Migration Workbench loads the Informix Dynamic Server system catalogs during the capture phase of the database migration, it attempts to identify any procedure lines with unnecessary extras spaces, and issues warnings accordingly. The warnings list the relevant database, schema, and the stored procedure. The warnings also inform you of the line number and column position where an extra space is located.

This is only an issue for Informix Dynamic Server databases with a multibyte character set. The following example describes how the multibyte issue occurs:

Multibyte Issue Example

Informix Dynamic Server stored procedure text is stored in CHAR(256) blocks in the data column of the sysprocbody table. When one line is filled another line is added, and then the two lines are concatenated to produce the original text.

An Informix database base is created with the multibyte character set SJIS-S. A stored procedure is created that contains multibyte Japanese characters. The stored procedure is compiled and binary and plain text versions are stored row by row in the sysprocbody table. The Migration Workbench only captures the plain text version. As Japanese characters and single byte characters are added to sysprocbody, the space in a CHAR(256) is used up.

At a certain point, a character is written to position 249 in the data column of the current row. An identifier for a table referenced in a select statement in the stored procedure written next to sysprocbody. If the identifier has 5 characters, 2 single byte and 3 double byte, it requires 7 bytes to store this identifier:

In this instance the first single byte character is written to position 250. The second single byte is written to position 251. The third character, a Japanese double byte character is written to position 252 and 253, as it requires two bytes for storage. The fourth character is written out to positions 254 and 255. Informix Dynamic Server attempts to write out the fifth character that requires two bytes of storage. However, it does not fit because there is only one byte space left in the current data char(256) row.

Informix Dynamic Server writes the next character to a new line in the data column of sysprocbody. Because the data column is CHAR(256) and nothing was written to position 256 in the previous line, then it is blank padded with a space. Therefore when the two lines are concatenated to produce the original text, the identifier appears with a space in it.

Handling Extra Spaces in Stored Procedures

When the Migration Workbench issues a warning about possible extra spaces in a stored procedure, you must navigate to the text of the stored procedure using the Migration Workbench, to examine the possible extra space. If the extra space is correct, you can ignore the warning. However, an extra space that requires attention, would, for example, be a space in the middle an identifier for a variable. Attempting to create the Oracle model from the captured database with this space left unchanged would generate an error during the parsing of the stored procedure. In this case you must remove the space.