469,266 Members | 1,679 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

BLOB with oracle stored proc

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
Aug 23 '06 #1
0 3074

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Mohammad | last post: by
11 posts views Thread by Chris Fink | last post: by
12 posts views Thread by Newbie | last post: by
9 posts views Thread by matt | last post: by
2 posts views Thread by =?Utf-8?B?Vmlua2k=?= | last post: by
1 post views Thread by =?Utf-8?B?Vmlua2k=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.