【Oracle】オブジェクトのCREATE文を自動作成する

DB移行したいときとかに
使ってみるといいかもしれない。

ただ、ここで紹介する呼び出しはvbscriptでADODB使ってるので
その環境がない人は別の接続方法で試してほしい。

「SQL_DDL.vbs」として以下のソースコードをDB接続先情報を記述して保存する

Option Explicit

' ユーザ設定する項目 (DB 接続先情報)
Const HOST = "ホストIPアドレス"
Const PORT = "ポート番号"
Const SERVICE = "サービス名"
Const USER_ID = "ユーザー"
Const PASSWORD = "パスワード"
Dim respond, eLevel

MAIN()


' コンソール画面のメイン処理
Sub EXECUTE_SQL(sql,dr,tm_flg)

  'On Error Resume Next
  
  Dim con
  Set con = CreateObject("ADODB.Connection")
  
  Dim conStr
  conStr = "Provider=OraOLEDB.Oracle;" & _
           "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" & HOST & ")(PORT=" & PORT & ")))(CONNECT_DATA=(SERVICE_NAME=" & SERVICE & ")));" & _
           "User Id=" & USER_ID & ";Password=" & PASSWORD & ";"
  con.CommandTimeout = 3000
  con.ConnectionString = conStr
  con.Open
  
  Dim rs
  Set rs = con.Execute(sql)
  if Err.number = 0 then
		
      do until rs.eof=true

            if tm_flg="1" then
                'タイムスタンプをファイルにつける
            	WRITETXT dr & "\" & rs("TM_STAMP") & "_" & rs("OB_NAME") & ".sql",REPLACE(TRIM(cstr(rs("DDL"))),"""" & USER_ID & """" & ".","") 
            else
          	WRITETXT dr & "\" & rs("OB_NAME") & ".sql",REPLACE(TRIM(cstr(rs("DDL"))),"""" & USER_ID & """" & ".","") 
            end if
            rs.MoveNext 'カーソルを進める
      loop
  else
	    WScript.Echo( "エラーが発生しました！:" & Err.Description ) 
  end if

  Set rs = Nothing
  
  con.Close
  Set con = Nothing
 
End Sub
Function READTXT(filename)

    
    READTXT = " SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, '" & USER_ID & "') DDL," & _ 
              "'" & USER_ID & "',OBJECT_TYPE || '_' || OBJECT_NAME AS OB_NAME,TO_CHAR(TO_DATE(TIMESTAMP,'yyyy-mm-dd:hh24:mi:ss'),'yyyymmdd_hh24miss') AS TM_STAMP " & _
              " FROM USER_OBJECTS " & _
              " WHERE " & _
              " OBJECT_TYPE IN ('FUNCTION','PROCEDURE','TABLE','VIEW','INDEX','PACKAGE','TRIGGER') " & _
              " ORDER BY OBJECT_TYPE,OBJECT_NAME " 
             
End Function
SUB WRITETXT(filename,txt)


  Dim fso
  Dim outputFile
  Set fso = WScript.CreateObject("Scripting.FileSystemObject")


  ' 書き出しファイルの指定 (今回は新規作成する)
  Set outputFile = fso.OpenTextFile(filename, 2, True)
  outputFile.Write txt
  outputFile.Close
  Set outputFile = Nothing
  Set fso = Nothing

END SUB

Sub MAIN()


Dim args, arg(99), v, cnt
Dim str1, str2
Dim mode

Dim CRLF
REM 改行符号を設定します。
CRLF = chr(13) & chr(10)
REM 引数の設定
Set args = WScript.Arguments
cnt = 1
For Each v In args
    arg(cnt) = v
    cnt = cnt + 1
Next
eLevel = 0: REM エラーが発生したときに値を設定します。
respond = "": REM 呼び出し元への戻り値を設定します。
REM --------------------------------
  Dim sql
  'msgbox args(0) 
  sql = READTXT("")
  EXECUTE_SQL sql,args(0),args(1)
REM --------------------------------
REM 結果の表示
WScript.Echo respond
WScript.Quit (eLevel)
REM --------------------ここまで


End Sub

これをコマンドプロンプトで呼び出してみる。
最後の引数を1にするとファイル名がタイムスタンプ付きになる

@SET VBSCMD=SQL_DDL.vbs

@echo 日付の設定
date /t
set dt=%date%
set d0=%dt:~0,1%
set d1=%dt:~1,1%
set d2=%dt:~2,1%
set d3=%dt:~3,1%
set d4=%dt:~5,1%
set d5=%dt:~6,1%
set d6=%dt:~8,1%
set d7=%dt:~9,1%

@echo 時間の設定
time /t
@set tm=%time%
@set t0=%tm:~0,1%
@set t0=%t0: =0%
@set t1=%tm:~1,1%
@set t2=%tm:~3,1%
@set t3=%tm:~4,1%
@set t4=%tm:~6,1%
@set t5=%tm:~7,1%
@set t6=%tm:~9,1%
@set t7=%tm:~10,1%

@echo 時間文字列の出力
@set fname=%d0%%d1%%d2%%d3%%d4%%d5%%d6%%d7%%t0%%t1%%t2%%t3%%t4%%t5%%t6%%t7%


@timeout /T 2 /NOBREAK
MKDIR %fname%
@timeout /T 2 /NOBREAK

cscript //nologo %VBSCMD% %fname% 0

@timeout /T 2 /NOBREAK
MKDIR %fname%TIME
@timeout /T 2 /NOBREAK

cscript //nologo %VBSCMD% %fname%TIME 1

@timeout /T 2 /NOBREAK

PAUSE

ちなみにVBscriptで呼び出すSQLは以下になる

SELECT
    DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,'SQLTEST') DDL,
    'SQLTEST',
    OBJECT_TYPE
    || '_'
    || OBJECT_NAME AS OB_NAME,
    TO_CHAR(TO_DATE(TIMESTAMP,'yyyy-mm-dd:hh24:mi:ss'),'yyyymmdd_hh24miss') AS TM_STAMP
FROM
    USER_OBJECTS
WHERE
    OBJECT_TYPE IN (
        'FUNCTION',
        'PROCEDURE',
        'TABLE',
        'VIEW',
        'INDEX',
        'PACKAGE',
        'TRIGGER'
    )
ORDER BY
    OBJECT_TYPE,
    OBJECT_NAME

出力時の文字化けとかあったら不安だ。