Finding Unique Index Keys - Oracle

Will give you a list of indexes used by the table:
select * from all_indexes where table_name = 'T931_ACCOUNT_SWITCHING';

Will return fields (or keys) used by the index:
select * from all_ind_columns where table_name = 'T931_ACCOUNT_SWITCHING'
and index_name = 'T931_ACCOUNT_SWITCHINGP1'
order by 1,2,3,4,6

VBA example - Handling Import and Export Files

Because sometimes you have to use VBA. This example was for a 3rd party's interface.

Option Compare Database

Public Sub ifcHR185GL_Actuals()

DoCmd.SetWarnings False
stAurion = CheckEnviron() ' Get & set Env

locIn = "C:\Temp\"
locOut = "C:\Temp\"

stFilePathIn = locIn & "gl_actuals.txt"
stFilePathOutHdr = locOut & "JACT" & Format(Date, "YYMMDD") & "_H.txt"
stFilePathOutLn = locOut & "JACT" & Format(Date, "YYMMDD") & "_L.txt"
stFilePathOut = locOut & "JACT" & Format(Date, "YYMMDD") & ".txt"

' stFilePathIn = "U:\INTERFACE\CURRENT\IN\gl_interface_"
' stFilePathOut = "U:\INTERFACE\CURRENT\OUT\JACT" & Format(Date, "YYMMDD") & ".txt"

'Delete the existing records
DoCmd.OpenQuery "qryHR185Actuals_Aurion_Del", acNormal, acEdit
DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_Del", , acEdit

'Import the Aurion text file and create a new table
DoCmd.TransferText acImportDelim, "HR185Gl_actuals Import Specification", "tblHR185Actuals_Aurion", _
stFilePathIn, False, ""

'Update header record with system date and PPEDN date to be used for journal lines
'may be commented out when the file has a PPEND
'but it requires import spec changes and qry_App changes
DoCmd.OpenQuery "qryHR185HeaderRecord_Upd", acNormal, acEdit

'Create output table
''DoCmd.OpenQuery "qryHR185Journal_Header_Actuals_App", acNormal, acEdit
DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_App", acNormal, acEdit
DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_UpdPPEND", acNormal, acEdit

' Delete old copy of output files if found
stScratch = Dir(stFilePathOutHdr)
If stScratch <> "" Then
Kill stFilePathOutHdr
End If
stScratch = Dir(stFilePathOutLn)
If stScratch <> "" Then
Kill stFilePathOutLn
End If
stScratch = Dir(stFilePathOut)
If stScratch <> "" Then
Kill stFilePathOut
End If

'Export the Header and Line tables
DoCmd.TransferText acExportFixed, "tblHR185Journal_Header_Actuals Export Specification", _
"tblHR185Journal_Header_Actuals", stFilePathOutHdr
DoCmd.TransferText acExportFixed, "tblHR185Journal_Line_Actuals Export Specification", _
"qryHR185Journal_Line_Actuals_Export", stFilePathOutLn

' Merge Header and Line files into final export file.
Set fso = CreateObject("Scripting.FileSystemObject")
Set fStrmH = fso.OpenTextFile(stFilePathOutHdr)
Set fStrmL = fso.OpenTextFile(stFilePathOutLn)
Set fStrmM = fso.CreateTextFile(stFilePathOut, True)
Do Until fStrmH.AtEndOfStream
strLine = fStrmH.ReadLine
fStrmM.writeline strLine
Do Until fStrmL.AtEndOfStream
strLine = fStrmL.ReadLine
fStrmM.writeline strLine


' Cleanup Header and Line files.
Kill stFilePathOutHdr
Kill stFilePathOutLn
DoCmd.SetWarnings True

MsgBox "completed"

End Sub

Simple Example of using an Application Package

Because I'm always forgetting the syntax:

import APP_PACKAGE:AppClass;

Local APP_PACKAGE:AppClass &objAppClass = create APP_PACKAGE:AppClass();

&x = &objAppClass.Method();

For Loop Gotcha

From a recent email from a collegue. It seems that For loops only evaluate the conditions once, when the loop is entered:

Hi all,

I have just encountered an ‘interesting’ property of for loops.

It appears that they hard-code the number that they loop through until, and there doesn’t seem to be any way to affect this number.

I.e. If you have a rowset and are looping through it via a for loop - &I = 1 to &rowset.activerowcount, and you add a row to the rowset, the for loop will only go through the original number of rows – the added row will not be part of the &rowset.activerowcount of the loop. (If there were originally 10 rows in the rowset, and I add another 5 rows to the rowset as part of the for loop, &I will only ever get to 10 – not 15).

If you wish to loop through a rowset (or any variable number loops) you should use a while or a repeat loop, as the conditions are re-evaluated each time the loop comes to the conditional part (the top of the while statement, and the until of the repeat statement).

Hope that helps people.