Oracle <CLOB> question

[jason]

I need to retrieve a table from Oracle using VBA (i can do this
easily), but in this situation one of the entries in the table is a
<CLOB>.

i tried:
DBMS_LOB.substr(My_COL,2000,1)

and this seemed to help (or at least got me a step closer i thought)
to my desired solution
but still I cannot read the data into excel (i want to display it as a
string or character in a cell).

error received in VBA:
Run-time error '1004':
Application-defined or object-defined error

any help would be great.

 

[Tim Williams]

Would help if you showed more code - including the connection string you're
using.
Some drivers are better than others with LOB columns.

Tim

 

[jason]

Would help if you showed more code - including the connection string you're
using.
Some drivers are better than others with LOB columns.

Tim

Set objSession = CreateObject
("OracleInProcServer.XOraSession")


Sql = "SELECT * FROM B"



Set OraDynaSet = objdatabase.DBCreateDynaSet(Sql, 0)

where B contains a clob column.

 

[Tim Williams]

I've never used the Oracle data access objects - ADO has always been easier
(no extra installs for users)
Have you tried incorporating the DBMS_LOB.substr(My_COL,2000,1) into your
SQL ?

Eg:
Sql = "SELECT DBMS_LOB.substr(B.My_COL,2000,1) FROM B"

Can you run that in SQLPlus or some other query tool ?

Equivalent ADO code would be (untested):

Dim oConn as new adodb.connection , sConnString as string
Dim oRS as new adodb.recordset

sConnString = "Provider=OraOLEDB.Oracle;User Id =" & sUser & _
";Password=" & sPassword & _
";Data Source=" & sInstance

oConn.Open sConnString
oRS.Open "SELECT DBMS_LOB.substr(B.My_COL,2000,1) FROM B", _
oConn, adOpenStatic, adLockReadOnly

debug.print left(oRS("My_COL").value,100)



Tim

Would help if you showed more code - including the connection string
you're
using.
Some drivers are better than others with LOB columns.

Tim

Set objSession = CreateObject
("OracleInProcServer.XOraSession")


Sql = "SELECT * FROM B"



Set OraDynaSet = objdatabase.DBCreateDynaSet(Sql, 0)

where B contains a clob column.