将Oracle LONG提取到VBA中的字符串 [英] Extract Oracle LONG to string in VBA

问题描述

我很难将LONG字段从数据库中移出并移到我的Excel(VBA + ADO)应用程序中.我想知道是否有人可以提供帮助?

I'm having trouble getting a field of LONG out of the database and into my Excel (VBA + ADO) application. I wonder if anyone could help?

执行此操作完全无效(是否广播到字符串):

Doing this has no effect at all (casting to a string, or not):

myString = dataset!long_field

我尝试了GetChunk方法，该方法应该可以完成我希望做的事情:

I tried the GetChunk method, which is supposed to do what I'm hoping:

myString = CStr(dataset!long_field.GetChunk(1000))

这确实有所收获，但这不是我期望的数据(通常是一些乱码).奇怪的是，如果我在单步执行代码的同时尝试在即时"窗口中运行此代码，则会得到一些希望:

This does get something, but it's not the data I'm expecting (it's usually some garbled mess). What's weird is that, if I try running this in the Immediate window, while I'm stepping through the code, I get something hopeful:

Debug.Print CStr(dataset!long_field.GetChunk(1000))
 e l l o  W o r l d !

我不知道为什么它(在某种程度上)在即时"窗口中起作用，而在其他地方却不起作用.还要注意:

I don't know why it (sort of) works in the Immediate window and not elsewhere. Also of note:

• Oracle似乎正在使用某种2字节编码，因此存在奇怪的间隔

• Oracle seems to be using some kind of 2-byte encoding, so there's the weird spacing

缺少一开始的数据块；这应该显示为"Hello World！"

A chunk of the data from the beginning is missing; this should read "Hello World!"

我的另一项攻击计划是在数据库端编写一个将long转换为varchar2的函数.不幸的是，这也失败了，因为我需要访问的表不在我的架构之外，并且我没有适当的授权来从函数中选择数据(Oracle抱怨找不到该表).

My other plan of attack was to write a function on the database side to convert long to varchar2. Unfortunately, this also failed because the table I need to access is outside my schema and I don't have the appropriate grants to select the data from a function (Oracle complains that the table is not found).

所以我有点迷失:(有什么想法吗?

So I'm kind of lost :( Any ideas?

推荐答案

事实证明GetChunk确实有效:我进行了一些沙箱测试，尽管仍然有奇怪的编码，但它确实返回了我想要的所有数据.因此，我不知道为什么主查询中的数据变得乱码.因此我的解决方案是只使用获取长数据的函数-对其进行解码-并在需要时调用它，而不是将数据包含在主查询中.

Turns out that GetChunk does work: I did some sandbox testing and, while there's still the weird encoding, it does return all the data I want. As such, I don't know why the data is getting garbled in my main query; so my solution is to just use a function that gets the long data -- and decodes it -- and call this when needed, rather than including the data in the main query.

这篇关于将Oracle LONG提取到VBA中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！