Devart Forums

We have defined a stored procedure in Oracle 10g database that retrieves data from a DBLink pointing to a SQL Server 2005 database.

The following is the stored procedure:

CREATE OR REPLACE procedure GDOTUser.pr_ProcName 
(
 
  p_Cursor OUT SYS_REFCURSOR 
  , p_Param1 IN ViewName.Column1@DBLinkName%TYPE  
  , p_Param2 IN ViewName.Column2@DBLinkName%TYPE  
)
 
AS 
BEGIN 
  OPEN p_Cursor FOR  
  SELECT *
  
  FROM ViewName@DBLinkName 
  WHERE Column1 = p_Param1 
    AND Column2 = p_Param1;
 
END;
 
The above stored procedure executes fine in Oracle client tool such as TOAD, SQL Developer and etc.

However using OraDirect.NET in code produced the following error:

The following error occurred: An error occured while connecting to database: Error = ORA-01002: fetch out of sequence ORA-02063: preceding line from DBName ORA-02063: preceding 2 lines from DBLinkName; Command Type = StoredProcedure; Command= GDOTUser.pr_ProcName;

The issue seems to be with DBLink if it points to a SQL Server database.
 
If I run the above stored procedure using the following script, I get the same error. But if u comment the “Commit;” line, the procedure executes successfully without any issues.
 
CREATE OR DECLARE 
  p_Cursor SYS_REFCURSOR; 
  p_Param1 NUMBER; 
  p_Param2 NUMBER; 
BEGIN 
  p_Param1 := NULL; 
  p_Param2 := NULL; 
  PR_FIND_CONTRACT_BYKEY( 
    P_CURSOR => P_CURSOR, 
    p_Param1 => p_Param1, 
    p_Param2 => p_Param2 
  );
 
   COMMIT;‐‐No Issue if this line is commented  
END;

Please help. We are using OraDirect.Net version 4.60.33.0 for OraLab.Oracle.dll and 4.50.21.0 for OraLab.Data.dll.

Thanks,
Sean

Please wrap your code using OracleTransaction. This should resolve the problem.
Refer to the sample from our online documentation:
http://www.devart.com/dotconnect/oracle/docs/ , the OracleTransaction class.

Please notify us about the results.

Thank you. That is the solution and it is now working after a transaction is wrapped around the call.