I am new to using BLOB with oracle stored proc. I dont knw how to set the
size of the buffer which seems to overflow no matter what i used to pass it.
I am getting an error
System.Data.OracleClient.OracleException: ORA-06502: PL/SQL: numeric or
value error: character string buffer too small
ORA-06512: at "APPS.XXVAA_E_RECRUITMENT_HA_IF", line 13
ORA-06512: at line 1
The code i am executing is
oConn.Open();
OracleTransaction tx = oConn.BeginTransaction();
byte[] tempbuff =ConvertStringToByteArray(newHireXML);
//Creating a BLOB
OracleCommand cmdLob = oConn.CreateCommand();
cmdLob.Transaction = tx;
cmdLob.CommandText = "declare xx blob; begin dbms_lob.createtemporary(
xx, false, 0); :tempblob := xx; end;";
cmdLob.Parameters.Add(new
OracleParameter("tempblob",OracleType.Blob)).Direc tion =
ParameterDirection.Output;
cmdLob.ExecuteNonQuery();
OracleLob LobApplicantXML = (OracleLob)cmdLob.Parameters[0].Value;
LobApplicantXML.BeginBatch(OracleLobOpenMode.ReadW rite);
LobApplicantXML.Write(tempbuff,0,tempbuff.Length);
LobApplicantXML.EndBatch();
cmdLob.Parameters.Clear();
cmdLob.CommandText = "XXVAA_E_RECRUITMENT_HA_IF.main";
cmdLob.CommandType = CommandType.StoredProcedure;
//set store procedure parameters
cmdLob.Parameters.Add(new OracleParameter("p_applicant_xml",
OracleType.Blob)).Value = LobApplicantXML;
// cmdLob.Parameters.Add(new OracleParameter("p_applicant_xml",
OracleType.Blob)).Value = LobApplicantXML;
// cmdLob.Parameters.Add("p_applicant_xml", OracleType.Blob).Direction =
ParameterDirection.Input ;
cmdLob.Parameters.Add("p_success", OracleType.VarChar,10).Direction =
ParameterDirection.Output ;
cmdLob.Parameters.Add("p_error_text", OracleType.VarChar,2000).Direction
= ParameterDirection.Output;
cmdLob.ExecuteNonQuery();
tx.Commit();
***********
public static byte[] ConvertStringToByteArray(string stringToConvert)
{
return (new UnicodeEncoding()).GetBytes(stringToConvert);
}
Coudl anyone help me overcome this buffer error.
Any help would be appreciated
Thanks