4.2 Using Oracle Stored Procedures with the Gateway

The gateway stored procedure support is an extension of Oracle stored procedures.

An Oracle stored procedure is a schema object that logically groups together a set of SQL and other PL/SQL programming language statements to perform a specific task. Oracle stored procedures are stored in the database for continued use. Applications use standard Oracle PL/SQL to call stored procedures.

Oracle stored procedures can be located in a local instance of Oracle database and in a remote instance. Figure 4-1 illustrates two stored procedures: oraproc1 is a procedure stored in the ORA1 Oracle instance, and oraproc2 is a procedure stored in the ORA2 Oracle instance.

Figure 4-1 Calling Oracle Stored Procedures in a Distributed Oracle Environment

Description of "Figure 4-1 Calling Oracle Stored Procedures in a Distributed Oracle Environment "

To maintain location transparency in the application, a synonym can be created:

CREATE SYNONYM oraproc2 FOR oraproc2@ora2;

After this synonym is created, the application no longer needs to use the database link specification to call the stored procedure in the remote Oracle instance.

In Figure 4-1, the second statement in oraproc1 is used to access a table in the ORA2 instance. In the same way, Oracle stored procedures can be used to access DB2 tables through the gateway.