Error using OraDirect.Net accessing DBLINK

Error using OraDirect.Net accessing DBLINK

Postby admin » Tue 07 Apr 2009 21:35

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_Param1 IN ViewName.Column1@DBLinkName%TYPE  
  , p_Param2 IN ViewName.Column2@DBLinkName%TYPE  
  OPEN p_Cursor FOR  
  FROM ViewName@DBLinkName 
  WHERE Column1 = p_Param1 
    AND Column2 = p_Param1;
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.
  p_Cursor SYS_REFCURSOR; 
  p_Param1 NUMBER; 
  p_Param2 NUMBER; 
  p_Param1 := NULL; 
  p_Param2 := NULL; 
    p_Param1 => p_Param1, 
    p_Param2 => p_Param2 
   COMMIT;‐‐No Issue if this line is commented  

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

Site Admin
Posts: 41
Joined: Fri 22 Oct 2004 06:50

Postby Shalex » Wed 08 Apr 2009 07:44

Please wrap your code using OracleTransaction. This should resolve the problem.
Refer to the sample from our online documentation: , the OracleTransaction class.

Please notify us about the results.
Devart Team
Posts: 7149
Joined: Thu 14 Aug 2008 12:44

Postby xcbroker » Thu 09 Apr 2009 16:25

Thank you. That is the solution and it is now working after a transaction is wrapped around the call.
Posts: 21
Joined: Tue 07 Apr 2009 21:25

Return to dotConnect for Oracle