Update Internet time in Linux

1) Check current date

# date 

2) Update HK standard time

ntpdate stdtime.gov.hk

3) Verify result

# date 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

1) Check Time Zone Status 

timedatectl status

2) Set HK Time Zone

timedatectl set-timezone Asia/Hong_Kong

Excel VBA - Read xml content

Const TopLevelFolder = "C:\Users\Administrator\Dropbox\CCBA\Data Extraction\asihkdmsq11-SSIS-20171108-masked\asihkdmsq11-SSIS-20171108-masked"

'Creating a FileSystemObject
Public FSO As New FileSystemObject

Sub MAIN()

    ' Clear Data
    Sheets(2).Select
    Cells(1, 1).CurrentRegion.Select
    Selection.Delete
    
    ' Create Header
    sHeader = "No|File Path|File Name|File Type|File Size(M)|Modification Date|xml Content"
    aHeader = Split(sHeader, "|")
    
    For c = 0 To UBound(aHeader)
        Cells(1, c + 1).Value = aHeader(c)
    Next c
    
    Cells(2, 1).Select
    
    ' Scan Folder
    ScanFolder (TopLevelFolder)
    
    ' Post-Actions
    LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    
    Cells(2, 1).Value = 1
    Cells(3, 1).Value = 2
    
    Range("A2:A3").Select
    Selection.AutoFill Destination:=Range("A2:A" & LastRow)
        
    Columns("E:E").Select
    Selection.NumberFormat = "0.0"
    
'    Columns.AutoFit
    Cells(1, 1).Select

End Sub

Sub ScanFolder(sFolder As Variant)
    'Declaring variables
    Dim objFolder As Folder
    Dim objFile As file
    Dim objSubFolder As Folder
    ' Dim strPath As String
    Dim NextRow As Long
    
    'Create the object of this folder
    Set objFolder = FSO.GetFolder(sFolder)
    
    'Find the next available row
    NextRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1

    'Loop through each file in the folder
    For Each objFile In objFolder.Files
        If InStr(objFile.Name, "dtsx") > 0 Then
            Cells(NextRow, 1).Select
            'List the name, size, and date/time of the current file
            Cells(NextRow, 2).Value = Replace(objFile.Path, objFile.Name, "")
            Cells(NextRow, 3).Value = objFile.Name
            Cells(NextRow, 4).Value = objFile.Type
            Cells(NextRow, 5).Value = objFile.Size / 1025 / 1025
            Cells(NextRow, 6).Value = objFile.DateLastModified
            
            Dim strXml As String
            strXml = FSO.OpenTextFile(objFile.Path).ReadAll
            Cells(NextRow, 7).Value = strXml
            
            'Find the next row
            NextRow = NextRow + 1
        End If
    Next objFile

    For Each objSubFolder In objFolder.SubFolders
        ScanFolder (objSubFolder.Path)
    Next objSubFolder
End Sub

Excel VBA - Scan Files in Folder

Const TopLevelFolder = "C:\Users\Administrator\Dropbox\CCBA\Data Extraction\asihkdmsq11-SSIS-20171108-masked\asihkdmsq11-SSIS-20171108-masked"

'Creating a FileSystemObject
Public FSO As New FileSystemObject

Sub MAIN()

    ' Clear Data
    Sheets(1).Select
    Cells(1, 1).CurrentRegion.Select
    Selection.Delete
    
    ' Create Header
    sHeader = "No|File Path|File Name|File Type|File Size(M)|Modification Date"
    aHeader = Split(sHeader, "|")
    
    For c = 0 To UBound(aHeader)
        Cells(1, c + 1).Value = aHeader(c)
    Next c
    
    Cells(2, 1).Select
    
    ' Scan Folder
    ScanFolder (TopLevelFolder)
    
    ' Post-Actions
    LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    
    Cells(2, 1).Value = 1
    Cells(3, 1).Value = 2
    
    Range("A2:A3").Select
    Selection.AutoFill Destination:=Range("A2:A" & LastRow)
        
    Columns("E:E").Select
    Selection.NumberFormat = "0.0"
    
    Columns.AutoFit
    Cells(1, 1).Select
End Sub

Sub ScanFolder(sFolder As Variant)
    'Declaring variables
    Dim objFolder As Folder
    Dim objFile As file
    Dim objSubFolder As Folder
    ' Dim strPath As String
    Dim NextRow As Long
    
    'Create the object of this folder
    Set objFolder = FSO.GetFolder(sFolder)
    
    'Find the next available row
    NextRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1

    'Loop through each file in the folder
    For Each objFile In objFolder.Files
        If InStr(objFile.Name, "dtsx") > 0 Then
            'List the name, size, and date/time of the current file
            Cells(NextRow, 2).Value = Replace(objFile.Path, objFile.Name, "")
            Cells(NextRow, 3).Value = objFile.Name
            Cells(NextRow, 4).Value = objFile.Type
            Cells(NextRow, 5).Value = objFile.Size / 1025 / 1025
            Cells(NextRow, 6).Value = objFile.DateLastModified
            'Find the next row
            NextRow = NextRow + 1
        End If
    Next objFile

    For Each objSubFolder In objFolder.SubFolders
        ScanFolder (objSubFolder.Path)
    Next objSubFolder
