ODP.NET: Ora-06502 Executing a PL/SQL Stored Procedure With a LONG IN/OUT Parameter (Doc ID 359212.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Data Provider for .NET - Version: 9.2.0.4.0 to 10.2.0.1.0







Symptoms

Problem:
Error calling a PL/SQL procedure with one in/out parameter of type LONG



Error:
ORA-06502: PL/SQL: numeric or value error ORA-06512: at "procedure_name", line
119 ORA-06502: PL/SQL: numeric or value error: character string buffer too
small ORA-06512: at line 1

/*
create table longtab (col1 long);
create or replace procedure testlong (v1 in out longtab.col1%type)
as
begin
insert into longtab values(v1);
v1 := rpad ('z',32000,'z');
end;
/
*/

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Xml;

public class odpparam
{
public static void Main()
{
OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl");
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "testlong";
cmd.CommandType=CommandType.StoredProcedure;
cmd.Connection = con;
OracleParameter v1 = new OracleParameter("v1",OracleDbType.Long,ParameterDirection.InputOutput);
//v1.Size=32000;
v1.Value= "abcdefg";
cmd.Parameters.Add(v1);
cmd.ExecuteNonQuery();
Console.WriteLine("value is {0}",v1.Value);
v1.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();
}
)





Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms