CODEWITHSUNDEEP
.netoracle-databaseplsqlblobodp.net
Travis Heseman
Travis Heseman

Reputation: 11449

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

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.

Upvotes: 0

Views: 1998

Answers (2)

Scott Welker
Scott Welker

Reputation: 21

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.

Upvotes: 2

O. Jones
O. Jones

Reputation: 108641

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.

Upvotes: 0

Related Questions