cortilius
asked on
Oracle Transparent / Database Gateway Call to SQL Server Stored Procdure
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:
EXEC
[dbo].[Law_MCDB_Import_INS ERT]
@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:
DECLARE
result VARCHAR2(200);
inputvars VARCHAR2(500) := "@Company='300',@EmployeeN um='345400 ',@FirstNa me='TEST', @MidInitia l='T',@Las tName='USE R',@NickNa me='TESTER ',@EmpStat us='FT',@H ireDate='0 6/25/2009' ,@Departme nt='301888 8',@JobCod e='0998',@ BadgeNumbe r='112233' ,@Exempt=' Y',@Role=' ADROLE'";
BEGIN
result := DBMS_HS_PASSTHROUGH.EXECUT E_IMMEDIAT E@ARSYSTEM ('EXEC dbo.Law_MCDB_Import_INSERT ' ||' ' || Chr(39) || inputvars || Chr(39));
END;
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.
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:
EXEC
[dbo].[Law_MCDB_Import_INS
@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:
DECLARE
result VARCHAR2(200);
inputvars VARCHAR2(500) := "@Company='300',@EmployeeN
BEGIN
result := DBMS_HS_PASSTHROUGH.EXECUT
END;
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.
EXECUTE IMMEDIATE
statement has other syntax.
See at this example:
DECLARE
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := 'PERSONNEL';
location VARCHAR2(13) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING dept_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;
statement has other syntax.
See at this example:
DECLARE
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := 'PERSONNEL';
location VARCHAR2(13) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING dept_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;
ASKER
In your example, is that code you are executing across a transparent gateway or is it internal within Oracle?
Transparent gateway or not, this is the code Oracle expects to receive.
Apparently it received something similar but slightly screwed up.
The main question is how to fix the code.
I see mixture of double quotes, single quotes, character @ and other things.
Will recommend to write the learn Oracle syntax and to follow the rules.
Apparently it received something similar but slightly screwed up.
The main question is how to fix the code.
I see mixture of double quotes, single quotes, character @ and other things.
Will recommend to write the learn Oracle syntax and to follow the rules.
ASKER
Actually, the gateway does not necessarily work the same way as direct PL/SQL to an Oracle Database. I am specifically having to use the DBMS_HS_PASSTHROUGH.EXECUT E_IMMEDIAT E package, which is a bit different than the regular execute immedaite package. I followed your suggestion and modified my code to build the command differently. Now I do not get an error message, but there is no record created on the SQL Server side. My new code is attached.
DECLARE
result VARCHAR2(200);
sqlstmt varchar2(1000);
returnval varchar2(100);
Company varchar2(100) := chr(39)||'300'||chr(39);
EmployeeNum varchar2(100) := chr(39)||'111111'||chr(39);
FirstName varchar2(100) := chr(39)||'TEST'||chr(39);
MidInitial varchar2(100) := chr(39)||'T'||chr(39);
LastName varchar2(100) := chr(39)||'USER'||chr(39);
NickName varchar2(100) := chr(39)||'TESTER'||chr(39);
EmpStatus varchar2(100) := chr(39)||'FT'||chr(39);
HireDate varchar2(100) := chr(39)||'07/14/2009'||chr(39);
Department varchar2(100) := chr(39)||'11185111'||chr(39);
JobCode varchar2(100) := chr(39)||'0893'||chr(39);
BadgeNumber varchar2(100) := chr(39)||'112233'||chr(39);
ExemptFlag varchar2(100) := chr(39)||'N'||chr(39);
ADRole varchar2(100) := chr(39)||'SMH-FIN'||chr(39);
BEGIN
returnval := '';
sqlstmt := 'EXECUTE [dbo].[Law_MCDB_Import_INSERT] ';
sqlstmt := sqlstmt || company || ', ';
sqlstmt := sqlstmt || employeenum || ', ';
sqlstmt := sqlstmt || firstname || ', ';
sqlstmt := sqlstmt || midinitial || ', ';
sqlstmt := sqlstmt || lastname || ', ';
sqlstmt := sqlstmt || nickname || ', ';
sqlstmt := sqlstmt || empstatus || ', ';
sqlstmt := sqlstmt || hiredate || ', ';
sqlstmt := sqlstmt || department || ', ';
sqlstmt := sqlstmt || jobcode || ', ';
sqlstmt := sqlstmt || badgenumber || ', ';
sqlstmt := sqlstmt || exemptflag || ', ';
sqlstmt := sqlstmt || adrole ;
result := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@ARSYSTEM (sqlstmt);
END;
What about the EXCEPTION section at the end that will indicate errors:
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Othe r: '||substr(sqlerrm,1,60));
before running
SET SERVEROUTPUR ON
to see the error message.
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Othe
before running
SET SERVEROUTPUR ON
to see the error message.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thats the reason.