Getting error on output parameter.

Question

I have done POC on Visual Studio 2017 .Net Framework 4.5.2 with DB Oracle 19c (Version 19.3) as ODP.Net we have used dll named as Oracle.DataAccess.Client.

Getting below error at C# side

ORA-06502 : PL/SQL: Numeric or Value error : Character string buffer too small.

Stored Procedure

Create or Replace Procedure POC_Test(p_message Out Varchar2)
As
Begin
    p_Message := 'XYZ'; 
End POC_Test;

//C# Code

using System;
Using Oracle.DataAcess.Client;

class DataBasePOC {
  static void Main() 
  {
    OracleConnection connection = new OracleConnection("DataSource=POC_Database;User Id=User1;Password=PQR123");
    OracleCommand cmd=new OracleCommand(); 
    connection.Open();
    cmd.CommandText="POC_Test";
    cmd.CommandType=CommandType.StoredProcedure;
    cmd.Parameters.Add("p_message", OracleDbType.Varchar2, "", ParameterDirection.Output);  
    OracleDataAdaptor da= new OracleDataAdaptor();
    da.SelectCommand=cmd;  
    DataTable dt= new DataTable();
    da.Fill(dt);
    connection.Close();  
  }
}

Jignesh Kumar · Answer

Hi Please try below one which will help you --Stored Procedure Create or Replace Procedure POC_Test(p_message Out Varchar2(2000)) As Begin p_Message := 'XYZ'; End POC_Test; //C# Code using System; Using Oracle.DataAcess.Client; class DataBasePOC { static void Main() { OracleConnection connection = new OracleConnection(DataSource=POC_Database;User Id=User1;Password=PQR123); OracleCommand cmd=new OracleCommand(); connection.Open(); cmd.CommandText=POC_Test; cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.Add(p_message OracleDbType.Varchar2 2000 ParameterDirection.Output); OracleDataAdaptor da= new OracleDataAdaptor(); da.SelectCommand=cmd; DataTable dt= new DataTable(); da.Fill(dt); connection.Close(); } }

Sachin Singh · Answer

This a common error when we forget to specify the size while using varchar2 to solve this just specify the size class DataBasePOC { static void Main() { OracleConnection connection = new OracleConnection( DataSource=POC_Database;User Id=User1;Password=PQR123 ); OracleCommand cmd= new OracleCommand(); connection.Open(); cmd.CommandText= POC_Test ; cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.Add( p_message  OracleDbType.Varchar2 32767 ParameterDirection.Output); // notice here OracleDataAdaptor da= new OracleDataAdaptor(); da.SelectCommand=cmd; DataTable dt= new DataTable(); da.Fill(dt); connection.Close(); } } now run it will work.