dbms_hs_passthrough–the magic package

July 18, 2012

1 minute

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"'
);

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;

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

4 responses to “dbms_hs_passthrough–the magic package”

Paradox

September 18, 2013 at 9:37 pm

What about running of “storedproc” on “sqlserver”?

Reply
1. connormcdonald
  
  September 19, 2013 at 3:47 pm
  
  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
  
  Reply
eric

January 12, 2017 at 3:28 am

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.

Reply
1. connormcdonald
  
  January 12, 2017 at 8:49 am
  
  Thanks for stopping by Eric.
  
  Cheers,
  Connor
  
  Reply