Hello everyone. I'm trying to call a stored proc from Oracle using a db link. I know the db link works, because they have a view on their end that I can query using the db link and it works fine. From researching I see that using DBMS_HS_PASSTHROUGH is the recommended way to accomplish what I'm trying to accomplish. I wrote an anonymous block to run from TOAD to try and work through issues and get this working. In the example below I've replaced the actual proc name and db link names with generic names. I'm getting an error when I try and execute this code. This is my first time trying to call a sql server stored proc from Oracle and I'm sure I'm doing something wrong.
I run the anonymous block below:
declare
vCur integer;
nr integer;
vVal varchar2(2000);
vParChar varchar2(20) := '12345';
vParDate date := sysdate;
BEGIN
vString := 'EXEC proc_name_here @vParChar = '''||vParChar|| ''' @vParDate = '''||vParDate||''';';
vCur := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@dblink.dblink ;
DBMS_HS_PASSTHROUGH.PARSE@dblink.dblink (vCur, vString );
nr := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@dblink.dblink (vCur);
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@dblink.dblink (vCur);
commit;
END;
and I get the following error:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near '@vParDate'. {HY000,NativeErr = 102}
ORA-06512: at line 16
Line 16 in this case is nr := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@dblink.dblink (vCur);
I found a few general examples and tried to adapt them to the procedure I'm trying to call. I also realize I may be setting up things incorrectly. I tried creating the entire string of EXEC proc_name_here @vParChar = '12345' @vParDate = '9/16/2020 9:17:48 AM';
My thinking was that I could get the string in the format of calling a sql server stored procedure and then just have it run that on the other end.
I've looked online for the error that I'm getting but haven't been able to get it working yet.
Does anyone see any issues with the way I'm trying to call the stored procedure?
I greatly appreciate any help provided.