Passing PL/SQL table as stored procedure parameter

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
bhav27
Posts: 20
Joined: Tue 20 Apr 2010 11:53

Passing PL/SQL table as stored procedure parameter

Post by bhav27 » Wed 05 May 2010 14:51

Hi
I'm trying to pass Pl/sql table as a parameter to the procedure. I've created an example as per sample provided with dotconnect for oracle. But for some reason this seems to be not supported.

Kindly, can someone give us correct way of doing this?

Following is the error we get

ORA-03115 unsupported network datatype or representation

connection code

Code: Select all

using (OracleConnection conn = new OracleConnection())
            {
                conn.ConnectionString = "replace with yours";
                OracleCommand cmd = new OracleCommand("PKG_SAMPLE.SP00GETLIST", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                
                //cmd.Parameters.Add("P_CUSTOMERS", OracleDbType.Number).Value = new decimal[] { 1, 2, 3, 4 };
                //cmd.Parameters["P_CUSTOMERS"].ArrayLength = 4;

                conn.Open();

                OracleType numtype = OracleType.GetObjectType("NUMBERLIST", conn);
                OracleTable table = new OracleTable(numtype);
                table.Add(1);
                table.Add(2);
                table.Add(3);
                table.Add(4);
                cmd.Parameters.Add("P_CUSTOMERS", OracleDbType.Table).Value = table;

                IDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine(reader[1]);
                }
            }

Code: Select all

CREATE TYPE NumberList IS TABLE OF NUMBER;

CREATE OR REPLACE PACKAGE PKG_SAMPLE IS

  -- Author  : BHAVESHP
  -- Created : 05/05/2010 15:09:17
  -- Purpose : 

  -- Public type declarations
  TYPE T_CURSOR IS REF CURSOR;

  PROCEDURE SP00GETLIST(P_CUSTOMERS IN NUMBERLIST, CUR_OUT OUT T_CURSOR);

END PKG_SAMPLE;

CREATE OR REPLACE PACKAGE BODY PKG_SAMPLE IS
  PROCEDURE SP00GETLIST(P_CUSTOMERS IN NUMBERLIST, CUR_OUT OUT T_CURSOR) AS
  BEGIN
    OPEN CUR_OUT FOR
      SELECT *
        FROM CUSTOMER
       WHERE CUSTOMERID IN (SELECT * FROM TABLE(P_CUSTOMERS));
  
  END SP00GETLIST;
END PKG_SAMPLE;

-- Create table
create table CUSTOMER
(
  CUSTOMERID NUMBER,
  NAME       VARCHAR2(50)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

insert into customer (1, "sample 1");
insert into customer (2, "sample 2");
insert into customer (3, "sample 3");
insert into customer (4, "sample 4");
[/code]

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 06 May 2010 15:16

Are you using the Direct connection mode? If yes, the problem is that Oracle objects, including nested tables, are not supported in the Direct mode. For more information on Direct mode peculiarities, please see the corresponding topic of our documentation:
http://www.devart.com/dotconnect/oracle ... tMode.html

Post Reply