Reading TABLE of RECORDs

Reading TABLE of RECORDs

Postby 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
sjt
 
Posts: 1
Joined: Tue 05 Apr 2005 14:22

Postby 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()) {
      }
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Is there any work around for returning Table of Records from stored procedures

Postby 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
Arun Chowdhary
 

Postby 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.
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56


Return to dotConnect for Oracle