I'm having trouble using a temp table in Oracle. Here's my code:
Dim myConn As OleDbConnection
Dim strconn As String = Session.Item("optSrcDBConnect")
Dim dcSQL As OleDbCommand
Dim strSQL As String
Dim drSQL As OleDbDataReader
WriteLog("Open connection")
Try
myConn = New OleDbConnection(strconn)
myConn.Open()
Catch ex As Exception
WriteLog("Error opening connection " & vbCrLf & ex.Message)
Exit Sub
End Try
Try
strSQL = "DROP TABLE TEMPODSWIP"
dcSQL = New OleDbCommand(strSQL, myConn)
dcSQL.ExecuteNonQuery()
WriteLog(strSQL)
Catch ex As Exception
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf &
ex.ToString & " " & ex.Message)
' Exit Sub
End Try
Dim trSQL As OleDbTransaction
trSQL = myConn.BeginTransaction
Try
strSQL = "CREATE GLOBAL TEMPORARY TABLE TEMPODSWIP ON COMMIT
PRESERVE ROWS AS SELECT /*+ ORDERED */ RMAM.RMA_NBR AS RMAM_RMA_NBR,
RMAD.LN_NBR AS RMAD_LN_NBR, SORD.ORD_NBR AS SORM_ORD_NBR, SORD.LN_NBR AS
SORD_LN_NBR, RMAD.OUT_PART_NBR, SORD.PRODUCT_ID, RMAD.OUT_TRACE_ID,
RMAD.DT_RECEIVED, SORD.DT_CREATED, ZORD.ORD_NBR AS ZORD_ORD_NBR,
ZORD.SALES_SUB_ORD_NBR AS ZORD_SALES_SUB_ORD_NBR FROM RMAM, RMAD, SORD,
ZORD WHERE RMAM.RMA_NBR = RMAD.RMA_NBR AND RMAD.RMA_NBR=SORD.ORD_NBR AND
SORD.PRODUCT_ID=ZORD.PART_NBR AND RMAM.RMA_NBR=ZORD.SALES_ORD_NBR AND
SORD.REC_CD='DT' AND TRIM(SORD.PRODUCT_ID) NOT LIKE '%=_' AND TRIM
(SORD.CNCL_ITM_RSN_CD) Is Null AND RMAD.DT_RECEIVED Is Not Null AND TRIM
(SORD.INVOICE_ID) is null AND ZORD.PART_NBR Not Like '*-R *' AND
ZORD.SERV_ORDER='Y'"
dcSQL = New OleDbCommand(strSQL, myConn)
dcSQL.Transaction = trSQL
dcSQL.ExecuteNonQuery()
trSQL.Commit()
WriteLog(strSQL)
Catch ex As Exception
trSQL.Rollback()
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf &
ex.Message)
Exit Sub
End Try
Try
strSQL = "COMMIT WORK"
dcSQL = New OleDbCommand(strSQL, myConn)
dcSQL.ExecuteNonQuery()
WriteLog(strSQL)
Catch ex As Exception
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf &
ex.Message)
Exit Sub
End Try
Try
strSQL = "SELECT /*+ ORDERED */ RMAM.RMA_NBR, SORD.LN_NBR,
TRIM(RMAD.OUT_TRACE_ID), TRIM(RMAM.CUST_ID), TRIM(RMAD.OUT_PART_NBR),
SORD.PRODUCT_ID, RMAD.DT_RECEIVED, SORD.DT_CREATED, SORD.DT_LST_UPDATE,
ZORD.ORD_NBR, ZORD.SALES_SUB_ORD_NBR, OORD.ACTUAL_OPEN_DATE FROM
TEMPODSWIP, RMAM, RMAD, SORD, ZORD, OORD WHERE TEMPODSWIP.RMAM_RMA_NBR =
RMAM.RMA_NBR And TEMPODSWIP.RMAM_RMA_NBR = RMAD.RMA_NBR And
TEMPODSWIP.RMAD_LN_NBR = RMAD.LN_NBR AND TEMPODSWIP.SORM_ORD_NBR =
SORD.ORD_NBR AND TEMPODSWIP.SORD_LN_NBR = SORD.LN_NBR AND
TEMPODSWIP.ZORD_ORD_NBR = ZORD.ORD_NBR AND ZORD.ORD_NBR=OORD.ORD_NBR AND
ZORD.SUB_ORD_NBR=OORD.SUB_ORD_NBR"
Dim daOrders = New OleDbDataAdapter(strSQL, strconn)
'WriteLog(txtSpecificQuery.Text)
If myDS.Tables.Contains("SpecificQuery") Then myDS.Tables
("SpecificQuery").Dispose()
daOrders.fill(myDS, "SpecificQuery")
WriteLog(strSQL & vbCrLf & myDS.Tables("SpecificQuery")
..Rows.Count & " rows returned", EventLogEntryType.Information)
Catch ex As Exception
WriteLog("Error processing SQL" & vbCrLf & strSQL & vbCrLf &
ex.Message)
End Try
Try
strSQL = "DROP TABLE TEMPODSWIP"
dcSQL = New OleDbCommand(strSQL, myConn)
dcSQL.ExecuteNonQuery()
WriteLog(strSQL)
Catch ex As Exception
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf &
ex.Message)
End Try
Session.Item("myDS") = myDS
Radiobutton8.Checked = True
DataGrid2.CurrentPageIndex = 0
DataGrid2.DataSource = myDS.Tables("SpecificQuery")
DataBind()
The problem is that the commit doesn't appear to be working (despite trying
it two different ways!). I get a temp table, but no data. Very occasionally
I do get data, but I have to idea why!
I basically need to create a temp table to use as a "root" for a series of
related queries, all going off at different tangents in the database from
this subset of about 2,000 records out of 30,000.
Anyone know where my data is going?
--
Message posted via http://www.dotnetmonster.com
