Oracle DBMS_HS_PASSTHROUGH
Version 19.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose This package contains utilities to supports parallel processing for heterogeneous targets access. Passthrough SQL statements are executed directly at the non-Oracle system. Any statement executed with this package is executed in the same transaction as standard SQL statements.

DBMS_HS_PASSTHROUGH is a virtual package. It conceptually resides at the non-Oracle system. In reality, however, calls to this package are intercepted by Heterogeneous Services and mapped to one or more Heterogeneous Services calls. The driver, in turn, maps these Heterogeneous Services calls to the API of the non-Oracle system. The client application invokes the procedures in the package through a database link in exactly the same way as it would invoke a non-Oracle system stored procedure. The special processing done by Heterogeneous Services is transparent to the user.
AUTHID None
Dependencies None
Documented Yes
Exceptions
Error Code Reason
ORA-01403 NO_DATA_FOUND
ORA-28550 The cursor passed is invalid.
ORA-28551 Invalid SQL statement
ORA-28552 Procedure is not run in right order. (was the cursor opened and the SQL statement parsed?)
ORA-28553 The position of the bind variable is out of range.
ORA-28554 Maximum open cursors
ORA-28555 A NULL value was passed for a NOT NULL parameter.
First Available Not known
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmshsld.sql
Subprograms
 
BIND_INOUT_VARIABLE
Binds IN OUT bind variables

Overload 1
dbms_hs_passthrough.bind_inout_variable(
c IN     BINARY_INTEGER NOT NULL,
p IN     BINARY_INTEGER NULL,
v IN OUT DATE,
n IN     VARCHAR2);
TBD
Binds IN OUT bind variables

Overload 2
dbms_hs_passthrough.bind_inout_variable(
c IN     BINARY_INTEGER NOT NULL,
p IN     BINARY_INTEGER NULL,
v IN OUT NUMBER,
n IN     VARCHAR2);
TBD
Binds IN OUT bind variables

Overload 3
dbms_hs_passthrough.bind_inout_variable(
c IN     BINARY_INTEGER NOT NULL,
p IN     BINARY_INTEGER NULL,
v IN OUT VARCHAR2,
n IN     VARCHAR2);
TBD
 
BIND_INOUT_VARIABLE_RAW
Binds IN OUT bind variables of datatype RAW dbms_hs_passthrough.bind_inout_variable_raw(
c IN     BINARY_INTEGER NOT NULL,
p IN     BINARY_INTEGER NULL,
v IN OUT RAW,
n IN     VARCHAR2);
TBD
 
BIND_OUT_VARIABLE
Binds an OUT variable with a PL/SQL program variable

Overload 1
dbms_hs_passthrough.bind_out_variable(
c IN  BINARY_INTEGER NOT NULL,
p IN  BINARY_INTEGER NULL,
v OUT DATE,
n IN  VARCHAR2);
TBD
Binds an OUT variable with a PL/SQL program variable

Overload 2
dbms_hs_passthrough.bind_out_variable(
c IN  BINARY_INTEGER NOT NULL,
p IN  BINARY_INTEGER NULL,
v OUT NUMBER,
n IN  VARCHAR2);
TBD
Binds an OUT variable with a PL/SQL program variable

Overload 3
dbms_hs_passthrough.bind_out_variable(
c IN  BINARY_INTEGER NOT NULL,
p IN  BINARY_INTEGER NULL,
v OUT VARCHAR2,
n IN  VARCHAR2);
TBD
 
BIND_OUT_VARIABLE_RAW
Binds an OUT variable of datatype RAW with a PL/SQL program variable dbms_hs_passthrough.bind_out_variable_raw(
c IN  BINARY_INTEGER NOT NULL,
p IN  BINARY_INTEGER NOT NULL,
v OUT RAW,
n IN  VARCHAR2);
TBD
 
BIND_VARIABLE
Binds an IN variable positionally with a PL/SQL program variable

Overload 1
dbms_hs_passthrough.bind_variable(
c IN BINARY_INTEGER NOT NULL,
p IN BINARY_INTEGER NOT NULL,
v IN DATE,
n IN VARCHAR2);
TBD
Binds an IN variable positionally with a PL/SQL program variable

Overload 2
dbms_hs_passthrough.bind_variable(
c IN BINARY_INTEGER NOT NULL,
p IN BINARY_INTEGER NOT NULL,
v IN NUMBER,
n IN VARCHAR2);
TBD
Binds an IN variable positionally with a PL/SQL program variable

