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");