Passing Native SQL Statements through the Gateway

4.11 Passing Native SQL Statements through the Gateway

The passthrough SQL feature enables an application developer to send a SQL statement directly to the DRDA server without the statement being interpreted by Oracle database.

DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE SQL passthrough statements that are supported by the gateway are limited to nonqueries (INSERT, UPDATE, DELETE, and DDL statements) and cannot contain bind variables. The gateway can run native SQL statements using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE.

DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE is a built-in gateway function. This function receives one input argument and returns the number of rows affected by the SQL statement. For data definition language (DDL) statements, the function returns zero.

DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE are reserved names of the gateway and are used specifically for running native SQL.

The 12c Release 2 (12.2) of Oracle Database Gateway for DRDA enables retrieval of result sets from queries issued with passthrough. The syntax is different from the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function. Refer to Retrieving Results Sets Through Passthrough for more information.

Processing DDL Statements through Passthrough
SQL statements that are processed through the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function are not interpreted by the Oracle database.
Using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE
To run a passthrough SQL statement using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE, use the following syntax where number_of_rows is a variable that is assigned the number of rows affected by the passthrough SQL completion.
Retrieving Results Sets Through Passthrough
Oracle Database Gateway for DRDA provides a facility to retrieve results sets from a SELECT SQL statement entered through passthrough.

Parent topic: Developing Applications

4.11.1 Processing DDL Statements through Passthrough

SQL statements that are processed through the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function are not interpreted by the Oracle database.

As a result, the Oracle database will not know if such statements are making any modifications to the DRDA server. This means that unless you keep the cached information of the Oracle database up to date after changes to the DRDA server, the database may continue to rely upon inaccurate or outdated information in subsequent queries within the same session.

An example of this occurs when you alter the structure of a table by either adding or removing a column. When an application references a table through the gateway (for example, when you perform a query on it), the Oracle database caches the table definition. Now, suppose that within the same session, the application subsequently alters the table's form, by using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE to add a column. Then, the next reference to the table by the application will return the old column definitions of the table and will ignore the table's new column. This is because the Oracle database did not process the statement and, so, has no knowledge of the alteration. Because the database does not know of the alteration, it has no reason to requery the table form, and, so, it will use the already-cached form to handle any new queries.

In order for the Oracle database to acquire the new form of the table, the existing session with the gateway must be closed and a new session must be opened. This can be accomplished in either of two ways:

By ending the application session with the Oracle database and starting a new session after modifications have been made to the DRDA server; or
By running the ALTER SESSION CLOSE DATABASE LINK command after making any modifications to the DRDA server.

Either of the above actions will void the cached table definitions and will force the Oracle database to acquire new definitions on the next reference.

Parent topic: Passing Native SQL Statements through the Gateway

4.11.2 Using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE

To run a passthrough SQL statement using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE, use the following syntax where number_of_rows is a variable that is assigned the number of rows affected by the passthrough SQL completion.

For example:

number_of_rows = DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink ('native_DRDA_sql');

For DDL statements, a zero is returned for the number of rows affected.

dblink is the name of the database link used to access the gateway.

native_DRDA_sql is a valid nonquery SQL statement (except CONNECT, COMMIT, and ROLLBACK). The statement cannot contain bind variables. The DRDA server rejects native SQL statements that cannot be dynamically prepared. The SQL statement passed by the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function must be a character string. For more information regarding valid SQL statements, refer to the SQL Reference for the particular DRDA server.

Examples
Examples using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE.

Parent topic: Passing Native SQL Statements through the Gateway

4.11.2.1 Examples

Examples using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE.

Insert a row into a DB2 table using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE:

DECLARE
  num_rows integer;
BEGIN
num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink
('INSERT  INTO SCOTT.DEPT VALUES (10,''PURCHASING'',''PHOENIX'')');
END;
/

Create a table in DB2 using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE:

DECLARE
  num_rows integer;
BEGIN
  num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink
  ('CREATE TABLE MYTABLE (COL1 INTEGER, COL2 INTEGER, COL3 CHAR(14),
  COL4 VARCHAR(13))');
END;
/

Parent topic: Using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE

4.11.3 Retrieving Results Sets Through Passthrough

Oracle Database Gateway for DRDA provides a facility to retrieve results sets from a SELECT SQL statement entered through passthrough.

Refer to Oracle Database Heterogeneous Connectivity User's Guide for additional information.

Example
Example using DBMS_HS_PASSTHROUGH.

Parent topic: Passing Native SQL Statements through the Gateway

4.11.3.1 Example

Example using DBMS_HS_PASSTHROUGH.

DECLARE
  CRS binary_integer;
  RET binary_integer;
  VAL VARCHAR2(10)
BEGIN
  CRS:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@gtwlink;
  DBMS_HS_PASSTHROUGH.PARSE@gtwlink(CRS,'SELECT NAME FROM PT_TABLE');
BEGIN
  RET:=0;
  WHILE (TRUE)
  LOOP
    RET:=DBMS_HS_PASSTHROUGH.FETCH_ROW@gtwlink(CRS,FALSE);
    DBMS_HS_PASSTHROUGH.GET_VALUE@gtwlink(CRS,1,VAL);
    INSERT INTO PT_TABLE_LOCAL VALUES(VAL);
  END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('END OF FETCH');
        DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@gtwlink(CRS);
      END;
    END;  
END;
/

Parent topic: Retrieving Results Sets Through Passthrough