SQL & PL/SQL

I am porting an app from Microsoft VB6 to Microsoft c#.

The last four parametes of the stored procedure are out table date, out table varchar, out table varchar, out table number.

In VB6 the code for executing this stored procedure is

Dim rs As ADODB.Recordset
Dim objCommand As ADODB.Command
Dim ConnPkg As New ADODB.Connection

rs = New ADODB.Recordset

rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic

objCommand = New ADODB.Command

objCommand.ActiveConnection = conn

strSQL = "{call PKG_LMP_VIEWER_DATA.GET_PRICES (?,?,?,?,?,?,?,?,?,?,?,?,?,{resultset 200000, paryTradeDt, paryDayofWeek, paryHREnding, paryPRICE})}"

objCommand.CommandText = strSQL
objCommand.CommandType = ADODB.CommandTypeEnum.adCmdText

'parameters...
objCommand.Parameters(0).Value = cmbControlArea1.Text
objCommand.Parameters(1).Value = UCase(cmbLocation1.Text)
objCommand.Parameters(2).Value = cmbType1.Text
objCommand.Parameters(3).Value = dtpBegDate.Value
objCommand.Parameters(4).Value = dtpEndDate.Value
objCommand.Parameters(5).Value = strSunParam
objCommand.Parameters(6).Value = strMonParam
objCommand.Parameters(7).Value = strTueParam
objCommand.Parameters(8).Value = strWedParam
objCommand.Parameters(9).Value = strThuParam
objCommand.Parameters(10).Value = strFriParam
objCommand.Parameters(11).Value = strSatParam
If Option1(0).Checked Then
objCommand.Parameters(12).Value = "Y"
Else
objCommand.Parameters(12).Value = "N"
End If

MainRS = objCommand.Execute()


I have tried all these .net technologies with only errors Oracle.DataAccess.Client, system.data.odbc, system.data.oracleclient, adodb. The following is my test app.
The errors are in comments below the execute statements.

QUESTION >>> Are "out table" parameters supported in any of these technologies ? If so, any hints on making it work ?

using System;
using System.Collections.Generic;
using System.Data;
using Oracle.DataAccess.Client;
using ADODB;
using System.Data.OracleClient;
using System.Data.OracleClient;
using System.Text;

