VBA: Oracle SQL Insert Query Not Inserting Data

Question

Apologies if this is too much info, but I wanted to be thorough when posting.

I've been struggling with an issue getting my temporary table to populate with data from an Insert Query.

This data is being pulled from Oracle SQL tables
This query creates the temp table, inserts the data, and queries the temp table without issue in the Oracle SQL Developer software
My code will successfully create the temp table (I've verified its existence in the Oracle software)
I am able to use Select Queries with my setup (So I do not believe the connection is the issue)
Running my code doesn't produce any errors (At least nothing that stops the execution)

What I did notice is that if I use "Record Set" in the execution of my insert query, it does show that there are the expected amount of affected rows, but when I Select All (From the Oracle Software) from the temp table it shows nothing populated.

I've been looking all over but I haven't quite figured out what I am missing. From what I was reading it could be that I don't Set NOCOUNT ON (Or Set FEEDBACK OFF), but I can't find how to actually do that within my VBA code. I originally tried inserting the data into an existing table, so I attempted to create a temporary table to move the data into.

Option Explicit

Sub oracleQuery()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ActiveWorkbook.Worksheets("Query Output")
Set ws2 = ActiveWorkbook.Worksheets("Main Page")

Clears query output worksheet

ws1.Cells.Clear

Assigns variables

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.RecordSet
Dim ConnectionString As String
Dim StrQuery As String
Dim StrQuery2 As String
Dim StrQuery3 As String
Dim StrQuery4 As String

This query creates a blank Global Temp table (With column headers)

StrQuery = "XXXXXX"

This is my insert Query

StrQuery2 = "INSERT INTO temp_1 (Field1,Field2,Field3,..etc) Select From 
      Where UNION ALL Select From Where UNION ALL..Etc

This query selects the data from the temp table, and joins in some addition fields with detail information

StrQuery3 = "Select temp_1. Etc....

This query is used to drop the temp table when I am done with it

StrQuery4 = "DROP TABLE temp_1"

This is my connection string

ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=(XXX)(CONNECT_DATA=(SERVICE_NAME=XXX)));User ID=XXX;Password=XXX"

Opens connection to the database, also sets a timeout timer

cnn.Open ConnectionString
cnn.CommandTimeout = 60

Executes Temp table query

cnn.Execute StrQuery

Executes Insert Query

cnn.BeginTrans
cnn.Execute StrQuery2
cnn.CommitTrans

Executes select query on temp table, uses a record set

rst.Open StrQuery3, cnn

Drops temp table

cnn.Execute StrQuery4

Dumps all the results from the record set into cell A2 of the first sheet in the active workbook

ws1.Range("A2").CopyFromRecordset rst

Empties connection string

ConnectionString = ""

Closes and clears recordset

rst.Close
Set rst = Nothing

End Sub

It would be great to determine what is actually causing my Insert Query to not insert data. I would greatly appreciate any insight on this issue as I am running out of ideas to check.

UPDATE I simplified my code to hone in on my problem. My issue is the INSERT INTO Query is not actually inserting any data into the table. The Select Query I am trying to Insert results in 3 columns & rows of data.

Temp table query

StrQuery = "CREATE GLOBAL TEMPORARY TABLE temp_1( GALOTIDX Number(9, 0), LOTID VARCHAR2(12 BYTE), LOTDESCR VARCHAR2(40 BYTE))"

INSERT INTO Query String

StrQuery2 = "Insert INTO temp_1 (GALOTIDX, LOTID, LOTDESCR)"
StrQuery2 = StrQuery2 + " Select LotIdx, ID1, Descr1 From DataTable1"

Query For Final Select

StrQuery3 = "Select * From temp_1"

Query for Dropping Temp Table

StrQuery4 = "DROP TABLE temp_1"

Builds connection string

ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=XXX)(Port=XXX))(CONNECT_DATA=(SERVICE_NAME=XXX)));User ID=XXX;Password=XXX"

Opens connection to the database, also sets a timeout timer

cnn.Open ConnectionString
cnn.CommandTimeout = 60

Creates Temp Table

cnn.Execute StrQuery

Executes Insert Into Query

Dim recordsAffected As Integer

cnn.BeginTrans
cnn.Execute StrQuery2, recordsAffected
cnn.CommitTrans

Selects Data from temp table and dumps into a worksheet

rst.Open StrQuery3, cnn
ws1.Range("A2").CopyFromRecordset rst

Drops Temp Table

cnn.Execute StrQuery4

Empties connection string

ConnectionString = ""
cnn.Close

I believe temp table are per-session visible only, so you cannot see the contents from a different session than the one used to create it. The issue may be that you're closing and then re-opening the connection before trying to read the data. — Tim Williams
When executing my code line by line, I checked (The Oracle Software) after the temp table query was ran and I saw it was created, and when I ran the query to drop the temp table, I could see that it was dropped. My interpretation was that if I could see the creation and drop, I should have been able to see if data was inserted. Also, the select query in my code didn't pick anything up either. Am I missing something with the connection throughout the code? — Will
TBH what you posted isn't comprehensive enough to really know what's going on - it's not clear if you carried out all of the steps in succession using a single connection, or if you didn't. It might be helpful to create a very stripped-down example which you can post in its entirety but which still shows the same issues. Set NOCOUNT ON and Set FEEDBACK OFF are more like SQLPLus commands and I've never used them outside of that tool. — Tim Williams
What would be helpful to show? More details on the Insert Query? My issue is that the Insert Query is Massive. The contents of it seem fairly simple, it is comprised of a handful of Select Queries with unions between each of them. On its own it function properly. What focal point should I use when posting more detail? — Will
I guess something simple - like a hard-coded temp table insert of a single row, which you can query right out again to show that the basic approach is sound? And show the full code for that. — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2019-03-26T16:47:17

Looks like your commit may be deleting your rows.

From: https://docs.oracle.com/javadb/10.8.3.0/ref/rrefdeclaretemptable.html

ON COMMIT Specifies the action taken on the global temporary table when a COMMIT operation is performed.

DELETE ROWS All rows of the table will be deleted if no hold-able cursor is open on the table. This is the default value for ON COMMIT. If you specify ON ROLLBACK DELETE ROWS, this will delete all the rows in the table only if the temporary table was used. ON COMMIT DELETE ROWS will delete the rows in the table even if the table was not used (if the table does not have hold-able cursors open on it).

PRESERVE ROWS The rows of the table will be preserved.

VBA: Oracle SQL Insert Query Not Inserting Data

1 Answers