perl command line console

perl -d -e 1

find out when a index applied

select created from dba_objects where object_type='INDEX' and object_name='IX1_STAGE_NCF_OPTION';

find dates difference in oracle

select numtodsinterval(max(create_date)-min(create_date),'day') from table where master_process_id=17292640;

Orcale client tools tnsping

Oracle client has a tool called tnsping,it help us to check a tns name working or not.


tnsping somename

VBA operate on excel sheet

I have situation that create seeding query from data in excel sheet,so I write some VBA code that scan the data in excel cell,then generate query in one column

here is the code:


Sub ExpandRows()
    Dim dat As Variant
    Dim i As Long
    Dim rw As Range
    Dim rng As Range
    Dim access_group_id As Long
   

    Set rng = ActiveSheet.UsedRange
    dat = rng

    ' Loop thru your data, starting at the first row
    For i = 2 To UBound(dat, 1) Step 1
           
        ' clean the cell
        Set rw = rng.Rows(i).EntireRow
        rw.Cells(1, 12).Value = ""
       
        If dat(i, 4) = "Y" Then
           
            Set rw = rng.Rows(i).EntireRow
            rw.Cells(1, 12).Value = rw.Cells(1, 12).Value & vbLf
            rw.Cells(1, 12).Value = rw.Cells(1, 12).Value & "insert into logins_roles (login_id,role_id,access_group_id,organization_id) select (select login_id from logins where name='" & rw.Cells(1, 7).Value & _
            "' and rownum <= 1)," & _
            "27," & _
            rw.Cells(1, 3) & _
            "," & rw.Cells(1, 2).Value & " from dual " & _
            " where not exists (select 1 from logins_roles where login_id = (select login_id from logins where name='" & rw.Cells(1, 7).Value & _
            "'" & _
            " and rownum <= 1) and " & _
            " role_id=27" & " and access_group_id=" & _
            rw.Cells(1, 3) & _
            " and organization_id = " & rw.Cells(1, 2) & ")" & _
            " and exists (select 1 from logins where name='" & _
            rw.Cells(1, 7).Value & _
            "' and active = 1);"
        End If
       
        If dat(i, 5) = "Y" Then
           
            Set rw = rng.Rows(i).EntireRow
            rw.Cells(1, 12).Value = rw.Cells(1, 12).Value & vbLf
            rw.Cells(1, 12).Value = rw.Cells(1, 12).Value & "insert into logins_roles (login_id,role_id,access_group_id,organization_id) select (select login_id from logins where name='" & rw.Cells(1, 7).Value & _
            "' and rownum <= 1)," & _
            "2," & _
            rw.Cells(1, 3) & _
            "," & rw.Cells(1, 2) & " from dual " & _
            " where not exists (select 1 from logins_roles where login_id = (select login_id from logins where name='" & rw.Cells(1, 7).Value & _
            "'" & _
            " and rownum <= 1) and " & _
            " role_id=2" & " and access_group_id=" & _
            rw.Cells(1, 3) & _
            " and organization_id = " & rw.Cells(1, 2).Value & ")" & _
            " and exists (select 1 from logins where name='" & _
            rw.Cells(1, 7).Value & _
            "' and active = 1);"
        End If
       
        If dat(i, 6) = "Y" Then
           
            Set rw = rng.Rows(i).EntireRow
            rw.Cells(1, 12).Value = rw.Cells(1, 12).Value & vbLf
            rw.Cells(1, 12).Value = rw.Cells(1, 12).Value & "insert into logins_roles (login_id,role_id,access_group_id,organization_id) select (select login_id from logins where name='" & rw.Cells(1, 7).Value & _
            "' and rownum <= 1)," & _
            "26," & _
            rw.Cells(1, 3) & _
            "," & rw.Cells(1, 2).Value & " from dual " & _
            " where not exists (select 1 from logins_roles where login_id = (select login_id from logins where name='" & rw.Cells(1, 7).Value & _
            "'" & _
            " and rownum <= 1) and " & _
            " role_id=26" & " and access_group_id=" & _
            rw.Cells(1, 3) & _
            " and organization_id = " & rw.Cells(1, 2).Value & ")" & _
            " and exists (select 1 from logins where name='" & _
            rw.Cells(1, 7).Value & _
            "' and active = 1);"
         
        End If
       
        Set rw = rng.Rows(i).EntireRow
        rw.Cells(1, 12).Value = rw.Cells(1, 12).Value & vbNewLine
       

    Next
End Sub

copy file between machines

following command is copy file from linux server into local folder of windows by cygwin


 scp uploads@xxx.autc.com:testftp/ncf/inbound/280DAY.DAT 280DAT.DAY

"testftp/ncf/inbound/280DAY.DAT" is the file in remote server's uploads' home folder,last parameter is the file name in local folder

can not download pom dependency from remote repository while VPN connected and under JDK7

add following to system properties will solve problem

MAVEN_OPTS=-Djava.net.preferIPv4Stack=true