Error using OraDirect.Net accessing DBLINK

[admin]

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

[Shalex]

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.

[xcbroker]

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