CODEWITHSUNDEEP
c#oracle-databaseodp.net
user1863635
user1863635

Reputation: 899

odp.net run oracle stored procedure with output parameter of user defined parameter

This procedure has address_table out pkg.address_tab_type

create or replace
procedure hr.p_get_address
  (
    in_pid in number,
    address_table out pkg.address_tab_type
  )
as
  address_row pkg.address_rec_type;
  cursor address_cursor is 
    select
      addr.pid, addr.street, addr.city
    from
      hr.address addr
    where
      pid = in_pid;
begin
  row_count := 0;
  open address_cursor;
  loop
    fetch address_cursor into address_row;
    exit when address_cursor%NOTFOUND;
    row_count := row_count + 1;
    address_table(row_count) := address_row;
  end loop;
  close address_cursor;
end p_get_address;

My pkg declares custom address_rec_type and address_tab_type:

create or replace
package hr.pkg as
  type address_rec_type is
    record
    (
      pid address.pid%type,
      street address.street%type,
      city address.city%type
    );
  type address_tab_type is
    table of address_rec_type index by binary_integer;
end hr.pkg;

When I execute my stored procedure in SQL Developer everything work file. So, my proc. works. I need to call this procedure and get address_table in C# (ASP.NET). I am using odp.net. I am able to call procedure with refCursor, works fine. How do I call procedure with output parameter of a user defined type?

Upvotes: 0

Views: 1760

Answers (1)

Michael O'Neill
Michael O'Neill

Reputation: 954

You will not be able to use package-defined record data types as parameters with ODP.NET. You can used database-defined types (i.e., defined using a CREATE TYPE statement) but answering how to do that is outside the scope of your question.

So your options are create database-defined types (and learn how to use the Abstract Data Type API of ODP.NET or to change your procedure parameter list as a decomposition the primative data types of the package-defined record data type.

Upvotes: 1

Related Questions