Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Unhappy Unanswered: ADO.NET and BLOB parameters

    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 !!!

    Besides simply defining a prameter as type OracleType.Blob I have also tried using 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?

    Help me pleeeeaaaaassseeee!!!!

  2. #2
    Join Date
    Oct 2003
    Posts
    12
    Problem appears to be with passing a BLOB with length > 32k to Stored Procedures. I 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •