Importing Data from Oracle for CLOB data Type

[bhanupraveen]

Hi,

I have a scenario where in i need to connect to Oracle and get data from a table which contains CLOB datatype value. Below is my code:

Dim ConnectionString As String
Dim SQL_String As String
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
Oracle_Data_Source = "perf01"
Oracle_User = "PERF_OWNER"
Oracle_Password = "PERF_OWNER"
ConnectionString = "Provider=msdaora;Data Source=" + Oracle_Data_Source + "; User Id=" + Oracle_User + "; Password=" + Oracle_Password

con.ConnectionString = ConnectionString
con.Open con.ConnectionString

SQL_String = "select ID,dbms_lob.substr(bus_key,25,10) from MDI_WORK"

recset.Open SQL_String, con, adOpenStatic, adLockReadOnly

Do While Not recset.EOF
Debug.Print recset.Fields(0).Value
Debug.Print recset.Fields(1).Value
Loop

When i try to execute this code, getting "Application Defined or Object Defined error '1004'", Pls help me.

 

[GTO]

Cross-posted: here

 

[RoryA]

Which line causes the error?

(I suspect your problem is in the dbms_lob.substr(bus_key,25,10) part)

 

[bhanupraveen]

Till below statement it executes fine, once i execute below it gives error. might be with dbms_lob only.

Debug.Print recset.Fields(0).Value

 

[RoryA]

I suspect the value is Null then.

 

[bhanupraveen]

Ya, record count is -1 when i execute with Debug.Print recset.RecordCount, But when i execute the same query in Oracle, i get 19 records.

 

[RoryA]

No, I mean I expect the field is actually Null, not that there are no records. (whether or not recordcount is accurate depends on various factors such as cursors)

 

[bhanupraveen]

Data is there in both the columns

 

[RoryA]

Are you checking in the recordset or in the table?

 

[bhanupraveen]

In Table, I'm not getting any data in record set..might be CLOB data issue.