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
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;
/
Learning is not a spectator sport 
What about running of “storedproc” on “sqlserver”?
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
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.
Thanks for stopping by Eric.
Cheers,
Connor