Solved

Oracle Transparent / Database Gateway Call to SQL Server Stored Procdure

Posted on 2009-07-14
7
2,137 Views
Last Modified: 2012-06-21
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_INSERT]
@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',@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'";
BEGIN
   result := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@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.

0
Comment
Question by:cortilius
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 24852715
The maximum length of Oracle names is 30 characters.
Thats the reason.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24852744
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;
0
 

Author Comment

by:cortilius
ID: 24852764
In your example, is that code you are executing across a transparent gateway or is it internal within Oracle?
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 48

Expert Comment

by:schwertner
ID: 24853070
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.
0
 

Author Comment

by:cortilius
ID: 24859459
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.EXECUTE_IMMEDIATE 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;

Open in new window

0
 
LVL 48

Expert Comment

by:schwertner
ID: 24859922
What about the EXCEPTION section at the end that will indicate errors:

EXCEPTION
     WHEN others THEN
       DBMS_OUTPUT.PUT_LINE('Other: '||substr(sqlerrm,1,60));

before running

SET SERVEROUTPUR ON

to see the error message.
0
 

Accepted Solution

by:
cortilius earned 0 total points
ID: 24909072
I ended up having to concatenate the ascii values of the @ symbol and the ' mark to get the syntax correct.
DECLARE
   result           VARCHAR2(200);
   sqlstmt          varchar2(1000);
   Company          varchar2(100)  :=  chr(64)||'Company='    ||chr(39)||'300'||chr(39);
   EmployeeNum      varchar2(100)  :=  chr(64)||'EmployeeNum='||chr(39)||to_char(sysdate,'hh'||'mi'||'ss')||chr(39);
   FirstName        varchar2(100)  :=  chr(64)||'FirstName='  ||chr(39)||'ORACLE'||chr(39);
   MidInitial       varchar2(100)  :=  chr(64)||'MidInitial=' ||chr(39)||'T'||chr(39);
   LastName         varchar2(100)  :=  chr(64)||'LastName='   ||chr(39)||'GATEWAY'||chr(39);
   NickName         varchar2(100)  :=  chr(64)||'NickName='   ||chr(39)||'DEV'||chr(39);
   EmpStatus        varchar2(100)  :=  chr(64)||'EmpStatus='  ||chr(39)||'FT'||chr(39);
   HireDate         varchar2(100)  :=  chr(64)||'HireDate='   ||chr(39)||to_char(sysdate,'mm/dd/yyyy')||chr(39);
   Department       varchar2(100)  :=  chr(64)||'Department=' ||chr(39)||'10185100'||chr(39);
   JobCode          varchar2(100)  :=  chr(64)||'JobCode='    ||chr(39)||'0893'||chr(39);
   BadgeNumber      varchar2(100)  :=  chr(64)||'BadgeNumber='||chr(39)||'223344'||chr(39);
   ExemptFlag       varchar2(100)  :=  chr(64)||'Exempt='     ||chr(39)||'Y'||chr(39);
   ADRole           varchar2(100)  :=  chr(64)||'Role='       ||chr(39)||'SMH-FINANCIAL ANALYST'||chr(39);
 
BEGIN
    
   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      ;
   
   dbms_output.put_line ('SQLSTMT - ' || sqlstmt );
   
   result := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@ARSYSTEM (sqlstmt);
   
   commit;
   
   
END;

Open in new window

0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need to Implment Subset of Oracle Schema to Read-Only version 7 37
SQL Query 9 29
SQL Job Failed 6 31
Sql group by orderdate and ordertotal 3 12
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question