I am using ADO.NET and the OracleClient class to call Oracle 9i stored procedures. Unfortunately there seems to be a limit to the size of a parameter you can pass in. This seems to be 32K !!!

I am working with blob objects, and have used the code in the following article to pass a blob object into a stored procedure (i.e. to add an image to a table):-

http://support.microsoft.com/default...b;en-us;322796

However if the parameter you pass in is > 32k, Oracle gives the following error:-

"ORA-01460: unimplemented or unreasonable conversion
requested"

Does anyone know if there is a way round this?

Many Thanks,
Gary

Gary,

Problem appears to be with passing a BLOB with length > 32k to Stored Procedures. I had same problem and changed my SP to exclude the BLOB parameter and then called the following code in my data adapter after the update or insert SP was executed to update the BLOB column. (Note the connection and transaction are already established at this point).


    Public Sub SaveDetails(ByRef row As SomeDataset.SomeRow)

        Dim cmd As New OracleCommand
        cmd = mSqlConn.CreateCommand() ' existing conn
        cmd.Transaction = mSqlInsCmd.Transaction ' existing tran

        cmd.CommandText = "DECLARE dpBlob BLOB; BEGIN DBMS_LOB.CREATETEMPORARY(dpBlob, False, 0); :tmpBlob := dpBlob; END;"
        cmd.Parameters.Add(New OracleParameter("tmpBlob", OracleType.Blob)).Direction = ParameterDirection.Output
        cmd.ExecuteNonQuery()

        Dim tempLob As OracleLob
        tempLob = cmd.Parameters(0).Value
        tempLob.BeginBatch(OracleLobOpenMode.ReadWrite)
        tempLob.Write(row.BLOBField, 0, row.BLOBField.Length)
        tempLob.EndBatch()

        cmd.Parameters.Clear()
        cmd.CommandText = "Update SomeTable Set BLOBField = :blb WHERE ID = " + row.Id.ToString
        cmd.CommandType = CommandType.Text
        cmd.Parameters.Add(New OracleParameter("blb", OracleType.Blob)).Value = tempLob
        Try
            cmd.ExecuteNonQuery()
        Catch ex As OracleException
            Throw ex
        End Try

    End Sub

Thanks. I'll give that a try

Gary