Overload 3
dbms_hs_passthrough.bind_variable(
c IN BINARY_INTEGER NOT NULL,
p IN BINARY_INTEGER NOT NULL,
v IN VARCHAR2,
n IN VARCHAR2);
TBD
 
BIND_VARIABLE_RAW
Binds IN variables of type RAW dbms_hs_passthrough.bind_variable_raw(
c IN BINARY_INTEGER NOT NULL,
p IN BINARY_INTEGER NOT NULL,
v IN RAW,
n IN VARCHAR2);
TBD
 
CLOSE_CURSOR
Closes the cursor and releases associated memory after the SQL statement has been run at the non-Oracle system dbms_hs_passthrough.close_cursor(s IN BINARY_INTEGER NOT NULL);
See Demo Below
 
EXECUTE_IMMEDIATE
Runs a (non-SELECT) SQL statement immediately, without bind variables dbms_hs_passthrough.execute_immediate(s IN VARCHAR2 NOT NULL)
RETURN BINARY_INTEGER;
TBD
 
EXECUTE_NON_QUERY
Runs a (non-SELECT) SQL statement dbms_hs_passthrough.execute_non_query(s IN BINARY_INTEGER NOT NULL)
RETURN BINARY_INTEGER;
TBD
 
FETCH_ROW
Fetches rows from a query dbms_hs_passthrough.fetch_row(
c IN BINARY_INTEGER NOT NULL,
f IN BOOLEAN)
RETURN BINARY_INTEGER;
See Demo Below
 
GET_VALUE
Retrieves column value from SELECT statement, or retrieves
OUT bind parameters

Overload 1
dbms_hs_passthrough.get_value(
c IN  BINARY_INTEGER NOT NULL,
p IN  BINARY_INTEGER NOT NULL,
v OUT DATE);;
See Demo Below
Retrieves column value from SELECT statement, or retrieves
OUT bind parameters

Overload 2
dbms_hs_passthrough.get_value(
c IN  BINARY_INTEGER NOT NULL,
p IN  BINARY_INTEGER NOT NULL,
v OUT NUMBER);;
See Demo Below
Retrieves column value from SELECT statement, or retrieves
OUT bind parameters

Overload 3
dbms_hs_passthrough.get_value(
c IN  BINARY_INTEGER NOT NULL,
p IN  BINARY_INTEGER NOT NULL,
v OUT VARCHAR2);
See Demo Below
 
GET_VALUE_RAW
Retrieves column value from SELECT statement, or retrieves
raw OUT bind parameters
dbms_hs_passthrough.get_value_raw(
dbms_hs_passthrough.get_value(
c IN  BINARY_INTEGER NOT NULL,
p IN  BINARY_INTEGER NOT NULL,
v OUT RAW);
TBD
 
OPEN_CURSOR
Opens a cursor for running a passthrough SQL statement at the non-Oracle system dbms_hs_passthrough.open_cursor RETURN BINARY_INTEGER;
See Demo Below
 
PARSE
Parses SQL statement at non-Oracle system dbms_hs_passthrough.parse(
c    IN BINARY_INTEGER NOT NULL,
stmt IN VARCHAR2       NOT NULL);
See Demo Below
 
Demo
Gateway Passthrough Demo DECLARE
 pt_cur BINARY_INTEGER := dbms_hs_passthrough.open_cursor@gtwlink;
 ret    BINARY_INTEGER := 0;
 val    VARCHAR2(10);
 age    NUMBER;
BEGIN
  dbms_has_passthrough.parse@gtwlink(pt_cur, 'SELECT name, age FROM pt_table');
  BEGIN
    WHILE (TRUE) LOOP
      ret := dbms_hs_passthrough.fetch_row(@gtwlink(pt_cur, FAlSE);
      dbms_hs_passthrough.get_values@gtwlink(pt_cur, 1, val);|
      dbms_hs_passthrough.get_values@gtwlink(pt_cur, 2, age);

      INSERT INTO test_table VALUES(val);
    END LOOP;
    COMMIT;
    EXCEPTION
  WHEN NO_DATA_FOUND THEN
    BEGIN
      dbms_output.put_line('Processing Complete');
      dbms_hs_passthrough.close_cursor@gtwlink(pt_cur);
    END;
  END;
END;
/

Related Topics
Built-in Functions
Built-in Packages
Database Links
DBMS_HS_PARALLEL
DBMS_HS_PARALLEL_METADATA
What's New In 19c
What's New In 20c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx