Fetch Out of Sequence Error using SQL DBLink

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
MrTOracle
Posts: 3
Joined: Mon 11 Aug 2014 13:26

Fetch Out of Sequence Error using SQL DBLink

Post by MrTOracle » Mon 11 Aug 2014 14:17

We have the same issue as in this article http://forums.devart.com/viewtopic.php?t=14581 back in 2009. I am not sure how they resolved it. We are using stored procedure in Oracle database that retrieves data from a DBLink pointing to a SQL Server database. The following is the stored procedure:

create or replace PROCEDURE SPRPTCROSSINGACCIDENTS
(
p_recordset OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_recordset FOR
SELECT *
FROM TRAIN_ACCIDENTS@DBLinkName

END SPRPTCROSSINGACCIDENTS;

The above stored procedure executes fine in Oracle client tool; TOAD, SQL Developer and SQL Plus. When I called it from VB Web Application the following error occurred: The following error occurred: ORA-01002: fetch out of sequence
ORA-02063: preceding line from DBLinkName...

The issue seems to be with DBLink when it points to a SQL Server database and when it returns a resultset using cursor. But when it returns a single row or a field, it works fine.
On the last article same issue someone did mention about using OracleTransaction. We do not plan to put any SQL codes in the VB Application. As far as I understand it, I only use Oracle Transaction when using Update or Insert statement and not in Select statement. Any help regarding getting this resolve will be greatly appreciated.

Thanks,
Chen

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Fetch Out of Sequence Error using SQL DBLink

Post by Pinturiccio » Thu 14 Aug 2014 15:12

MrTOracle wrote:On the last article same issue someone did mention about using OracleTransaction. We do not plan to put any SQL codes in the VB Application. As far as I understand it, I only use Oracle Transaction when using Update or Insert statement and not in Select statement. Any help regarding getting this resolve will be greatly appreciated.
The "ORA-02063" error can occurs during select statement through database link and not in transaction. For more information, please refer to http://www.dba-oracle.com/t_ora_02063_p ... string.htm

In order to fix the issue it's enough to start a transaction before executing the command:

Code: Select all

Dim trans = connection.BeginTransaction()

MrTOracle
Posts: 3
Joined: Mon 11 Aug 2014 13:26

Re: Fetch Out of Sequence Error using SQL DBLink

Post by MrTOracle » Tue 19 Aug 2014 15:26

We checked the remote database DBLink and it's only a SQL View to multiple tables. There is no setting on the DBLink to make it a transaction. Neither nor in the SQL View database. I am not sure how to do a transaction on the Oracle store procedure since it only contains SELECT statement. You did mentioned use Code: Dim trans = connection.BeginTransaction(). It looks to me it is in the VB codes. On the VB codes we only calling the CommandType.StoredProcedure and returning OracleDbType.RefCursor using Oracle.DataAccess.Client (Oraclient11g). I don't see where I can put this code that you mentioned. My supervisor insisted that there should be a way to do it in Oracle store procedure calling SQL DBLink to SQL Server View in data warehouse. In Oracle database we use SQL Server ODBC connection to the DBLink. Can a Oracle store procedure returning resultset (multiple rows) without using ref_Cursor? Is it possible to wrap a Transaction inside a select statement? I keep hearing that transaction need to be used. If you have ideas to fix this error, please send some instructions and examples will be appreciated.

Thanks,
Chen

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Fetch Out of Sequence Error using SQL DBLink

Post by Pinturiccio » Wed 20 Aug 2014 14:01

MrTOracle wrote:You did mentioned use Code: Dim trans = connection.BeginTransaction(). It looks to me it is in the VB codes. On the VB codes we only calling the CommandType.StoredProcedure
Please send us the sample of code, where you assign the CommandType.StoredProcedure value.
To the left of this value must be a variable, the property of which is set to this value. Please send us the code of the variable declaration.
MrTOracle wrote:and returning OracleDbType.RefCursor using Oracle.DataAccess.Client (Oraclient11g)
According to this, you use ODP.NET in your project, not dotConnect for Oracle. dotConnect for Oracle has a Migration Wizard that allows migrating code, using ODP.NET, to dotConnect for Oracle. For more information, please refer to http://www.devart.com/dotconnect/oracle ... ation.html.
MrTOracle wrote:Can a Oracle store procedure returning resultset (multiple rows) without using ref_Cursor?
Yes, you can create user-defined object type that will store all the columns of your query, and then work with this type. For more information about working with user-defined object type see the Oracle documentation.
MrTOracle wrote:Is it possible to wrap a Transaction inside a select statement?
The transaction begins automaticly with the first executable SQL statement. A transaction ends when it is explicitly committed or rolled back by user. For more information, please refer to http://docs.oracle.com/cd/B19306_01/ser ... #sthref266

You can add the COMMIT statement to your procedure in the way, demonstrated below, but it won't fix the issue:

Code: Select all

create or replace PROCEDURE SPRPTCROSSINGACCIDENTS
(
p_recordset OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_recordset FOR
SELECT *
FROM TRAIN_ACCIDENTS@DBLinkName;
COMMIT;

END SPRPTCROSSINGACCIDENTS;

MrTOracle
Posts: 3
Joined: Mon 11 Aug 2014 13:26

Re: Fetch Out of Sequence Error using SQL DBLink

Post by MrTOracle » Wed 20 Aug 2014 22:45

Here a copy of the VB codes:

Using oracleConn As New OracleConnection(connection)
Try
Dim cmd As New OracleCommand("SPRPTCROSSINGACCIDENTS", oracleConn)
With {.CommandType = CommandType.StoredProcedure}
cmd.Parameters.Add(New OracleParameter("p_recordset", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

Dim da As New OracleDataAdapter(cmd)
da.TableMappings.Add("Table", "Accidents")
da.Fill(accData)

If accData.Tables("Accidents").Rows.Count > 0 Then

discreteVal.Value = subtitle
paramField.Name = "subtitle"
paramField.CurrentValues.Add(discreteVal)
paramFields.Add(paramField)

CrystalReportViewer1.ParameterFieldInfo = paramFields
accRpt.SetDataSource(accData)
CrystalReportViewer1.ReportSource = accRpt
Else
CrystalReportViewer1.Visible = False
lblNoResults.Visible = True
End If
Catch err As Exception
Throw New ApplicationException("Error occurred!!", err)
Finally
If oracleConn.State = ConnectionState.Open Then
oracleConn.Close()
End If
End Try
End Using

When I debugged the codes, the error occurred at "da.Fill(accData)" line. On the IIS site where the application resided, we use the light version of ODP.NET. When I use the actual table in the store procedure to select command, instead of the SQL Link, it works. So am still think is in the Store Procedure and not in the VB Codes application.

Thanks,
Chen

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Fetch Out of Sequence Error using SQL DBLink

Post by Pinturiccio » Thu 21 Aug 2014 11:32

The reason is both in the Visual Basic code and the stored procedure. If you replace select from the DBLink with the select from the table from the database, to which the application is connected, everything will work correctly. However, if you want to select data via a DBLink, you need to modify your code. The changes below fix the issue for dotConnect for Oracle, however, we are not sure whether they are correct for ODP.NET. If the issue is reproduced, contact the Oracle support, because ODP.NET is their product.

Code: Select all

Dim cmd As New OracleCommand("SPRPTCROSSINGACCIDENTS", oracleConn)
With {.CommandType = CommandType.StoredProcedure}
cmd.Parameters.Add(New OracleParameter("p_recordset", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

Dim da As New OracleDataAdapter(cmd)
da.TableMappings.Add("Table", "Accidents")
Dim check As Boolean = False
If oracleConn.State <> ConnectionState.Open Then
	oracleConn.Open()
	check = True
End If
oracleConn.BeginTransaction()
da.Fill(accData)
oracleConn.Commit()
If check = True Then
	oracleConn.Close()
End If
If you want to migrate from ODP.NET to dotConnect for Oracle, you can use the Migration Wizard of dotConnect for Oracle, as we have written before.

dotConnect for Oracle has a Trial version which allows you to evaluate dotConnect for Oracle during 30 days since the provider installation. Trial version of dotConnect for Oracle offers the same features as the Professional edition. You can download the Trial version of dotConnect for Oracle from our site http://www.devart.com/dotconnect/oracle/download.html

Post Reply