如何通过SQL查询处理Excel中的Clob数据类型 [英] How to Handle Clob Data Type in Excel from SQL Query

问题描述

由于字段中可能存在4000个以上的字符，因此我需要从Oracle视图中检索数据，该视图的列定义为clob数据类型.

I need to retrieve data from an Oracle view that has a column defined as a clob Data Type due to the possibility of there being more than 4000 characters in the field.

我在Excel VBA中有一个子例程，该子例程执行查询并将结果放入单元格区域.

I have a subroutine in Excel VBA that executes a query and puts the results into a cell range.

我遇到错误

不支持数据类型"

"Data Type Not Supported"

at

strSQL = "Select clob_field from Table1"

甚至可以在Excel中查询Clob数据类型的列吗?

Is it even possible to query for a Column of Clob Data Type in Excel?

我找不到任何表明我要去哪里的信息.

I cant find any information which will indicate where I am going wrong.

Sub Get_Data4()
Dim cn As Object
Dim rs As Object
Dim strCon As String
Dim strSQL As String
Dim strUsername As String
Dim strPassword As String
Dim strInput As String

'strUsername = InputBox("Input User Name")

'strPassword = InputBox("Input Password")

strCon = "Provider=MSDAORA;Data Source=xxx;User ID=xxx;Password=xxx;"

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon

strSQL = "Select clob_field from Table1"

Set rs = CreateObject("ADODB.RECORDSET")
rs.ActiveConnection = cn
rs.Open strSQL
Sheet1.Range("A4").CopyFromRecordset rs
rs.Close
cn.Close

Call Get_Data
End Sub

推荐答案

我也遇到过同样的问题.尝试打开记录集时出现未指定错误".我发现使用Oracle Home驱动程序可以解决此问题.

I have also faced the same issue. I was getting "Unspecified error" while trying to open the recordset. I found that using Oracle Home driver resolves this issue.

请按照以下步骤在您的计算机中配置Oracle Home Driver-

Please follow the below steps to configure Oracle Home Driver in your machine -

1. 安装Oracle Instant Client 12C
2. 找到此路径-C:\Oracle\product\12.1.0\client\Network\Admin\Sample
3. 同时复制"sqlnet.ora"和"tnsnames.ora"文件
4. 粘贴到Admin文件夹中(如上面的路径中所述)
5. 编辑"tnsnames.ora"文件

6. 在末尾添加以下代码

1. Install Oracle Instant Client 12C
2. Find this path - C:\Oracle\product\12.1.0\client\Network\Admin\Sample
3. Copy both "sqlnet.ora" and "tnsnames.ora" files
4. Paste in Admin folder (as mentioned in above path)
5. Edit "tnsnames.ora" file

6. Add the below code at the end

[DS Name as you wish] =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = [your DB server name or IP])(PORT = [Port Number]))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = [Your DB Service Name]")
    )
  )

保存文件

Save the file

尝试以下连接字符串

ConnectionString="Driver={Oracle in OraClient12Home1};DBQ=Your SERVICE_NAME;User ld=User ID;pwd=Password;"

这篇关于如何通过SQL查询处理Excel中的Clob数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！