oracle internal_function,Data provider internal error(-3001)

Hi Vincent,

1) ODP.NET supports binding of PL/SQL associative arrays but it's single-dimensional.

2) SP execution with default param value exmple is below:

using System;

using System.Data;

using System.Text;

using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

namespace Sample

{

class Test

{

public static void WarningHandler(object src, OracleInfoMessageEventArgs args)

{

Console.WriteLine("Source object is: " + src.GetType().Name);

Console.WriteLine("InfoMessageArgs.Message is " + args.Message);

Console.WriteLine("InfoMessageArgs.Source is " + args.Source);

}

static void Main(string[] args)

{

string constr = "User Id=scott;Password=tiger;Data Source=inst1;";

OracleConnection con = new OracleConnection(constr);

con.Open();

con.InfoMessage += new OracleInfoMessageEventHandler(WarningHandler);

// Setup

Setup(con);

OracleCommand cmd = new OracleCommand("TEST.Def", con);

cmd.CommandType = CommandType.StoredProcedure;

OracleParameter oparam = cmd.Parameters.Add("empno_out", OracleDbType.Int32);

oparam.Direction = ParameterDirection.Output;

try

{

cmd.ExecuteNonQuery(); // ORA-06502: PL/SQL: numeric or value error

}

catch(Exception ex)

{

Console.WriteLine(ex);

}

int empno = (int)(oparam.Value);

Console.WriteLine("the empno for default ename of 'MARTIN' is : " + empno);

// Let's override the default value of MARTIN

OracleParameter oparam2 = cmd.Parameters.Add("ename_in", OracleDbType.Varchar2);

oparam2.Direction = ParameterDirection.Input;

oparam2.Value = "SMITH";

try

{

cmd.ExecuteNonQuery(); // ORA-06502: PL/SQL: numeric or value error

}

catch(Exception ex)

{

Console.WriteLine(ex);

}

empno = (int)(oparam.Value);

Console.WriteLine("the empno for default ename of 'SMITH' is : " + empno);

}

public static void Setup(OracleConnection con)

{

StringBuilder blr;

OracleCommand cmd = new OracleCommand("",con);

// Create Package Header

blr = new StringBuilder();

blr.Append("CREATE OR REPLACE PACKAGE TEST is ");

blr.Append("PROCEDURE Def (empno_out out NUMBER, name_in in VARCHAR2 default 'MARTIN'); ");

blr.Append("end TEST;");

cmd.CommandText = blr.ToString();

try

{

cmd.ExecuteNonQuery();

}

catch (Exception e)

{

Console.WriteLine("Error: {0}", e.Message);

}

// Create Package Body

blr = new StringBuilder();

blr.Append("create or replace package body TEST is ");

blr.Append("PROCEDURE Def (empno_out out NUMBER, name_in in VARCHAR2 default 'MARTIN') is ");

blr.Append("BEGIN ");

blr.Append("select empno into empno_out from emp where ename = name_in; ");

blr.Append("End Def; ");

blr.Append("end TEST; ");

cmd.CommandText = blr.ToString();

try

{

cmd.ExecuteNonQuery();

}

catch (Exception e)

{

Console.WriteLine("Error: {0}", e.Message);

}

}

}

}