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
Loop
Do Until fStrmL.AtEndOfStream
strLine = fStrmL.ReadLine
fStrmM.writeline strLine
Loop

fStrmH.Close
fStrmL.Close
fStrmM.Close

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

'!!! COMMENT OUT THE LINE BELOW LATER
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.