namespace ArrayExample
{
class Program
{
static void Main()
{
#if true
// system.data.oracleclient

System.Data.OracleClient.OracleConnection ConnPkg = new System.Data.OracleClient.OracleConnection("Data Source=;UID=;PWD=");
//string strSQL = "{call PKG_LMP_VIEWER_DATA.GET_PRICES (?,?,?,?,?,?,?,?,?,?,?,?,?,{resultset 200000, paryTradeDt, paryDayofWeek, paryHREnding, paryPRICE})}";
//strSQL = "{call PKG_LMP_VIEWER_DATA.GET_PRICES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
string strSQL = "PKG_LMP_VIEWER_DATA.GET_PRICES";
System.Data.OracleClient.OracleCommand objCommand = new System.Data.OracleClient.OracleCommand(strSQL, ConnPkg);
objCommand.CommandType = CommandType.StoredProcedure;
//objCommand.CommandType = CommandType.Text;
objCommand.Parameters.Add("", OracleType.VarChar).Value = "PJM";
objCommand.Parameters.Add("", OracleType.VarChar).Value = "6TH_ST/69 KV/TR1/ATSI";
objCommand.Parameters.Add("", OracleType.VarChar).Value = "REALTIME";
objCommand.Parameters.Add("", OracleType.DateTime).Value = DateTime.Parse("November 1, 2011");
objCommand.Parameters.Add("", OracleType.DateTime).Value = DateTime.Now;
string hours = "8,9";
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = "Y";
// objCommand.Parameters.Add("", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleParameter p13 = objCommand.Parameters.Add("", OracleType.DateTime);
p13.Direction = ParameterDirection.Output;
p13.DbType = DbType.Object;
System.Data.OracleClient.OracleParameter p14 = objCommand.Parameters.Add("", OracleType.VarChar);
p14.Direction = ParameterDirection.Output;
p14.DbType = DbType.Object;
System.Data.OracleClient.OracleParameter p15 = objCommand.Parameters.Add("", OracleType.VarChar);
p15.Direction = ParameterDirection.Output;
p15.DbType = DbType.Object;
System.Data.OracleClient.OracleParameter p16 = objCommand.Parameters.Add("", OracleType.Number);
p16.Direction = ParameterDirection.Output;
p16.DbType = DbType.Object;

ConnPkg.Open();
System.Data.OracleClient.OracleDataReader rd = objCommand.ExecuteReader();
// ORA-06550: line 1, column 7:
// PLS-00306: wrong number or types of arguments in call to 'GET_PRICES'
// ORA-06550: line 1, column 7:
// PL/SQL: Statement ignored

// ORA-01036: illegal variable name/number

// ERROR [HY000] [Oracle][ODBC][Ora]ORA-06550: line 1, column 88:
// PLS-00201: identifier 'PARYTRADEDT' must be declared
// ORA-06550: line 1, column 7:
// PL/SQL: Statement ignored
bool res = rd.NextResult();
#endif
#if false
// system.data.odbc

System.Data.OracleClient.OracleConnection ConnPkg = new System.Data.OracleClient.OracleConnection("DSN=;UID=;PWD=r");
string strSQL = "{call PKG_LMP_VIEWER_DATA.GET_PRICES (?,?,?,?,?,?,?,?,?,?,?,?,?,{resultset 200000, paryTradeDt, paryDayofWeek, paryHREnding, paryPRICE})}";
//strSQL = "{call PKG_LMP_VIEWER_DATA.GET_PRICES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
//strSQL = "PKG_LMP_VIEWER_DATA.GET_PRICES";
System.Data.OracleClient.OracleCommand objCommand = new System.Data.OracleClient.OracleCommand(strSQL, ConnPkg);
//objCommand.CommandType = CommandType.StoredProcedure;
objCommand.CommandType = CommandType.Text;
objCommand.Parameters.Add("", OracleType.VarChar).Value = "PJM";
objCommand.Parameters.Add("", OracleType.VarChar).Value = "6TH_ST/69 KV/TR1/ATSI";
objCommand.Parameters.Add("", OracleType.VarChar).Value = "REALTIME";
objCommand.Parameters.Add("", OracleType.DateTime).Value = DateTime.Parse("November 1, 2011");
objCommand.Parameters.Add("", OracleType.DateTime).Value = DateTime.Now;
string hours = "8,9";
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = hours;
objCommand.Parameters.Add("", OracleType.VarChar).Value = "Y";
objCommand.Parameters.Add("", OracleType.Cursor).Direction = ParameterDirection.Output;
//objCommand.Parameters.Add("", OracleType.Cursor).Direction = ParameterDirection.Output;
//objCommand.Parameters.Add("", OracleType.Cursor).Direction = ParameterDirection.Output;
//objCommand.Parameters.Add("", OracleType.Cursor).Direction = ParameterDirection.Output;

ConnPkg.Open();
System.Data.OracleClient.OracleDataReader rd = objCommand.ExecuteReader();
// ERROR [HY000] [Oracle][ODBC][Ora]ORA-06550: line 1, column 88:
// PLS-00201: identifier 'PARYTRADEDT' must be declared
// ORA-06550: line 1, column 7:
// PL/SQL: Statement ignored
bool res = rd.NextResult();
#endif
#if false
// ADODB.net

ADODB.Connection con = new ADODB.Connection();
string strConnect = "Data Source=;User Id=;Password=";
con.ConnectionTimeout = 25;
con.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
con.Open(strConnect, "", "", -1);
string strSQL = "{call PKG_LMP_VIEWER_DATA.GET_PRICES (?,?,?,?,?,?,?,?,?,?,?,?,?,{resultset 200000, paryTradeDt, paryDayofWeek, paryHREnding, paryPRICE})}";
//strSQL = "PKG_LMP_VIEWER_DATA.GET_PRICES";
ADODB.Command objCommand = new ADODB.Command();
objCommand.ActiveConnection = con;
objCommand.CommandText = strSQL;
objCommand.CommandType = ADODB.CommandTypeEnum.adCmdText;
string hours = "8,9";
ADODB.Parameter par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 3, "PJM");
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 21, "6TH_ST/69 KV/TR1/ATSI");
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 8, "REALTIME");
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adDBDate, ADODB.ParameterDirectionEnum.adParamInput, 0, DateTime.Parse("November 1, 2011"));
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adDBDate, ADODB.ParameterDirectionEnum.adParamInput, 0, DateTime.Now);
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 3, hours);
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 3, hours);
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 3, hours);
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 3, hours);
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 3, hours);
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 3, hours);
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 3, hours);
objCommand.Parameters.Append(par);
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 1, "Y");
objCommand.Parameters.Append(par);
DateTime [] dates = new DateTime[20000];
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adArray | ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamOutput, 0, dates);
objCommand.Parameters.Append(par);
string[] strs1 = new string[20000];
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adArray | ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 0, strs1);
objCommand.Parameters.Append(par);
string[] strs2 = new string[20000];
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adArray | ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 0, strs2);
objCommand.Parameters.Append(par);
double [] dbls = new double[20000];
par = objCommand.CreateParameter(null, ADODB.DataTypeEnum.adArray | ADODB.DataTypeEnum.adDouble, ADODB.ParameterDirectionEnum.adParamOutput, 0, dbls);
objCommand.Parameters.Append(par);
/*
OracleParameter Param12 = cmd.Parameters.Add("", OracleDbType.Date);
Param12.Direction = ParameterDirection.Output;
Param12.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param12.Size = 20000;
OracleParameter Param13 = cmd.Parameters.Add("", OracleDbType.Varchar2);
Param13.Direction = ParameterDirection.Output;
Param13.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param13.Size = 20000;
Param13.ArrayBindSize = new int[20000]; for (int i = 0; i < 20000; i++) Param13.ArrayBindSize[i] = 20;
OracleParameter Param14 = cmd.Parameters.Add("", OracleDbType.Varchar2);
Param14.Direction = ParameterDirection.Output;
Param14.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param14.Size = 20000;
Param14.ArrayBindSize = new int[20000]; for (int i = 0; i < 20000; i++) Param14.ArrayBindSize[i] = 20;
OracleParameter Param15 = cmd.Parameters.Add("", OracleDbType.Double);
Param15.Direction = ParameterDirection.Output;
Param15.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param15.Size = 20000;
*/
object recsAffected = null;
int options = 0; // (int)ADODB.CommandTypeEnum.adCmdText;
object paramObj = null;
ADODB.Recordset MainRS = objCommand.Execute(out recsAffected, ref paramObj, options);
// "Type name is invalid." -2147217872
/*
//print out the parameter's values
Console.WriteLine("parameter values after executing the PL/SQL block");
for (int i = 0; i < 3; i++)
Console.WriteLine("Param2[{0}] = {1} ", i,
(cmd.Parameters[1].Value as Array).GetValue(i));

for (int i = 0; i < 3; i++)
Console.WriteLine("Param3[{0}] = {1} ", i,
(cmd.Parameters[2].Value as Array).GetValue(i));

// Close and Dispose OracleConnection object
con.Close();

*/
#endif
#if false
// Oracle.DataAccess.Client

