?
Solved

Error calling an Oracle stored procedure from SQL Server

Posted on 2009-04-20
3
Medium Priority
?
1,183 Views
Last Modified: 2013-11-22
I have an Oracle Linked Server en SQL Server 2005.  I can call an Oracle's stored procedure from SQL Server 2005 and the Oracle's Data is updated.  There is a trigger in a  SQL Server table which call the oracle stored procedure, when the stored procedure is called from trigger return the error : OLE DB provider "OraOLEDB.Oracle" for linked server "XXX" returned message "ORA-02089: COMMIT is not allowed in a subordinate session.  I need to know how fix that error.
Calling stored procedure from SQL Server Editor:
 
declare @lc_err CHAR(5)
declare @lc_err2 CHAR(254);
declare @return_status int;
 
EXECUTE ('begin  PKGRLINVOICE.PRCHANGESTATUS (?,?,?,?,?); end;', 'AAAAA', 111111, 'H', @lc_err OUTPUT,@lc_err2 OUTPUT) AT XXX; 
 
Trigger in SQL Server 2005:
 
USE [Envio]
GO
/****** Object:  Trigger [dbo].[trg_update_icc_status]    Script Date: 04/20/2009 16:34:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- Batch submitted through debugger: SQLQuery1.sql|7|0|C:\Documents and Settings\bigdog\Local Settings\Temp\2\~vs8.sql
-- Batch submitted through debugger: SQLQuery3.sql|7|0|C:\Documents and Settings\bigdog\Local Settings\Temp\1\~vs1F.sql
--THE EVALUATION VERSION TRIMS COLUMN NAMES AND VARIABLES TO 7 CHARACTERS
--
ALTER trigger [dbo].[trg_update_XXXX] on [dbo].[RECEIVER]
after update 
   AS
   DECLARE @SWV_NE CHAR(10)
   DECLARE @SWV_NE2 NUMERIC(15,0)
   DECLARE @SWV_NE3 CHAR(2)
   DECLARE @SWV_Cu CURSOR
   SET @SWV_Cu = CURSOR  FOR SELECT ID_BRAN,ID_RECE,ID_FLAG FROM inserted
   OPEN @SWV_Cu
   FETCH NEXT FROM @SWV_Cu INTO @SWV_NE,@SWV_NE2,@SWV_NE3
   WHILE @@FETCH_STATUS = 0
   begin
 
      declare @lc_err CHAR(5)
      declare @lc_err2 CHAR(254)
      --set @lc_err = '11111'
      --set @lc_err2 = '                                                                                                               '
      DECLARE @ID_BRANCH CHAR(6);
      DECLARE @ID_FLAG_RECEIVER CHAR(1);
      SET @ID_BRANCH = LTRIM(RTRIM(@SWV_NE))
      SET @ID_FLAG_RECEIVER = LTRIM(RTRIM(@SWV_NE3))
      
      EXECUTE ( 'begin PKGRLINVOICE.PRCHANGESTATUS (?,?,?,?,?); end;', @ID_BRANCH, @SWV_NE2, @ID_FLAG_RECEIVER, @lc_err OUTPUT,@lc_err2 OUTPUT) AT XXX; 
      
      --EXECUTE sp_change_status @SWV_NE,@SWV_NE2,@SWV_NE3,@lc_err OUTPUT,@lc_err2 OUTPUT
      FETCH NEXT FROM @SWV_Cu INTO @SWV_NE,@SWV_NE2,@SWV_NE3
   end
   CLOSE @SWV_Cu
 
 
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_update_icc_status]', @order=N'First', @stmttype=N'UPDATE'

Open in new window

0
Comment
Question by:freyeso
3 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24199902
Just out of curiosity, is there a reason you posted this in this list?
"Home Programming Languages Pascal Delphi Curiosities" may not be the best place for an answer to this question. ;-)
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 24210576
So let me get this, in a trigger you have a cursor that at each iteration executes a stored procedure on an Oracle linked server. I have only one advise and I am not ironic at all:

DROP THAT TRIGGER and implement another solution.
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 24237144
I agree with Zberteoc. DON'T! It's a bad idea, resulting in distributed transactions, which can cause a lot of trouble. In most cases, it is not worth it.

The error message is a result of the remote stored procedure issueing an implicit (autocommit) or explicit commit. You cannot prevent the stored procedure doing so from the trigger, but you can change the stored procedure itself. This might cause other issues when used in a changed environment.

0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

840 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