I am trying to invoke SP which has Associative Array(EMP_IDS_TAB) as one of the input from C#.
CREATE OR REPLACE TYPE EMP_IDS_TAB is table of varchar2(50);
/
PROCEDURE READ_PRECOMPUTE_SCHED_DATA(
p_in_start_date IN DATE,
p_in_end_date IN DATE,
p_in_emp_ids IN EMP_IDS_TAB,
p_in_system IN VARCHAR2,
p_out_precompute_data OUT SYS_REFCURSOR,
p_out_err_code OUT NUMBER,
p_out_err_msg OUT VARCHAR2)
AS
BEGIN
-- Sample code of using EMP_IDS_TAB
IF(p_in_emp_ids IS NOT NULL AND p_in_emp_ids.COUNT > 0)
THEN
load_gtt_emp(p_in_emp_ids);
END IF;
END;
/
Calling from C#
----------------------------
string constr = "user id=hr; password=hr; data source=oramag; enlist=false; pooling=false";
OracleConnection con = new OracleConnection(constr);
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "PKG_PRECOMPUTE_MGR.READ_PRECOMPUTE_SCHED_DATA";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_emp_ids = new OracleParameter();
p_emp_ids.OracleDbType = OracleDbType.Varchar2;
p_emp_ids.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_emp_ids.Value = new string[2] { "ID100027274110", "ID100027274168" };
p_emp_ids.Size = 2;
// other params
.
.
cmd.Parameters.Add(p_emp_ids);
.
.
cmd.ExecuteNonQuery();
Encountering with following issue at ExecuteNonQuery.
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'READ_PRECOMPUTE_SCHED_DATA'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Coded with reference to following link.
ODP.NET: Using PL/SQL Associative Arrays
ODP.NET Version: 4.112.3.0
Oracle Client: 11.2.0.3
Please share you inputs.