Error while executing SQL query in VBA

Question

I am getting error while executing below query in VB (I am using macro to run this query). But in ORACLE, the same query runs file and gives me the expected result. Please note that, "XML_FIELD" is a CLOB Which has XML Data in it. I am trying to extract data from "XML_FIELD" using VB Macro.

Query in SQL: executes fine :
---> select xmltype(XML_FIELD).extract('//XML_TAGNAME/text()').getStringVal() as NEW_COL from TAB_1 where TEST_ID = 123

Error in VB: SQL Execution Error ora-00936 missing expression

Query in VBA:
Sub Extract_CLOB()
Dim SQL As String
Dim OraDynaSet As Object
'Comment: I am using two single quotes (' ' ) to escape the other single quote
SQL = "select xmltype(XML_FIELD).extract(''//XML_TAGNAME/text()'').getStringVal() as NEW_COL from TAB_1 where TEST_ID = 123"
Set OraDynaSet = objDataBase.DBCreateDynaset(SQL, 0&)
End Sub

Go into debug mode, print out the value of SQL and post it.

Nick.Mc
– Nick.Mc

2012-11-30 06:05:23 +00:00
Commented Nov 30, 2012 at 6:05 — Nick.Mc
– Nick.Mc, Commented Nov 30, 2012 at 6:05

user1429899 · Accepted Answer · 2012-11-30 06:13:05Z

0

I tried this code and it worked well. I added one clob field to emp table.

Public objSession As Object
Public objDataBase As Object

Sub ConnectToOracle()
    'Create a reference to the OO4O dll
    Set objSession = CreateObject("OracleInProcServer.XOraSession")
    'Create a reference to my database
    Set objDataBase = objSession.OpenDatabase("mydb", "scott/tiger", 0&)
    Dim strSQL As String
Dim strTxt As String
Dim OraDynaSet As Object

strSQL = "select xmltype(MyXMLField).extract('//ROWSET//ROW//JOB/text()').getStringVal() as NEW_COL from emp where empno = 2"

'Retrieve the results from Oracle
Set OraDynaSet = objDataBase.DBCreateDynaset(strSQL, 0&)

strTxt = CStr(OraDynaSet.Fields(0).Value)

MsgBox strTxt
End Sub

and my xml is:

<?xml version="1.0"?>
      <ROWSET>
         <ROW num="1">
            <EMPNO>7</EMPNO>
            <ENAME>S</ENAME>
            <JOB>CLERK</JOB>
            <MGR>7</MGR>
            <HIREDATE>12/17/2008 0:0:0</HIREDATE>
           <SAL>999</SAL>
           <DEPTNO>20</DEPTNO>
        </ROW>
     </ROWSET>

edited Nov 30, 2012 at 6:13

answered Nov 30, 2012 at 5:05

user1429899

2885 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

tester Over a year ago

I have used two single quotes to escape the other. extract(''//XML_TAGNAME/text()'') - here I have used two single quotes (' ')

user1429899 Over a year ago

How are you connecting to Oracle? Are you using Oracle Objects for OLE or through ODBC? Have you tried to run other SQL statements?

tester Over a year ago

Below is the code to connect to DB. I get Connection Successful message. Other queries works fine. Private Sub StartConnection() 'Create a Session Object Set objSession = CreateObject("OracleInProcServer.XOraSession") dbName = .Cells(4, 3).Value dbUserName = .Cells(5, 3).Value dbPwd = .Cells(6, 3).Value Set objDataBase = objSession.OpenDatabase(dbName, dbUserName & "/" & dbPwd, 0&) MsgBox "Connection Successful" 'Set objDataBase = objSession.OpenDatabase("QTCSTG", "APPSRO/read0nly", 0&) Call Extract_CLOB End Sub

tester Over a year ago

Sorry for not formatting the above comment. Tried giving 2 spaces to insert line break, and tried with , still all the lines are displaying inline.

user1429899 Over a year ago

Please, post here your SQL variable's value using debug mode as suggested by ElectricLlama.

|

Collectives™ on Stack Overflow

Error while executing SQL query in VBA

Error in VB: SQL Execution Error ora-00936 missing expression

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Error in VB: SQL Execution Error ora-00936 missing expression

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related