bat 파일 작성하기 - 02 : 오라클 연동

BAT 파일 작성하는 방법 인공지능 선생님께 물어보기

[1] bat 스크립트에서 디렉토리 안에 있는 다수의 sql 파일을 실행하는 방법

Windows Batch 파일을 사용하여 디렉토리 내의 모든 SQL 파일을 실행하려면 다음 단계를 따라 작성하십시오.

1. 먼저, 오라클 클라이언트를 설치하고 환경 변수 PATH에 추가하여 sqlplus 명령이 실행되도록 해야 합니다.

2. 아래 코드를 입력합니다.

@echo off
setlocal enabledelayedexpansion

:: 데이터베이스 연결 정보 설정
set DB_CONNECTION_STRING=YOUR_USERNAME/YOUR_PASSWORD@YOUR_HOST:YOUR_PORT/YOUR_SERVICE_NAME

:: SQL 파일이 있는 디렉토리 설정
set SQL_FILES_DIR=C:\path\to\your\sql\files

:: 디렉토리의 모든 SQL 파일 실행
for %%f in (%SQL_FILES_DIR%\*.sql) do (
    echo Executing file: %%f
    sqlplus -S !DB_CONNECTION_STRING! @"%%f"
    echo Finished executing file: %%f
)

echo All SQL files have been executed.

위 코드에서 필요한 정보를 채워 넣으십시오.

• DB_CONNECTION_STRING: 오라클 데이터베이스 연결 문자 (예: username/password@localhost:1521/orclpdb1)

• SQL_FILES_DIR: SQL 파일이 저장된 디렉토리 경로

이제 수정한 .bat 파일을 저장한 후 실행하면, 디렉토리에 있는 모든 SQL 파일이 순차적으로 오라클 데이터베이스에 실행됩니다.

[2] bat 스크립트에서 에러발생 시 throw를 던지는 방법

1. errorlevel 설정

Windows Batch 스크립트에서 에러가 발생할 때 오류를 던지려면 errorlevel을 사용하여 명령의 실행 상태를 확인하고, 원하는 오류 코드로 종료해야 합니다.

예를 들어, 오라클 데이터베이스를 사용하는 이전 예제를 기반으로 에러가 발생했을 때 스크립트가 종료되도록 수정하겠습니다.

2. 예제 소스

@echo off
setlocal enabledelayedexpansion

:: 데이터베이스 연결 정보 설정
set DB_CONNECTION_STRING=YOUR_USERNAME/YOUR_PASSWORD@YOUR_HOST:YOUR_PORT/YOUR_SERVICE_NAME

:: SQL 파일이 있는 디렉토리 설정
set SQL_FILES_DIR=C:\path\to\your\sql\files

:: 디렉토리의 모든 SQL 파일 실행
for %%f in (%SQL_FILES_DIR%\*.sql) do (
    echo Executing file: %%f
    sqlplus -S !DB_CONNECTION_STRING! @"%%f"
    
    if errorlevel 1 (
        echo Error occurred while executing file: %%f
        exit /b 1
    )
    echo Finished executing file: %%f
)

echo All SQL files have been executed without errors.

이제 위의 배치 파일에서 sqlplus 명령 실행 시 오류가 발생하면, “Error occurred while executing file” 메시지가 출력되고 스크립트가 종료됩니다. exit /b 1 명령은 스크립트를 종료하고 오류 코드 1을 반환합니다.

이 방법을 사용하여 명령 실행 중 오류가 발생할 때마다 원하는 오류 코드와 메시지를 반환하도록 스크립트를 수정할 수 있습니다.

[3]. SQLplus spool 기능과 bat 스크립트를 활용한 방법

@echo off
setlocal enabledelayedexpansion

:: 데이터베이스 연결 정보 설정
:: 데이터베이스 연결 정보 설정
set DB_CONNECTION_STRING=YOUR_USERNAME/YOUR_PASSWORD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=YOUR_HOST)(PORT=YOUR_PORT))(CONNECT_DATA=(SID=YOUR_SID)))


:: SQL 파일이 있는 디렉토리 설정
set SQL_FILES_DIR=C:\path\to\your\sql\files

:: 실행 결과를 저장할 디렉토리 설정
set OUTPUT_DIR=C:\path\to\output\dir

