dbms_hs_passthrough–the magic package

Posted by

This is a package that you can call, that does not exist in the database.

I found this out after reading about its usage, and then running a DESCRIBE, and of course, finding nothing. Then I hunted through the familiar $ORACLE_HOME/rdbms/admin directory, looking for it….alas to no avail.

Why is it not there ? Because its fiction. Its essentially a mean of doing the equivalent of DBMS_SQL but on a remote database. For example, if HS is going via ODBC to SQL Server, then just about every ODBC driver supports the concept of parse, execute, fetch, and thus DBMS_HS_PASSTHROUGH lets you access the level on the remote database.

Hence in the example below, the database link REMOTEDB is a HS link to a SQL Server database. Obviously the DBMS_HS_PASSTHROUGH package does not exist in that database, but the code works nonetheless !

set serverout on
  l_cursor    BINARY_INTEGER;
  l_id VARCHAR2(60);
  l_temp      VARCHAR2(250);
  l_notes     VARCHAR2(32767);
  l_cursor := DBMS_HS_PASSTHROUGH.open_cursor@remotedb;
  'select "RecId","Notes" from "MySqlServerTable"'

  DBMS_HS_PASSTHROUGH.get_value@remotedb(l_cursor, 1, l_id);
  DBMS_HS_PASSTHROUGH.get_value@remotedb(l_cursor, 2, l_notes);
  DBMS_OUTPUT.put_line(l_id || ' ' || l_notes);

  when others then


    1. I’m not entirely sure – I’ve never tried it. But I would assume that if you can parse the command the command to execute a stored proc, then I’d imagine you should be able to try this – there’s probably restrictions on returning things like resultsets etc…

      Let me know how you go


  1. It depends on the capabilities of the driver (i.e the program= value of the listener entry the datatabase link resolves to. But if it is then something like the following would work using plsql anonymous block syntax
    if the stored procedure ps_inout_timestamp assigns its second argument a timestamp.

    val timestamp;
    crs integer;
    nr integer;
    crs := dbms_hs_passthrough.open_cursor@holink2;
    ‘begin ps_inout_timestamp(1, :e); end;’);
    dbms_hs_passthrough.bind_inout_variable@holink2(crs, 1, val);
    nr := dbms_hs_passthrough.execute_non_query@holink2(crs);
    dbms_hs_passthrough.get_value@holink2(crs, 1, val);
    dbms_output.put_line (‘ts: ‘ || to_char (val));

    Sqlserver syntax for anonymous block is more like { call ps_inout_timestamp(1,?) } I believe

    Note that better support for stored procedures is in “oracle gateway for sql server”
    including result sets etc.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.