Oracle Transparent / Database Gateway Call to SQL Server Stored Procdure
Posted on 2009-07-14
We have Oracle Transparent Gateway (TG4MSQL or DG4MSQL) installed so that we can connect to and interact with Microsoft SQL Server 2005 and 2008. The gateway is running on an 11g database.
We have the link set up to the SQL database that we want. Select and Insert queries seem to run fine, but we are having some issues executing a stored procedure that was written on the SQL Server side.
Here is the code that I would use to execute the SP on the SQL Server side:
@HireDate = '6/25/2009',
@Company = '300',
@Department = '30188888',
@FirstName = 'TEST',
@EmployeeNum = '123456',
@JobCode = '0998',
@LastName = 'USER',
@EmpStatus = 'FT',
@MidInitial = 'T',
@NickName = 'TESTER',
@BadgeNumber = '112233',
@Exempt = 'Y',
@Role = 'ADROLE'
I have consulted MetaLink Doc ID 197192.1 and come up with what I think we need to do based on the second method under Sample 1 in the article. We do not need any data to come back from the execution of the sp. The SP on the SQL side only creates new records, there are no return values.
Here is what we have on the Oracle side that is not working:
inputvars VARCHAR2(500) := "@Company='300',@EmployeeNum='345400',@FirstName='TEST',@MidInitial='T',@LastName='USER',@NickName='TESTER',@EmpStatus='FT',@HireDate='06/25/2009',@Department='3018888',@JobCode='0998',@BadgeNumber='112233',@Exempt='Y',@Role='ADROLE'";
result := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@ARSYSTEM('EXEC dbo.Law_MCDB_Import_INSERT' ||' ' || Chr(39) || inputvars || Chr(39));
The error we get is:
ORA-01948: identifier's name length (232) exceeds maximum (30)
We have tried many variations of the code above, but nothing seems to work.