:: 디렉토리의 모든 SQL 파일 실행
for %%f in (%SQL_FILES_DIR%\*.sql) do (
    echo Executing file: %%f
    set OUTPUT_FILE=!OUTPUT_DIR!\%%~nf.log
    echo Spooling to: !OUTPUT_FILE!
    (
      echo SPOOL !OUTPUT_FILE!
      echo @%%f
      echo SPOOL OFF
      echo EXIT
    ) | sqlplus -S !DB_CONNECTION_STRING!

    if errorlevel 1 (
        echo Error occurred while executing file: %%f
        exit /b 1
    )
    echo Finished executing file: %%f
)

echo All SQL files have been executed without errors.

위 코드에서 필요한 정보를 채워 넣으십시오.

• DB_CONNECTION_STRING: 오라클 데이터베이스 연결 문자열 (예: username/password@localhost:1521/orclpdb1)
• SQL_FILES_DIR: SQL 파일이 저장된 디렉토리 경로 OUTPUT_DIR: 실행 결과를 저장할 디렉토리 경로

DB_CONNECTION_STRING에서 필요한 정보를 채워 넣으십시오.

• YOUR_USERNAME: 데이터베이스 사용자 이름
• YOUR_PASSWORD: 데이터베이스 비밀번호
• YOUR_HOST: 데이터베이스 호스트 (예: localhost, 192.168.1.100 등)
• YOUR_PORT: 데이터베이스 포트 번호 (기본값: 1521)
• YOUR_SID: 데이터베이스 SID

이제 수정한 .bat 파일을 저장한 후 실행하면, 지정한 연결 문자열을 사용하여 Oracle 데이터베이스에 SID를 기반으로 접속하고 쿼리를 실행한 결과를 출력합니다.

이제 수정한 .bat 파일을 저장한 후 실행하면, 디렉토리에 있는 모든 SQL 파일이 순차적으로 오라클 데이터베이스에 실행되고 실행 결과가 지정한 출력 디렉토리에 저장됩니다. 실행 결과 파일의 이름은 각각의 SQL 파일 이름에 .log 확장자가 붙은 형태로 저장됩니다.

이 방법을 사용하면 실행 중인 SQL 파일의 실행 결과를 쉽게 확인하고 분석할 수 있습니다.

[4]. bat 스크립트 세부기능들

1. Windows Batch 스크립트에서 오늘 날짜를 조회하려면 다음 코드를 사용하십시오.

@echo off

set CURRENT_YEAR=%date:~10,4%
set CURRENT_MONTH=%date:~4,2%
set CURRENT_DAY=%date:~7,2%

echo Today's date is: %CURRENT_YEAR%-%CURRENT_MONTH%-%CURRENT_DAY%

2. Windows Batch 스크립트에서 현재 실행 중인 .bat 파일의 이름을 조회하려면 %~nx0 변수를 사용하십시오. 이 변수는 현재 실행 중인 배치 파일의 이름과 확장자를 반환합니다.

@echo off

echo The current batch file name is: %~nx0

3. 문자열 끝에서 자르기

@echo off
setlocal enabledelayedexpansion

set ORIGINAL_STRING=This is a sample string
set SUBSTRING=!ORIGINAL_STRING:~-4!

echo Original string: !ORIGINAL_STRING!
echo Last 4 characters: !SUBSTRING!

4. BAT + SQLPLUS + SPOOL

bat 스크립트에서 디렉토리의 모든 SQL 파일을 실행하는 방법을 제공하겠습니다.

다음은 SQL*Plus를 사용하여 디렉토리에 있는 모든 SQL 파일을 실행하고 각각의 결과를 spool 기능을 사용하여 로그 파일에 기록하는 방법입니다:

먼저, 실행할 SQL 파일의 목록을 만들어 주는 generate_sql_list.bat 파일을 작성합니다.

Copy code
@echo off
set SQL_DIRECTORY=C:\your_sql_directory
set SQL_LIST_FILE=sql_file_list.txt

dir /b /o:n %SQL_DIRECTORY%\*.sql > %SQL_LIST_FILE%

위의 스크립트는 지정된 디렉토리에서 .sql 확장자를 가진 모든 파일을 찾아 sql_file_list.txt 파일에 기록합니다.

