ODP.NET Pass large byte array to PL/SQL stored procedure accepting blob

Question

I have a PL/SQL stored procedure accepting a BLOB argument (among other arguments) and executes an insert of the BLOB into a table. How can I pass a large (1MB and greater) byte array from .NET to the stored procedure.

ThinkingStiff · Accepted Answer · 2011-12-18 23:10:36Z

2

As of Oracle 11.2/ODP.Net v2.112.1.2, you can't pass an array of BLOBs. From Oracle® Data Provider for .NET Developer's Guide 11g Release 2 (11.2.0.3), PL/SQL Associative Array Binding:

...ODP.NET supports binding parameters of PL/SQL Associative Arrays which contain the following data types.
  BINARY_FLOAT
  CHAR
  DATE
  NCHAR
  NUMBER
  NVARCHAR2
  RAW
  ROWID
  UROWID
  VARCHAR2   
Using unsupported data types with associative arrays can cause an ORA-600 error.

Note too: Oracle Forums: Passing Associative Array of BLOBs Not Supported.

edited Dec 18, 2011 at 23:10

ThinkingStiff

65.4k31 gold badges148 silver badges241 bronze badges

answered Dec 18, 2011 at 22:48

Scott Welker

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

O. Jones · Accepted Answer · 2011-04-18 16:03:08Z

0

When you're setting up your SP query (getting ready to Prepare it) set the the data type of the parameter to OracleDbType.Blob.

OracleParameter p = query.CreateParameter();
p.OracleDbType = OracleDbType.Blob;
p.Direction = ParameterDirection.Input;

Then right before you run the query simply set the parameter's value to the big BLOB you're mentioning.

answered Apr 18, 2011 at 16:03

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Collectives™ on Stack Overflow

ODP.NET Pass large byte array to PL/SQL stored procedure accepting blob

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related