Excel VBA - Oracle SQL Datatype not supported Error

[ron011994]

Hi All,

I have been trying to download SQL Scripts through excel. It was successful at first but when I tried to run the below script it prompts a Datatype not supported error.
I am using an ADO connection for this.

Below is the code to call SQL script.


Dim sql As String
sql = ThisWorkbook.Sheets("Scripts").Cells(2, 7)
Call GetDataFromADO(sql, ThisWorkbook.Sheets("temp").Range("p2"), Sheets("temp"))
Sheets("temp").Select

and here is the SQL script.

[TABLE="width: 247"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Select PERSONPERMID,COMPANYNAME,COMPANYTITLE[/TD]
[/TR]
[TR]
[TD]from PEOPLEAUTHORITY.PERSONINFOFORCONCORDANCE[/TD]
[/TR]
[TR]
[TD]Where PERSONPERMID in (00000000)[/TD]
[/TR]
</tbody>[/TABLE]


Kindly note that when the above script is run via Oracle developer it does works.


Thanks in advance.

Ron

 

[V_Malkoti]

What happens when you use single quotes around the value?

Where PERSONPERMID in ('00000000')

 

[ron011994]

What happens when you use single quotes around the value?

Where PERSONPERMID in ('00000000')

it will still not run. I think the script is not the problem.

 

[V_Malkoti]

Questions:
1. Is that error a VBA error or Oracle error - Can you post the exact and complete error message?
2. On which line of code do you get this error?
3. Also what is the datatype of all four columns used in the query?
4. You said it was successful at first. Did you mean this same VBA code ran once without errors and now it doesn't anymore? Did you change anything between that?

 

[ron011994]

Answers :

1.Error is Runtime Error '-2147467259(80004005)' Data Type is not supported
2. I get this upon the execution of the code please see below execution code for SQL command

Set ObjMyCmd.ActiveConnection = ObjMyConn
ObjMyCmd.CommandText = query
ObjMyCmd.CommandType = adCmdText
ObjMyCmd.Execute

3. PERSON.PERMID (NUMBER(19,0) and for Company Name, Title (CLOB)

4. Yes this same vba code ran for other scripts. But not for the above SQL Script.

 

[V_Malkoti]

I think its CLOB field. See if this MS article helps:

https://support.microsoft.com/en-us/kb/185958

 

[ron011994]

I still cant get the instruction from MS can you help me pinpoint what necessary changes should be made if I paste the code I used for ado connection?

 

[V_Malkoti]

Ok. Let me see if I can write something off my oracle database.

By the way, what happens if you modify your sql as following:

Select PERSONPERMID, to_char(COMPANYNAME) company_name, to_char(COMPANYTITLE) company_title
from PEOPLEAUTHORITY.PERSONINFOFORCONCORDANCE
Where PERSONPERMID in (00000000)

 

[V_Malkoti]

TO_CHAR() seems to work for me. I queried a table with a CLOB field and got data without any error.

Sub DBConn_test()
    Dim objConn, objRS
    Dim strConn As String, strUser As String, strPass As String, strTNS As String, strSQL As String
    
    On Error GoTo ErrorHandlerCode
    
    Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objRS.cursortype = 3
    
    strUser = "[COLOR=#0000ff]userid[/COLOR]"
    strPass = "[COLOR=#0000ff]password[/COLOR]"
    strTNS = "[COLOR=#0000ff]tnsid[/COLOR]"
    strConn = "Provider=MSDAORA.1;Password=" & strPass & ";User ID=" & strUser & _
                            ";Data Source=" & strTNS & ";Persist Security Info=True"
    
    objConn.Open strConn
    If objConn.State <> 1 Then Exit Sub
    
    strSQL = "select to_char(DEFAULT_CLOB) dataclob, length(to_char(DEFAULT_CLOB)) lenclob, WORKFLOW_ID " & _
                "from WORKFLOW_PARAMETER " & _
                "where WORKFLOW_ID = 21"
                
    Set objRS = objConn.Execute(strSQL)
    
    If objRS.EOF = True Or objRS.BOF = True Then
        Exit Sub
    Else
        objRS.MoveFirst
    End If


    ThisWorkbook.Sheets("DBExport").Cells.Clear
    ThisWorkbook.Sheets("DBExport").Range("A2").CopyFromRecordset objRS


    objRS.Close
    objConn.Close
    Exit Sub
    
ErrorHandlerCode:
    Debug.Print Err.Number & " - " & Err.Description
End Sub

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions179.htm

 

[ron011994]

TO_CHAR() seems to work for me. I queried a table with a CLOB field and got data without any error.

Sub DBConn_test()
    Dim objConn, objRS
    Dim strConn As String, strUser As String, strPass As String, strTNS As String, strSQL As String
    
    On Error GoTo ErrorHandlerCode
    
    Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objRS.cursortype = 3
    
    strUser = "[COLOR=#0000ff]userid[/COLOR]"
    strPass = "[COLOR=#0000ff]password[/COLOR]"
    strTNS = "[COLOR=#0000ff]tnsid[/COLOR]"
    strConn = "Provider=MSDAORA.1;Password=" & strPass & ";User ID=" & strUser & _
                            ";Data Source=" & strTNS & ";Persist Security Info=True"
    
    objConn.Open strConn
    If objConn.State <> 1 Then Exit Sub
    
    strSQL = "select to_char(DEFAULT_CLOB) dataclob, length(to_char(DEFAULT_CLOB)) lenclob, WORKFLOW_ID " & _
                "from WORKFLOW_PARAMETER " & _
                "where WORKFLOW_ID = 21"
                
    Set objRS = objConn.Execute(strSQL)
    
    If objRS.EOF = True Or objRS.BOF = True Then
        Exit Sub
    Else
        objRS.MoveFirst
    End If


    ThisWorkbook.Sheets("DBExport").Cells.Clear
    ThisWorkbook.Sheets("DBExport").Range("A2").CopyFromRecordset objRS


    objRS.Close
    objConn.Close
    Exit Sub
    
ErrorHandlerCode:
    Debug.Print Err.Number & " - " & Err.Description
End Sub

TO_CHAR (character)

Hi Please see below codes we used for Oracle Connection.

Public CN As ADODB.Connection

Function GetDataFromADO(query As String, destinyRange As Range, myWorksheet As Worksheet)
Dim ObjMyConn As ADODB.Connection
Dim ObjMyCmd As ADODB.Command
Dim ObjMyRecordset As ADODB.Recordset
Dim i As Integer


'Declare variables
Set ObjMyConn = New ADODB.Connection
Set ObjMyCmd = New ADODB.Command
Set ObjMyRecordset = New ADODB.Recordset

'Open Connection
'
' ObjMyConn.ConnectionString = "Provider=msdaora;" & _
' "Data Source=10.122.255.111/OASTBY;" & _
' "User Id=;" &
' "Password=1"
'ObjMyConn.Open
ObjMyConn.ConnectionString = "Provider=MSDAORA;" & _
"Data Source=;" & _
"User Id=;" & _
"Password=

ObjMyConn.Open

'Set and Excecute SQL Command
Set ObjMyCmd.ActiveConnection = ObjMyConn
ObjMyCmd.CommandText = query
ObjMyCmd.CommandType = adCmdText
ObjMyCmd.Execute

'Open Recordset
Set ObjMyRecordset.ActiveConnection = ObjMyConn
ObjMyRecordset.Open ObjMyCmd
'Copy Data to Excel

destinyRange.ClearContents
destinyRange.CopyFromRecordset ObjMyRecordset
Dim t
For i = 0 To ObjMyRecordset.Fields.Count - 1
myWorksheet.Cells(1, i + 1).Value = ObjMyRecordset.Fields(i).Name
Next

'Close Connection
ObjMyConn.Close
End Function




this macro is being called by the below code for it to run.

Sub Button1_Click()
Dim sql As String
sql = ThisWorkbook.Sheets("Scripts").Cells(2, 8)
Call GetDataFromADO(sql, ThisWorkbook.Sheets("temp").Range("p2"), Sheets("temp"))
Sheets("temp").Select
MsgBox "Done extracting from Oracle"
End Sub


thanks,