OraOLEDB.OracleUsing OraOLEDB Custom Properties with Microsofts OLE DB .NET Data Provider

The SPPrmsLOB and NDatatype properties can only be set as connection string attributes when OraOLEDB is used by OLE DB .NET Data Provider.

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;
Password
=myPassword;OLEDB.NET=True;SPPrmsLOB=False;NDatatype=False;
SPPrmsLOB
=False;

Using ADO, these properties would have been set as a property on the command. This is not possible if using the Microsofts OLE DB .NET Data Provider. So the properties are specified in the connection string instead.

PLSQLRSet: If the stored procedure, provided by the consumer, returns a rowset, PLSQLRSet must be set to TRUE (enabled).

NDatatype: This property allows the consumers to specify whether any of the parameters bound to the command are of Oracle's N datatypes (NCHAR, NVARCHAR or NCLOB). This information is required by OraOLEDB to detect and bind the parameters appropriately. This property should not be set for commands executing SELECT statements. However, this property must be set for all other SQLs such as INSERT, UPDATE, and DELETE.

SPPrmsLOB: This property allows the consumer to specify whether one or more of the parameters bound to the stored procedures are of Oracle's LOB datatype (CLOB, BLOB, or NCLOB). OraOLEDB requires this property to be set to TRUE, in order to fetch the parameter list of the stored procedure prior to execution. The use of this property limits the processing overhead to stored procedures having one or more LOB datatype parameters.

Connecting

This connection string (Using OraOLEDB Custom Properties with Microsofts OLE DB .NET Data Provider using OraOLEDB.Oracle) can be used for connections to Oracle.

Articlesread all »

Not the right connection string for you?

Start over in the connection string reference index.

You can also get help in the Q&A forums where you can ask your own question.

The kb articles contains how-tos and instructions and you can search for anything if you're not sure were to look.