Reputation: 1011

ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception from C# code

I am trying to execute some oracle pl/sql procedure with in and out parameters from # code on asp.net. I want to retrive the value from out parameter. but when I execute i am getting a oracle exception like "ORA-06502: PL/SQL: numeric or value error: character string buffer too small".What can I do now? Please help me.

code:

using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Activity.Account
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            SuccessLabel.Visible = false;
            FailureLabel.Visible = false;
        }

        protected void Create_user(object sender, EventArgs e)
        {
            var id="";
            string oradb = "Data Source=OracleServerHost;User ID=scott;password=tiger";
            using (OracleConnection conn = new OracleConnection(oradb))
            {
                try
                {
                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "create_users_372640";
                    cmd.CommandType = CommandType.StoredProcedure;
                    OracleParameter p1 = new OracleParameter("u_user_id", UserIDTextBox.Text.TrimEnd());
                    OracleParameter p2 = new OracleParameter("u_First", FirstNameTextBox.Text.TrimEnd());
                    OracleParameter p3 = new OracleParameter("u_Last", LastNameTextBox.Text.TrimEnd());
                    OracleParameter p4 = new OracleParameter("u_Email", EmailIDTextBox.Text.TrimEnd());
                    OracleParameter p5 = new OracleParameter("u_password", PasswordTextBox.Text.TrimEnd());
                    cmd.Parameters.Add(p1);
                    cmd.Parameters.Add(p2);
                    cmd.Parameters.Add(p3);
                    cmd.Parameters.Add(p4);
                    cmd.Parameters.Add(p5);
                    OracleCommand cmd_chk = new OracleCommand();
                    cmd_chk.Connection = conn;
                    cmd_chk.CommandText = "check_user_372640";
                    cmd_chk.CommandType = CommandType.StoredProcedure;
                    OracleParameter p6 = new OracleParameter("user_id", UserIDTextBox.Text.TrimEnd());
                    cmd_chk.Parameters.Add(p6);
                    cmd_chk.Parameters.Add("id", OracleDbType.Varchar2).Direction = ParameterDirection.Output;
                    conn.Open();
                    cmd_chk.ExecuteNonQuery();
                    id=(string)cmd_chk.Parameters["id"].Value;
                    //OracleDataReader rd = cmd_chk.ExecuteReader();
                    if (id != null && id != "")
                    {
                        //rd.Read();
                        SuccessLabel.Visible = false;
                        FailureLabel.Visible = true;
                    }
                    else
                    {
                        cmd.ExecuteNonQuery();
                        SuccessLabel.Visible = true;
                        FailureLabel.Visible = false;    
                    }
                }catch(Exception){
                    Console.WriteLine("SQL Exception Occured");
                }
            }

        }
    }
}

My oracle procedure is:

create or replace procedure check_user_372640(
user_id in varchar2,
id out varchar2
)
as
u_id varchar2(20);
begin
select user_id into u_id from ACTIVITY_USERS_372640 where user_id=user_id;
id:=u_id;
end;

Upvotes: 17

Answers (8)

Mohammad Naushad M

Reputation: 56

the error is because of size of output parameter is small ,you can solve this issue by specifying the size for output parameter

 command.Parameters.Add("P_MESSAGE", OracleDbType.Varchar2, ParameterDirection.Output).Size = 255;

Upvotes: 0

Conrado Silva

Reputation: 1

For me, this happened because the name of the procedure was bigger than expected for C#. I don't know exactly why, but I've reduced the size o the procedure name and it worked.

Upvotes: 0

Dinith Minura

Reputation: 1496

Faced the same issue when declaring out put value as Varchar2. Adding a Size property to Parameter solved the issue.

command.CommandType = CommandType.StoredProcedure;
command.CommandText = "function_name";    
command.Parameters.Add(new OracleParameter
                        {
                            ParameterName = "result",
                            Size = 1,
                            Direction = ParameterDirection.ReturnValue,
                            OracleDbType = OracleDbType.Varchar2
                        });

Upvotes: 5

sergiu

Reputation: 1509

Another strage thing we RAN into related to this is with Oracle functions, for the special ParameterDirection.ReturnValue (*** all the rest of the ParameterDirection will work)

if you decalre it like bellow, directly in the constructor it DOSEN'T work:

cmd.Parameters.Add(new OracleParameter("myretval", OracleDbType.Long, 10, ParameterDirection.ReturnValue));

Result in error like:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-01403: no data found
ORA-06512: at line 1

if you declare it like this it works:

OracleParameter retval = (new OracleParameter("myretval", OracleDbType.Long, 10);
            retval.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(retval);

Upvotes: 2

Sandeep Reddy K.

Reputation: 1011

It is working for me now. Mistake is I have declared a parameter "Id" as varchar2. but I didn't give any size to that. Now I have declared max size to the parameter and its working fine.

 cmd_chk.Parameters.Add("id", OracleDbType.Varchar2,32767).Direction = ParameterDirection.Output;

Upvotes: 31

Damith

Reputation: 63065

you need to increase the size of u_id

u_id varchar2(4000);

Upvotes: 0

Nagaraj S

Reputation: 13474

Can you try with anchored datatype like %type.
syntax :- tablename.colname%type.

Upvotes: 0

Vignesh Kumar A

Reputation: 28403

The problem originates from using Char which is a fixed length string. Not sure where, but somewhere in your code you try to put a Char or varchar2 string of length N into a char of lengh M where M > N.

Upvotes: 0

ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception from C# code

Answers (8)

Related Questions