Reading TABLE of RECORDs

[sjt]

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]

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]

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]

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.