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.

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" wrote in message
...
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.

On Nov 19, 9:53*pm, "Tim Williams" wrote:
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" wrote in message

...



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.

Set objSession = CreateObject
("OracleInProcServer.XOraSession")


Sql = "SELECT * FROM B"



Set OraDynaSet = objdatabase.DBCreateDynaSet(Sql, 0)

where B contains a clob column.

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


"jason" wrote in message
...
On Nov 19, 9:53 pm, "Tim Williams" wrote:
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" wrote in message

...



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.

Set objSession = CreateObject
("OracleInProcServer.XOraSession")


Sql = "SELECT * FROM B"



Set OraDynaSet = objdatabase.DBCreateDynaSet(Sql, 0)

where B contains a clob column.