Stored procedures

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.