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
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”‘

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

2 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


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