Using OraOLEDB Custom Properties with Microsofts OLE DB .NET Data Provider using OraOLEDB.Oracle

Connection string

This Oracle Provider for OLE DB connection string can be used for connections to Oracle.

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.