Devart Forums

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

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:

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

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:

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

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. 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