Excel Visual Basic 을 이용한 Oracle dba_users 정보 가져오기

---

Excel Visual Basic 을 이용한 Oracle dba_users 정보 가져오기

Excel Macro + Visual Basic 을 이용하여 Oracle DB User 정보를 Excel File로 받아오는 소스

---

1. 매크로 버튼을 만들기에 앞서 개발 도구 탭 표시하기

• 엑셀의 리본탭 중 마우스 포인트를 아무 위치나 이동한 후 마우스 우클릭
• "리본 매뉴 사용자 지정" 클릭

 

2. "개발 도구" 체크하고 확인 클릭

 

3. Sheet 명 "ORADB" 로 변경

 

4. "개발 도구" 탭을 클리하고 "삽입": 에서 왼쪽 상단 양식 컨트롤에 있는 "단추"를 클릭

 

5. 원하는 영역에 드래그를 하고 "확인" 클릭

 

6. 단추 마우스 우클릭 후 매크로 지정 클릭

 

7. 새로 만들기 클릭

 

8. "도구" 메뉴에서 "참조" 클릭

 

9. ADODB 라이브러리를 추가하기 위해 "Microsoft ActiveX Data Objects 6.1 Library" 를 체크한다. ADODB는 호환성 문제로 버전을 2.7, 2.8, 6.1 을 참조로 하는게 좋다고 한다. 같이 선택 시 호환성 충돌이 일어나므로 1개만 선택한다.

 

10. Oracle DB USer 를 가져오는 VBA 코드 작성 (각 환경에 맞게 IP, User, PW 설정)

Sub 단추1_Click()
	Dim cnOra As ADODB.Connection
    Dim RsOra As ADODB.Recordset
    Dim db_name As String
    Dim UserName As String
    Dim Password As String
    
    Set cnOra = New ADODB.Connection
    Set RsOra = New ADODB.Recordset
    
    ActiveSheet.Range("A2:A1048576,E2:E1048576,F2:F1048576,G2:G1048576,I2:I1048576,J2:J1048576,K2:K1048576").ClearContents
    
    If ActiveSheet.Name = "ORADB" Then
    	db_name = "ORADB"
        rac_ip1 = "172.20.17.48"
        rac_ip2 = "172.20.17.49"
        port = "1521"
        
        UserName = "oraadm" 'DBA 계정'
        Password = "a12345" 'DBA 계정 패스워드'
    Else
    	Return
    End If
	
    cnOra.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
    					"Data Source=(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = " + rac_ip1 + ")(PORT = " + port + "))(ADDRESS = (PROTOCOL = TCP)(HOST = " + rac_ip2 + ")(PORT = " + port + ")))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = " + db_name + ")));" & _
                        "User ID=" + UserName + ";Password=" + Password + ";"
    cnOra.Open
    RsOra.CursorLocation = adUseClient
    RsOra.Open "select username, '' realname, '' password, '' using, created, expiry_date, account_status, lock_date, default_tablespace, profile, '' etc from sys.dba_users order by profile, account_status desc, username", cnOra, adOpenForwardOnly
    
    X = 1
    While Not RsOra.EOF
    X = X + 1
    
    ActiveSheet.Cells(X, 1) = RsOra![UserName]
    ActiveSheet.Cells(X, 5) = RsOra![created]
    ActiveSheet.Cells(X, 6) = RsOra![expiry_date]
    ActiveSheet.Cells(X, 7) = RsOra![account_status]
    ActiveSheet.Cells(X, 8) = RsOra![lock_date]
    ActiveSheet.Cells(X, 9) = RsOra![default_tablespace]
    ActiveSheet.Cells(X, 10) = RsOra![profile]
    
    RsOra.MoveNext
    Wend
    
    RsOra.Close
    cnOra.Close
    Set RsOra = Nothing
End Sub