End Sub

PLSQL - Cursor Liked For Loop Example

    BEGIN
        FOR RESULT IN (
            SELECT C.CASEKEY AS THECASEKEY, CE.CASEEVENTKEY AS THECASEEVENTKEY
            FROM CASES C
            JOIN CASEEVENT CE ON C.CASEKEY = CE.CASEKEY
            )
        LOOP
            UPDATE CASES
            SET CURRENTCASEEVENTKEY = RESULT.THECASEEVENTKEY
            WHERE CASEKEY = RESULT.THECASEKEY;
        END LOOP;
    END;
    

PLSQL - Drop Table if Exist

DECLARE 
C INT; 
BEGIN 
  SELECT COUNT(*) INTO C FROM USER_TABLES WHERE TABLE_NAME = UPPER('TEMPTABLE');
  IF C=1 THEN 
      EXECUTE IMMEDIATE 'DROP TABLE TEMPTABLE';
   END IF;
END;

PLSQL - SQLPlus Patch File Template

ACCEPT LOG_DIR PROMPT 'Please enter the full path to the log files  (default  C:\Temp\Log) : ' DEFAULT C:\Temp\Log
SPOOL &&LOG_DIR\MyProject_MyIssue_MyDate.log

/*
Author : 
Date   : 
Client : 
Comments: 

*/

Prompt #############################################################
Prompt                 Connection Details
Prompt #############################################################
set termout  off
define _USER
define _CONNECT_IDENTIFIER
define _PRIVILEGE
define _O_VERSION
define _DATE
set termout  on
Prompt #############################################################
Prompt 
Prompt #############################################################

SET TIMING ON
------------------Script Start--------------------------

select settingvalue ENVIRONMENT from globalsetting where settingkey = 94;


PROMPT
PROMPT
Prompt #############################################################
PROMPT  1.1: 
    
PROMPT
PROMPT
Prompt #############################################################
PROMPT  1.2: 
    
PROMPT
PROMPT
Prompt #############################################################
PROMPT  1.3 

------------------Script End-----------------------------

Prompt #############################################################
Prompt #############################################################
prompt

SPOOL OFF

HOST &&LOG_DIR\MyProject_MyIssue_MyDate.log

PLSQL - For Loop Example

CREATE TABLE TEMPTABLE
AS
SELECT ROW_NUMBER() OVER (ORDER BY L.IPCONTACTTYPEID) AS ID, R.RECORDID AS RECORDID, L.IPCONTACTTYPEID AS IPCONTACTTYPEID, I.LONGNAME AS NAME, GM.GLOBALMEDIAKEY AS GMKEY
FROM SRC_RECORD R
JOIN SRC_CONFLICT C ON R.RECORDID = C.CONFLICTID
JOIN SRC_LNKIPCONTACT L ON R.RECORDID = L.RECORDID 
JOIN SRC_USERINFORMATION I ON L.USERID = I.USERID 
JOIN GLOBALMEDIA GM ON GM.PARENTKEY = R.CASEKEY 
JOIN TABLEMEDIATYPE TMT ON TMT.MEDIATYPEKEY = GM.MEDIATYPEKEY
WHERE TMT.MEDIATYPEDESCRIPTION = 'Additional Notes' 
AND L.IPCONTACTTYPEID IN ( 65, 66, 57, 107 ) 
ORDER BY 1; 

DECLARE IFINAL NUMBER;
BEGIN        
SELECT MAX(ID) INTO IFINAL FROM TEMPTABLE;
FOR ICOUNT IN 1..IFINAL 
LOOP        
UPDATE GLOBALMEDIALOB R
SET R.LONGVALUE = ( SELECT C2B( BLOB_TO_CLOB( R.LONGVALUE ) ||
TO_CLOB ( CHR(13)||CHR(10)|| 
CASE TT.IPCONTACTTYPEID
WHEN 65 THEN 'Searcher - ' || TT.NAME 
WHEN 66 THEN 'Third Party Contact - ' || TT.NAME
WHEN 57 THEN 'Law Firm Contact - ' || TT.NAME
WHEN 107 THEN 'Patent Lead - ' || TT.NAME
END ))
FROM TEMPTABLE TT WHERE R.GLOBALMEDIAKEY = TT.GMKEY AND TT.ID = ICOUNT)
WHERE R.GLOBALMEDIAKEY = ( SELECT GMKEY FROM TEMPTABLE WHERE ID = ICOUNT );
END LOOP;
END;

COMMIT;

DROP TABLE TEMPTABLE;