Oracle.DataAccess.Client.OracleConnection con = new Oracle.DataAccess.Client.OracleConnection();

con.ConnectionString = "Data Source=;User Id=;Password=";
con.Open();
Console.WriteLine("Connected to Oracle.DataAccess.Client.Oracle" + con.ServerVersion);

Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("PKG_LMP_VIEWER_DATA.GET_PRICES", con);
cmd.CommandType = CommandType.StoredProcedure;
string hours = "8,9";
Oracle.DataAccess.Client.OracleParameter Param1 = cmd.Parameters.Add("1", Oracle.DataAccess.Client.OracleDbType.Varchar2, 3, "PJM");
Oracle.DataAccess.Client.OracleParameter Param2 = cmd.Parameters.Add("2", Oracle.DataAccess.Client.OracleDbType.Varchar2, 21, "6TH_ST/69 KV/TR1/ATSI");
Oracle.DataAccess.Client.OracleParameter Param3 = cmd.Parameters.Add("3", Oracle.DataAccess.Client.OracleDbType.Varchar2, 8, "REALTIME");
cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Date, DateTime.Now.ToString().Length, DateTime.Parse ( "November 1, 2011" ).ToString());
cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Date, DateTime.Now.ToString().Length, DateTime.Now.ToString());
cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Varchar2, 3, hours);
cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Varchar2, 3, hours);
cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Varchar2, 3, hours);
cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Varchar2, 3, hours);
cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Varchar2, 3, hours);
cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Varchar2, 3, hours);
cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Varchar2, 3, hours);
cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Varchar2, 1, "Y");
Oracle.DataAccess.Client.OracleParameter Param12 = cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Date);
Param12.Direction = ParameterDirection.Output;
Param12.CollectionType = Oracle.DataAccess.Client.OracleCollectionType.PLSQLAssociativeArray;
Param12.Size = 20000;
Oracle.DataAccess.Client.OracleParameter Param13 = cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Varchar2);
Param13.Direction = ParameterDirection.Output;
Param13.CollectionType = Oracle.DataAccess.Client.OracleCollectionType.PLSQLAssociativeArray;
Param13.Size = 20000;
Param13.ArrayBindSize = new int[20000]; for (int i = 0; i < 20000; i++) Param13.ArrayBindSize[i] = 20;
Oracle.DataAccess.Client.OracleParameter Param14 = cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Varchar2);
Param14.Direction = ParameterDirection.Output;
Param14.CollectionType = Oracle.DataAccess.Client.OracleCollectionType.PLSQLAssociativeArray;
Param14.Size = 20000;
Param14.ArrayBindSize = new int[20000]; for (int i = 0; i < 20000; i++) Param14.ArrayBindSize[i] = 20;
Oracle.DataAccess.Client.OracleParameter Param15 = cmd.Parameters.Add("", Oracle.DataAccess.Client.OracleDbType.Double);
Param15.Direction = ParameterDirection.Output;
Param15.CollectionType = Oracle.DataAccess.Client.OracleCollectionType.PLSQLAssociativeArray;
Param15.Size = 20000;
cmd.ExecuteNonQuery();
// ORA-01001: invalid cursor
// ORA-06512: at \"CURVES.PKG_LMP_VIEWER_DATA\", line 414
// ORA-06512: at \"CURVES.PKG_LMP_VIEWER_DATA\", line 550
// ORA-06512: at line 1"

//print out the parameter's values
Console.WriteLine("parameter values after executing the PL/SQL block");
for (int i = 0; i < 3; i++)
Console.WriteLine("Param2[{0}] = {1} ", i,
(cmd.Parameters[1].Value as Array).GetValue(i));

for (int i = 0; i < 3; i++)
Console.WriteLine("Param3[{0}] = {1} ", i,
(cmd.Parameters[2].Value as Array).GetValue(i));

// Close and Dispose Oracle.DataAccess.Client.OracleConnection object
con.Close();
con.Dispose();
Console.WriteLine("Disconnected");
#endif
}
}
}

Are "out table" parameters supported in any of these technologies ? If so, any hints on making it work ?

Edited by: 901824 on Dec 12, 2011 12:50 PM