How to read NCLOB, CLOB data values from Oracle da

Question

I am getting the following error: Microsoft OLE DB Provider for Oracle: Data type is not supported.

Could somebody help me figure out this please...

Situation: Recently migrated database from SQL Server 2005 to Oracle 11g. One of the table has some columns of the data type ntext in SQL Server, which were converted to NCLOB during migration to Oracle. Client is Classic ASP page (VBScript) accessing the Oracle Database through OLEDB connection. When the execution reaches the query (Select query) that reads the column of type NCLOB it is throwing the Microsoft OLE DB Provider for Oracle: Data type is not supported error. When I take out that particular column then the query is running fine...

QUESTION: How to read NCLOB, CLOB data values from Classic ASP pages?

Plz let me know if you need more information.....

Thank You..

Answer 1

I know that Microsoft's ODBC Driver for Oracle didn't support any of the LOB types-- I would wager that its OLE DB Provider didn't either given the error. Can you upgrade to the Oracle OLE DB Provider?

As an aside, since you are migrating from SQL Server to Oracle, do you really need to use the NCLOB data type? Since Oracle allows the database character set to be Unicode, you normally don't need (and don't want) to use the NVARCHAR2 or NCLOB data types unless you're stuck supporting an old database that requires a non-Unicode character set. For data that is English or Western Eurpoean in nature, storing data in a CLOB has substantial benefits in terms of storage space since the CLOB would store the data in UTF-8 rather than UTF-16 in an NCLOB (assuming that you picked a Unicode character set for the database). Eliminating the NVARCHAR2 and NCLOB columns also tends to make it much easier for front-end tools to handle the data.