| SQL adaptation guide for Oracle Database 11, 12 / Data dictionary | |
Both Informix® and ORACLE support stored procedures, but the programming languages are totally different: SPL for Informix versus PL/SQL for ORACLE.
In ORACLE, stored procedures and functions can be implemented in packages (similar to BDL modules). This is a powerful feature which enables structured procedural programming in the database. ORACLE itself implements system tools with packages (dbms_sql, dbms_output, dbms_lock). Procedures, functions and packages can be created with 'CREATE OR REPLACE' to keep privileges settings. With Informix, you must drop and create again.
ORACLE uses a different privilege context when using dynamic SQL in PL/SQL; roles are not effective. Users must have direct privileges settings in order to perform DDL or DML operations inside dynamic SQL.
Informix stored procedures must be converted to ORACLE "by hand".
Try to use ORACLE packages in order to group stored procedures into modules.
See SQL Programming for more details about executing stored procedures with ORACLE.