Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
-
sjt
- Posts: 1
- Joined: Tue 05 Apr 2005 14:22
Post
by sjt » Tue 05 Apr 2005 14:29
I am currently evaluating OraDirect.NET 2.5 and need to be able to execute a package that returns a TABLE of RECORDs, eg
Code: Select all
CREATE OR REPLACE
PACKAGE SJT_Test AS
TYPE NAME_RECORD IS RECORD
( lastname people.LAST_NAME%TYPE,
firstname people.FIRST_NAME%TYPE);
TYPE NAME_TABLE IS TABLE OF NAME_RECORD INDEX BY BINARY_INTEGER;
PROCEDURE Get_name_table
( name_info OUT NAME_TABLE );
END SJT_Test;
Do you have an example that shows this?
Many thanks
Stuart
-
Paul
- Posts: 725
- Joined: Thu 28 Oct 2004 14:06
Post
by Paul » Thu 07 Apr 2005 09:21
You cannot work with TABLE of RECORD in OraDirect.Net. Please use CURSOR type.
Code: Select all
CREATE PACKAGE SJT_Test AS
TYPE NAME_RECORD IS RECORD
( lastname people.LAST_NAME%TYPE,
firstname people.FIRST_NAME%TYPE);
type NAME_TABLE IS REF CURSOR RETURN NAME_RECORD;
procedure Get_name_table(RSET OUT NAME_TABLE);
END SJT_Test;
CREATE PACKAGE BODY SJT_Test AS
procedure Get_name_table(RSET OUT NAME_TABLE)
IS
BEGIN
open RSET for select lastname, firstname from people;
END Get_name_table;
END SJT_Test;
OracleConnection con = new OracleConnection("Server=ORA;User Id=scott;Password=tiger;");
con.Open();
OracleCommand command = new OracleCommand("SJT_Test.Get_name_table");
command.Connection = con;
command.ParameterCheck = true;
command.CommandType = CommandType.StoredProcedure;
IDataReader reader = command.ExecuteReader(); //result recordset is here
while (reader.Read()) {
}
-
Arun Chowdhary
Post
by Arun Chowdhary » Wed 15 Jun 2005 06:41
Hi,
I have also same problem, I want to return a Table of Records from my stored procedure as in above post. can you suggest me any workaround for that. As you gave an example of using ref cursor but in my case it will not work. I have to return a table of records from my procedure, in case of ref cursor I can not insert values in ref curosr, I want to insert values in rows (in an array) from my procedure.
regards,
Arun
-
Oleg
- Devart Team
- Posts: 264
- Joined: Thu 28 Oct 2004 13:56
Post
by Oleg » Wed 15 Jun 2005 16:04
You can see a sample of working with REF CURSOR in our demo project RefCursor.
Also for your task you can use OracleArray type, a sample for its using is Array.
See also examples of using in the documentation for OraDirect .NET.