2.2 Using the Pass-Through Feature

The gateway can pass SQL Server commands or statements from the application to the SQL Server database using the DBMS_HS_PASSTHROUGH package.

Use the DBMS_HS_PASSTHROUGH package in a PL/SQL block to specify the statement to be passed to the SQL Server database, as follows:

DECLARE
    num_rows INTEGER;
BEGIN
    num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MSQL('command');
END;
/

Where command cannot be one of the following:

• BEGIN TRANSACTION
• COMMIT
• ROLLBACK
• SAVE
• SHUTDOWN
• RELEASE
• SAVEPOINT
• CONNECT
• SQL Server tool commands

The DBMS_HS_PASSTHROUGH package supports passing bind values and executing SELECT statements.

Note:

TRUNCATE cannot be used in a pass-through statement.

See Also:

Oracle Database PL/SQL Packages and Types Reference and Chapter 3, Features of Oracle Database Gateways, of Oracle Database Heterogeneous Connectivity User's Guide for more information about the DBMS_HS_PASSTHROUGH package.