2.2 Changes and Enhancements

These sections describe the changes and enhancements unique to this release of Oracle Database Gateway for DRDA.

• Remote Insert Rowsource
  A remote insert rowsource feature allows remote insert requiring local Oracle data to work through the Oracle database and Oracle Database Gateway. This functionality requires that the Oracle database and the Oracle Database Gateway to be version 12.2 or later.
• Gateway Password Encryption Tool
  The Gateway Password Encryption tool (g4drpwd) has been replaced by a generic feature that is now part of Heterogeneous Services.
• Result Sets and Stored Procedures
  The Oracle Database Gateway for DRDA provides support for stored procedures that return result sets.

2.2.1 Remote Insert Rowsource

A remote insert rowsource feature allows remote insert requiring local Oracle data to work through the Oracle database and Oracle Database Gateway. This functionality requires that the Oracle database and the Oracle Database Gateway to be version 12.2 or later.

By Oracle Database design, some distributed statement must be executed at the database link site. But in certain circumstances, there is data needed to execute these queries that must be fetched from the originating Oracle Database. Under homogeneous connections, the remote Oracle database would call back the source Oracle database for such data. But in heterogeneous connections, this is not viable, because this means that the Foreign Data Store would have to query call back functions, or data, that can only be provided by the Oracle instance that issued the query. In general, these kinds of statements are not something that can be supported through the Oracle Database Gateway.

The following categories of SQL statements results in a callback:

• Any DML with a sub-select, which refers to a table in Oracle database.
• Any DELETE, INSERT, UPDATE or "SELECT... FOR UPDATE..." SQL statement containing SQL functions or statements that needs to be executed at the originating Oracle database.

  These SQL functions include USER, USERENV, and SYSDATE; and involve the selection of data from the originating Oracle database.

• Any SQL statement that involves a table in Oracle database, and a LONG or LOB column in a remote table.

An example of a remote INSERT statement that can work through the remote insert rowsource feature is as follows:

INSERT INTO gateway_table@gateway_link select * from local_table;

2.2.2 Gateway Password Encryption Tool

The Gateway Password Encryption tool (g4drpwd) has been replaced by a generic feature that is now part of Heterogeneous Services.

Refer to Chapter 15, "Security Considerations" in Oracle Database Gateway Installation and Configuration Guide for IBM AIX on POWER Systems (64-Bit), Linux x86-64, Oracle Solaris on SPARC (64-Bit), Oracle Solaris on x86-64 (64-Bit) and HP-UX Itanium or Oracle Database Gateway Installation and Configuration Guide for Microsoft Windows for details.

2.2.3 Result Sets and Stored Procedures

The Oracle Database Gateway for DRDA provides support for stored procedures that return result sets.

By default, all stored procedures and functions do not return a result set to the user. To enable result sets, set the HS_FDS_RESULTSET_SUPPORT parameter in the initialization parameter file.

See Also:

Initialization Parameters for information about editing the initialization parameter file and the HS_FDS_RESULTSET_SUPPORT parameter. For further information about Oracle support for result sets in non-Oracle databases see Oracle Database Heterogeneous Connectivity User's Guide.

Note:

If you set the HS_FDS_RESULTSET_SUPPORT gateway initialization parameter, you must change the syntax of the procedure execute statement for all existing stored procedures or errors will occur.

When accessing stored procedures with result sets through the Oracle Database Gateway for DRDA, you will be in the sequential mode of Heterogeneous Services. The gateway returns the following information to Heterogeneous Services during procedure description:

• All the input arguments of the remote stored procedure
• None of the output arguments
• One out argument of type ref cursor (corresponding to the first result set returned by the stored procedure)

Client programs have to use the virtual package function DBMS_HS_RESULT_SET.GET_NEXT_RESULT_SET to get the ref cursor for subsequent result sets. The last result set returned is the out argument from the procedure.

The limitations of accessing result sets are as follows:

• Result sets returned by a remote stored procedure have to be retrieved in the order in which they were placed on the wire.
• On execution of a stored procedure, all result sets returned by a previously executed stored procedure will be closed, regardless of whether the data has been completely retrieved or not.

In the following example, the UDB stored procedure is executed to fetch the contents of the EMP and DEPT tables from UDB:

CREATE PROCEDURE REFCURPROC (IN STRIN VARCHAR(255), OUT STROUT VARCHAR(255) ) 
  RESULT SETS 3  LANGUAGE SQL 
BEGIN
  DECLARE TEMP CHAR (20); 
 DECLARE C1 CURSOR WITH RETURN TO CALLER FOR 
      SELECT * FROM TKHOEMP; 
  DECLARE C2 CURSOR WITH RETURN TO CALLER FOR 
     SELECT * FROM TKHODEPT; 
 OPEN C1; 
 OPEN C2; 
 SET STROUT = STRIN; 
 END