이제 SQL*Plus에서 sql_file_list.txt에 있는 SQL 파일을 순차적으로 실행하는 run_all_sql.sql 스크립트를 작성합니다.

Copy code
-- run_all_sql.sql
set echo off
set termout off
set feedback off
set verify off

define directory = "C:\your_sql_directory"

spool run_all_sql.log

WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE

prompt Running all SQL scripts in &directory...

begin
  for file in (select text from external_table) loop
    execute immediate 'set termout on';
    execute immediate 'spool ' || '&directory.' || file.text || '.log';
    execute immediate '@' || '&directory.' || file.text;
    execute immediate 'spool off';
    execute immediate 'set termout off';
  end loop;
end;
/

spool off

set echo on
set termout on
set feedback on
set verify on

prompt All SQL scripts have been executed.

run_all_sql.sql 스크립트를 실행하기 전에, sql_file_list.txt를 읽어들일 외부 테이블을 생성합니다.

Copy code
CREATE TABLE external_table (
  text VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY your_directory
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('sql_file_list.txt')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;

이 외부 테이블은 sql_file_list.txt에 있는 SQL 파일의 목록을 읽어들입니다.

마지막으로, .bat 스크립트를 작성하여 SQL*Plus에 접속하고 run_all_sql.sql 스크립트를 실행합니다.

Copy code
@echo off
set ORACLE_USER=your_username
set ORACLE_PASSWORD=your_password
set ORACLE_TNS=your_tns

call generate_sql_list.bat

sqlplus %ORACLE_USER%/%ORACLE_PASSWORD%@%ORACLE_TNS% @run_all_sql.sql

echo All

5. BAT + SQLPLUS 단순한 방법

알겠습니다. 한 번의 접속으로 실행하고자 한다면, 다음과 같이 .bat 스크립트와 SQL 파일을 작성하여 작업을 수행할 수 있습니다.

먼저, SQL 파일 목록을 읽어들여 동적 SQL 스크립트를 생성하는 create_dynamic_sql.bat 파일을 작성합니다.

Copy code
@echo off
set SQL_DIRECTORY=C:\your_sql_directory
set DYNAMIC_SQL_FILE=dynamic_sql.sql

echo Generating dynamic SQL script...
echo set echo off > %DYNAMIC_SQL_FILE%
echo set termout off >> %DYNAMIC_SQL_FILE%
echo set feedback off >> %DYNAMIC_SQL_FILE%
echo set verify off >> %DYNAMIC_SQL_FILE%

for %%f in (%SQL_DIRECTORY%\*.sql) do (
  echo spool %%f.log >> %DYNAMIC_SQL_FILE%
  echo @%%f >> %DYNAMIC_SQL_FILE%
  echo spool off >> %DYNAMIC_SQL_FILE%
  echo prompt Done executing %%f. See %%f.log for results. >> %DYNAMIC_SQL_FILE%
)

echo set echo on >> %DYNAMIC_SQL_FILE%
echo set termout on >> %DYNAMIC_SQL_FILE%
echo set feedback on >> %DYNAMIC_SQL_FILE%
echo set verify on >> %DYNAMIC_SQL_FILE%
echo exit >> %DYNAMIC_SQL_FILE%
echo Done generating dynamic SQL script.

이 스크립트는 지정된 디렉토리에서 .sql 파일을 찾아 dynamic_sql.sql 파일에 spool 및 실행 명령을 생성합니다.

다음으로, run_dynamic_sql.bat 스크립트를 작성하여 dynamic_sql.sql 파일을 SQL*Plus에서 실행합니다.

Copy code
@echo off
set ORACLE_USER=your_username
set ORACLE_PASSWORD=your_password
set ORACLE_TNS=your_tns

call create_dynamic_sql.bat

sqlplus -S %ORACLE_USER%/%ORACLE_PASSWORD%@%ORACLE_TNS% @dynamic_sql.sql

echo All SQL scripts have been executed.

이제 run_dynamic_sql.bat 스크립트를 실행하면, 한 번의 접속으로 지정된 디렉토리의 모든 SQL 파일을 순차적으로 실행하고 각각의 결과를 로그 파일에 기록합니다.