Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 18th, 2003, 05:08 AM
Authorized User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default Passing Parameters >32k to Oracle stored procedure

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


Reply With Quote
  #2 (permalink)  
Old December 22nd, 2003, 12:34 PM
Registered User
 
Join Date: Dec 2003
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #3 (permalink)  
Old December 22nd, 2003, 12:49 PM
Authorized User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks. I'll give that a try

Gary

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Odbc Parameters Stored Procedure jgrant ASP.NET 2.0 Basics 2 August 26th, 2007 10:21 PM
Passing Array to Oracle Stored Procedure kulkarnimonica Oracle ASP 1 August 6th, 2007 02:22 AM
passing parameters to stored procedure in crystal prankur ASP.NET 1.0 and 1.1 Basics 0 January 10th, 2006 03:29 AM
Passing parameters to a sql stored procedure digby_dog VB.NET 2002/2003 Basics 0 July 11th, 2005 08:35 AM
stored procedure with parameters Danmalam VB Databases Basics 0 February 27th, 2005 03:52 PM



All times are GMT -4. The time now is 04:06 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.