Re: reading longvarchar(484), data always empty in Excel Options

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 26 Oct 2007 08:53:50 -0700
Message-ID: <1193414027.966376@bubbleator.drizzle.com>

jodleren wrote:
> On Oct 25, 8:43 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:

>> On Oct 25, 1:27 am, jodleren <sonn..._at_hot.ee> wrote:
>>> On Oct 24, 7:41 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>>>> On Oct 23, 11:39 pm, Sonnich Jensen <sonnich.jen..._at_jotautomation.com>
>>>> wrote:
>>>>> I have a problem with reading data from Oracle in Excel. When reading
>>>>> a
>>>>> longvarchar, I get nothing - it does not read any data out.
>>>>> The code (VBA in Excel) below is in use, and when reading a normal
>>>>> varchar2 field it
>>>>> works. The current is a longvarchar(484), and it is always empty,
>>>>> causing EOF to be true - always - trying to to read data before reads
>>>>> nothing...
>>>>> The SQL works well in PHP, DB Explorer and such.... but M$ does not
>>>>> like it,
>>>>> Help, please...
>>>>>       Set oWS = CreateWorkspace("server", "me", "qwerty", dbUseODBC)
>>>>>       oWS.DefaultCursorDriver = dbUseODBCCursor
>>>>>       oWS.LoginTimeout = 280
>>>>>       Set Connection = oWS.OpenConnection("server", dbDriverNoPrompt,
>>>>> True, _
>>>>>         "ODBC;DSN=datastuff;UID=me;PWD=qwerty")
>>>>>       Connection.QueryTimeout = 480
>>>>>      sSQL = "select something from whatever"
>>>>>     Set oRec = oConn.OpenRecordset(sSQL, dbOpenDynaset)
>>>>>     If oRec.EOF Then   '<- always false, even that SQL returns data
>>>>>       sResult = ""
>>>> What, exactly, is a 'longvarchar(484)'?  That isn't a valid datatype
>>>> in Oracle.
>>> Well, that is what DB explorer shows me... anyway, some kind of long
>>> varchar, which shows as a "MEMO". I dont have that well access to the
>>> DB to tell you much more.
>>> When reading those long strings I get those problems.
>> And LONG columns don't play well with some interfaces.  Which version
>> of Excel are you using?  I can easily retrieve LONG data through an
>> ODBC connection (using Micro$ofts own driver for Oracle).  It may be
>> VBA causing your 'problems' with LONG columns, but I'm not that
>> familiar with VBA to make such an 'educated guess'.

>
> Excel 2003.
> As of VBA, I wrote to the m$.excel newsgroup too, but got absolutely
> no response.
>
> I used the oracle driver, changed that to M$, and the result is the
> same.
>
> In general I have not got much response on this, seems like noone
> knows.
>
> WBR
> Sonnich

We might know, and probably do know, if you were able to adequately describe what is going on.

As has been pointed out to you there is no data type with the name you mention and you seem to be unable, or unwilling, to do a describe on the table and supply DDL so you leave us just guessing.

If you work in an Oracle shop why not just ask a DBA for help?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Oct 26 2007 - 10:53:50 CDT