Oracle Blob 4000byte limitation

[Guest]

I am receiving the following error when I attempt to insert into a Blob field
from a bytearray > 4000 bytes into Oracle 8i using OleDb.

"ORA-01036: illegal variable name/number "

In investigating this, it looks like ADO had a solution using the SPPrmsLOB
parameter. How do I implement this solution in ADO.NET? or is there another
workaround?

' Enabling the SPPrmsLOB property indicates to the provider
' that one or more of the bound parameters is of LOB datatype
objCmd.Properties("SPPrmsLOB") = TRUE


Following is the c# code:

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Text;
using System.IO;

namespace web.playground
{

public class DebugOracleDriver : System.Web.UI.Page
{
private void Page_Load( object sender, System.EventArgs e)
{

FileStream file = new FileStream(@"F:\xml\acb.xml",
System.IO.FileMode.Open, System.IO.FileAccess.Read);
StreamReader sr = new StreamReader(file);
string s = sr.ReadToEnd();
sr.Close();
file.Close();

bool resp = this .addInboundTransaction("GENERIC", s,
"PENDING", "CJF", "TEST", "TEST");

}

#region Add Inbound Transaction
public bool addInboundTransaction( string structureID, string
msgIn, string status, string lastUpdatedBy, string originatingID, string
hostSystem)
{
string DataSource = "Provider=OraOLEDB.Oracle;Data
Source=XXXX;User ID=XX;Password=XX";

OleDbConnection cn = new OleDbConnection(DataSource);

string sql = "INSERT INTO TABLE (TABLEROWID, STRUCTUREID,
STATUS, LASTUPDATEDBY, MSGIN, ORIGINATINGID, HOSTSYSTEM) VALUES
(IBTXN_SEQ.NEXTVAL, :1,:2,:3,:4,:5,:6)";

OleDbCommand cmd = new OleDbCommand();
cmd.CommandTimeout = 60;
cmd.CommandText = sql;
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;

OleDbParameter p1 = cmd.Parameters.Add("strucID",
OleDbType.VarChar,50);
p1.Direction = ParameterDirection.Input;
p1.Value = structureID.Trim();

OleDbParameter p2 = cmd.Parameters.Add("status",
OleDbType.VarChar,50);
p2.Direction = ParameterDirection.Input;
p2.Value = status.ToUpper().Trim();

OleDbParameter p3 = cmd.Parameters.Add("lastUpdatedBy",
OleDbType.VarChar,50);
p3.Direction = ParameterDirection.Input;
p3.Value = lastUpdatedBy.ToUpper().Trim();

byte [] ba = stringToByteArray(msgIn);
OleDbParameter p4 = cmd.Parameters.Add("blobData",
OleDbType.Binary, ba.Length);
p4.Direction = ParameterDirection.Input;
p4.Value = ba;

// the ip/dns address of the system making the request (like
http_referrer)
OleDbParameter p5 = cmd.Parameters.Add("originatingID",
OleDbType.VarChar,50);
p5.Direction = ParameterDirection.Input;
p5.Value = originatingID.ToUpper().Trim();

// the name of the host system (http, websvcs)
OleDbParameter p6 = cmd.Parameters.Add("hostSystem",
OleDbType.VarChar,50);
p6.Direction = ParameterDirection.Input;
p6.Value = hostSystem.ToUpper().Trim();

cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

return true ;
}
#endregion

#region String to Byte Array
private byte [] stringToByteArray( string input)
{
// convert string to byte array in ascii format
Encoding encoding = Encoding.ASCII;
//byte[] ba = new byte[input.Length];
byte [] ba = encoding.GetBytes(input);
return ba;
}
#endregion

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form
Designer.
//
InitializeComponent();
base .OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this .Load += new System.EventHandler( this .Page_Load);
}
#endregion
}
}

 

[Frans Bouma [C# MVP]]

I am receiving the following error when I attempt to insert into a
Blob field from a bytearray > 4000 bytes into Oracle 8i using OleDb.

"ORA-01036: illegal variable name/number "

In investigating this, it looks like ADO had a solution using the
SPPrmsLOB parameter. How do I implement this solution in ADO.NET? or
is there another workaround?

How about using the Oracle provider of .NET instead of OLEDB? Have you
tried that one?

Frans


--

 

[Frans Bouma [C# MVP]]

OleDbParameter p4 = cmd.Parameters.Add("blobData",
OleDbType.Binary, ba.Length);

On second thought... Shouldn't this be of type
OleDbType.LongVarBinary? And length 2^31 -1 ?

FB


--

 

[Guest]

Hi Frans,

I appreciate the feedback. I attempted the following and it still has same
exception:

OleDbParameter p4 = cmd.Parameters.Add("blobData", OleDbType.VarBinary,
ba.Length);

This also fails:

OleDbParameter p4 = cmd.Parameters.Add("blobData", OleDbType.LongVarBinary,
ba.Length);

When the byte array is less than 4000 bytes, it does work. The exception
being thrown is an Oracle Exception. I am beginning to think that in order
to correct this problem something from the database side, not the application
side, will have to be corrected.

Any other suggestions?

 

[Rogas69]

Hi Chris,

Well I had similar problem with Oracle one yr ago.. I resolved it using
technique shown here:
http://msdn.microsoft.com/library/d...systemdataoracleclientoraclelobclasstopic.asp

In fact, you have to create blob reference in Oracle database and return it
to your code. Then you fill the blob with your data and at last you pass
this blob as parameter to your stored procedure.

HTH

Peter