dbms_hs_passthrough–the magic package

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
DECLARE
l_cursor    BINARY_INTEGER;
l_id VARCHAR2(60);
l_temp      VARCHAR2(250);
l_notes     VARCHAR2(32767);
BEGIN
l_cursor := DBMS_HS_PASSTHROUGH.open_cursor@remotedb;
DBMS_HS_PASSTHROUGH.parse@remotedb(
l_cursor,
‘select “RecId”,”Notes” from “MySqlServerTable”‘
);

    WHILE DBMS_HS_PASSTHROUGH.fetch_row@remotedb(l_cursor) > 0
LOOP
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);
END LOOP;

    DBMS_HS_PASSTHROUGH.close_cursor@remotedb(l_cursor);
exception
when others then
DBMS_HS_PASSTHROUGH.close_cursor@remotedb(l_cursor);
raise;
END;
/

Advertisements

4 thoughts on “dbms_hs_passthrough–the magic package

    • 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

      Cheers
      Connor

  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.

    declare
    val timestamp;
    crs integer;
    nr integer;
    begin
    crs := dbms_hs_passthrough.open_cursor@holink2;
    dbms_hs_passthrough.parse@holink2(crs,
    ‘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_hs_passthrough.close_cursor@holink2(crs);
    dbms_output.put_line (‘ts: ‘ || to_char (val));
    end;
    /

    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.

Leave a Reply

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 )

Google+ photo

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

Connecting to %s