Almost complete noob needs assistance

[JeffTaylor008]

Hi all,

Need a little assistance. I need to run an oracle packaged stored procedure that returns a record set. I'm not allowed onto the Oracle database, and the package has been built for me by our Oracle team.

I have a simple function that connects to the database and runs the stored proc, but it's only returning the last entry in the record set. I've been playing withe the oracle datatable, hence the dt.fill command that doesn't do anything. I KNOW this is simple, but I'm lost in the woods, and just can't see the trees any more. Any advice will be most welcome.

Here's the function:
----------------------------------------------------------------------------

Private Function GetSingleNumericParameterProcedure(ByVal ProcedureName As String, ByVal InputValue As Integer) As OracleDataSet

'Connection string needs to use a system string contained within the web.config
Dim MyConnectionString As String = GetOracleDatabaseConnectionString() ' OracleDatabaseConnectionString

Dim MyConnection As New Devart.Data.Oracle.OracleConnection
MyConnection.ConnectionString = MyConnectionString

Dim MyDataSet As New Devart.Data.Oracle.OracleDataSet 'Data.DataSet

MyConnection.Open()


'Dim MyCommand As New OracleCommand(ProcedureName, MyConnection)
Dim MyCommand As New OracleCommand
MyCommand.CommandType = System.Data.CommandType.StoredProcedure
MyCommand.CommandText = ProcedureName
MyCommand.ParameterCheck = True
MyCommand.Connection = MyConnection

Dim InputParameter As OracleParameter = MyCommand.Parameters.Add("p_input", OracleDbType.Number)
InputParameter.Direction = ParameterDirection.Input
InputParameter.Value = InputValue

Dim OutputParameter As OracleParameter = MyCommand.Parameters.Add("p_record_set", OracleDbType.Cursor)
OutputParameter.Direction = ParameterDirection.Output

Dim dt = New OracleDataTable(MyCommand, MyConnection)
dt.Fill()

Using MyAdapter As New OracleDataAdapter(MyCommand)
MyAdapter.Fill(MyDataSet)
End Using

MyConnection.Close()
MyConnection.Dispose()

Return MyDataSet

End Function

[JeffTaylor008]

This is driving me round the bend, so any suggestions are most welcome.

My previous code snippet does return data, but it only returns the last row and not the whole output. I've run the package I was given using dbForge and got the following data (Cleaned for obvious reasons):

ID FLAG PERSON_ID
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
(null) Y (null)
9324299 Y (null)
9295430 Y 7239

So I know the package is returning the data I want to see, but my VB function only ever returns the last row. I've tried a data reader as well, and that's doing exactly the same thing! Any ideas on what I've done wrong?

[JeffTaylor008]

Really need some help from the Devart guys please. This is very frustrating.

[Shalex]

The following code fills both OracleDataTable and OracleDataSet correctly (set a breakpoint in the end of the Main method and check the content of your dt and MyDataSet).

DDL/DML

Code: Select all

CREATE TABLE DEPT (
  DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14) ,
  LOC VARCHAR2(13)
);

INSERT INTO DEPT VALUES
        (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
        (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
        (40,'OPERATIONS','BOSTON');

CREATE OR REPLACE PROCEDURE GET_DEPT_PROC(p_deptno IN NUMBER, p_cursor OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN p_cursor FOR
    SELECT *
      FROM DEPT WHERE DEPTNO > p_deptno;
END;
/

VB

Code: Select all

Imports Devart.Data.Oracle
Module Module1

    Sub Main()

        Dim MyConnectionString As String = "server=orcl1120;uid=scott;pwd=tiger;"

        Dim MyConnection As New Devart.Data.Oracle.OracleConnection
        MyConnection.ConnectionString = MyConnectionString

        Dim MyDataSet As New Devart.Data.Oracle.OracleDataSet

        MyConnection.Open()

        Dim MyCommand As New OracleCommand
        MyCommand.CommandType = System.Data.CommandType.StoredProcedure
        MyCommand.CommandText = "GET_DEPT_PROC"
        MyCommand.ParameterCheck = True
        MyCommand.Connection = MyConnection

        Dim InputParameter As OracleParameter = MyCommand.Parameters.Add("p_deptno", OracleDbType.Number)
        InputParameter.Direction = ParameterDirection.Input
        InputParameter.Value = 10

        Dim OutputParameter As OracleParameter = MyCommand.Parameters.Add("p_cursor", OracleDbType.Cursor)
        OutputParameter.Direction = ParameterDirection.Output

        Dim dt = New OracleDataTable(MyCommand, MyConnection)
        dt.Fill()

        Using MyAdapter As New OracleDataAdapter(MyCommand)
            MyAdapter.Fill(MyDataSet)
        End Using

        MyConnection.Close()
        MyConnection.Dispose()

    End Sub // set a breakpoint here

End Module

Please tell us how we should modify this code (or script) to reproduce the issue in our environment.

[JeffTaylor008]

Many thanks for the code. I don't have write/db access to our live Oracle system, so I'm building a dev server to run your database code against in order to verify it works properly.

In the meantime, I've taken your code, set the connection string to our Oracle 10g server, set the stored procedure to the one I was given and run it.

Both sets of code return the schema (As I got with my original code) with 3 columns, but no rows present. I've seen elsewhere on the forum intimations that there are problems running stored procedures from the v7 dotconnect. Before I put my british two-pennies in for that, I want to confirm that I can run your code against my dev installation. The only difference being that I don't have a copy of Oracle 10g, so I'm going with 11g. I will also pass your code on to our Oracle team so they can see how you created your SP and hopefully identify any differences in how our SP was created.

Many thanks for getting back to me, it's much appreciated (Especially by my hair)

Jeff

[Shalex]

I've taken your code, set the connection string to our Oracle 10g server, set the stored procedure to the one I was given and run it.
Both sets of code return the schema (As I got with my original code) with 3 columns, but no rows present.

We need the DDL/DML script of your stored procedure and related database objects to reproduce the issue in our environment.

I've seen elsewhere on the forum intimations that there are problems running stored procedures from the v7 dotconnect.

Be specific - please post here the link(s) to the corresponding post(s).

[JeffTaylor008]

Hi there,

Unfortunately, because this is an internal system (And managed by another team) I am unable to share the information you've requested in a public forum. We'll have to take it offline. You should have my email details from the request I submitted on Tuesday that you responded to. I'll contact you by replying to the original email.

As for other people having problems with stored procedures, I am referring to the post by edstaffin "Stored procedures still do not work" It is unlikely that this is a related problem though. I am currently suspecting how the stored procedure was put together.

[Shalex]

We have answered you by e-mail.