VBScript_Get xml data stored in Oracle DB(DataType - CLOB)

Question

This may seem to be a pretty basic issue but I am not able to find any solution for this. I have to admit that I have very little experience with databases. I looked at THIS question but it didn't help me much.

Issue:

Whenever I modify my query to fetch data of type CLOB from the database, I get the error "Unspecified Error". In the below code, I have written the query strQuery = "select CDATA from WR2_USR.router_xml_data where EVENT_ID= '987787454'". The column CDATA(of datatype CLOB) contains an Long XML which I need to fetch and store in a variable for further use. Is there any way to achieve that?

Code:

Option Explicit
Dim objCon, objRs, strCon, strQuery, i, strServer, strUid, strPwd
set objCon = CreateObject("adodb.connection")
set objRs = CreateObject("adodb.recordset")
strServer = ""       'Contains the correct Server information
strUid = ""          'Contains the user name
strPwd = ""          'Contains the password
strCon = "Driver={Microsoft ODBC for Oracle};SERVER="&strServer&";uid="&strUid&";pwd="&strPwd &";"
strQuery = "select CDATA from WR2_USR.router_xml_data where EVENT_ID= '987787454'"
objCon.open strCon
if objCon.state=1 then
    objRs.open strQuery, objCon          '<--- GETTING ERROR HERE
    while (not objRs.eof)
        msgbox objRs.fields.count
        for i=0 to objRs.fields.count-1 step 1
            msgbox cstr(objRs.fields.item(i).value)
        next
        objRs.movenext
    Wend
end if

set objCon = Nothing
set objRs = Nothing

Column Details:

NOTE: If I change my query to fetch some other Column's data(not of CLOB datatype), the code runs fine.

Answer 1

ODBC Driver for Oracle from Microsoft is deprecated for ages:

Oracle 7.3x is supported fully; Oracle8 has limited support. The ODBC Driver for Oracle does not support any of the new Oracle8 data types — Unicode data types, BLOBs, CLOBs, and so on — nor does it support Oracle's new Relational Object Model.

Use the ODBC driver or OLE-DB Provider from Oracle, you can download from Oracle Data Access Components (ODAC) for Windows Downloads

Then the connection string has to look similar to this:

' ODBC Driver from Oracle
strCon = "Driver={Oracle in OraClient11g_home1};DBQ=" & strServer & ";Pwd=" & strPwd & ";Uid=" & strUid     

' OLE DB Provider from Oracle
strCon = "Provider=OraOLEDB.Oracle;Data Source=" & strServer & ";Password=" & strPwd & ";User ID=" & strUid