Almost complete noob needs assistance
-
- Posts: 5
- Joined: Fri 03 Aug 2012 17:04
Almost complete noob needs assistance
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
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
-
- Posts: 5
- Joined: Fri 03 Aug 2012 17:04
Re: Almost complete noob needs assistance
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?
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?
-
- Posts: 5
- Joined: Fri 03 Aug 2012 17:04
Re: Almost complete noob needs assistance
Really need some help from the Devart guys please. This is very frustrating.
Re: Almost complete noob needs assistance
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
VB
Please tell us how we should modify this code (or script) to reproduce the issue in our environment.
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;
/
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
-
- Posts: 5
- Joined: Fri 03 Aug 2012 17:04
Re: Almost complete noob needs assistance
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
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
Re: Almost complete noob needs assistance
We need the DDL/DML script of your stored procedure and related database objects to reproduce the issue in our environment.JeffTaylor008 wrote: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.
Be specific - please post here the link(s) to the corresponding post(s).JeffTaylor008 wrote:I've seen elsewhere on the forum intimations that there are problems running stored procedures from the v7 dotconnect.
-
- Posts: 5
- Joined: Fri 03 Aug 2012 17:04
Re: Almost complete noob needs assistance
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.
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.
Re: Almost complete noob needs assistance
We have answered you by e-mail.