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