메모하는 습관

Oracle Instantclient 적용 가이드

오라클 기반 개발 환경을 로컬에 구성하려면 오라클 클라이언트를 로컬환경에 설치해야 한다.

하지만 클라이언트 설치 없이 접속이 가능하도록 "instant Client"를 오라클에서 내놓았다.. 👍🏽👍🏽

1. 오라클 홈페이지에서 instant Client 다운 (환경에 맞는 32bit, 64bit)

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html" target="_blank" rel="noopener" data-mce-href="http:// http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html">http:// http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html 

 기존 PATH에         -> ;D:/oracle_instantclient; 
 신규 ORACLE_HOME    -> D:/oracle_instantclient 
 신규 TNS_ADMIN 생성  -> D:/oracle_instantclient (TNSNAMES.ORA 경로 path)
 신규 NLS_LANG 생성   -> KOREAN_KOREA.AL32UTF8 / KOREAN_KOREA.KO16MSWIN949 (DB 캐릭터셋에 맞춰서)

오라클 instantclient 를 8개월간 잘 사용했었는데 최근 신규 프로젝트에서NLS_LANG 과 관련된 문제에 직면하게 되었다. 클라이언트 버전으로 인스톨을 한 경우에는 NLS_CHARACTERSET 을 설치시에 설정을 하지만, 오라클 instantclient를 사용할 경우는 반드시 환경변수에 추가 하거나 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 에 NLS_LANG 문자열 값으로 캐릭터 셋을 지정해 줘야 한다. 이 부분을 지나치면 오라클 패키지 및 프로시저 컴파일시에 정상적인 한글이 깨지는 상황 발생, 다른 동료가 생성한 패키지에 한글이 깨지는 경우가 발생...민폐 OTL ...꼭 챙기자!!

CHARACTERSET 조회

SELECT * FROM sys.PROPS$ WHERE NAME = 'NLS_CHARACTERSET'; 

EXTPROC_CONNECTION_DATA =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))     )     (CONNECT_DATA =       (SID = PLSExtProc)       (PRESENTATION = RO)     )   ) ALIAS =    (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = DB 호스트)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = DB네임)       (SRVR = DEDICATED)     )  ) 

ms-sql getDate() format

--Getdate() 
Select Getdate() 

--YYYY/MM/DD 
Select Convert(varchar(10),Getdate(),111) 

--YYYYMMDD 
Select Convert(varchar(10),Getdate(),112) 

--HH:MM:SS 
Select Convert(varchar(8),Getdate(),108) 

--HH:MM:SS:mmm 
Select Convert(varchar(12),Getdate(),114) 

--HHMMSS 
Select Replace(Convert(varchar(8),Getdate(),108),':','') 

--HHMMSSmmm 
Select Replace(Convert(varchar(12),Getdate(),114),':','')

--YYYY/MM/DD HH:MM:SS 
Select Replace(Convert(varchar(30),Getdate(),120),'-','/') 

--YYYY/MM/DD HH:MM:SS 
Select Replace(Convert(varchar(30),Getdate(),121),'-','/') 

--YYYY/MM/DD HH:MM:SS 
Select Convert(varchar(10),Getdate(),111) + Space(1) + Convert(varchar(8),Getdate(),108) 

--YYYYMMDDHHMMSS 
Select Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(8),Getdate(),108),':','')

보통은 1Byte 문자열 타입을 쓰지만
다중언어 지원 시스템에선 2Byte를 사용한다.
 
│　MsSQL　│　Oracle │
* 1byte
│　char　 │char　　 │ -> 고정 문자열 : 한글 2자리, 영문 1자리
│　varchar│varchar2 │ -> 비고정 문자열 : 한글 2자리, 영문 1자리
 
*2Byte : 유니코드 문자열
│　nchar　 │nchar　　│ -> 고정 문자열 : 한글 1자리, 영문 1자리 , 기타언어 1자리

│　nvarchar│nvarchar2│ -> 비고정 문자열 : 한글 1자리, 영문 1자리 , 기타언어 1자리

.주기적으로 수행되어지는 JOB을 JOB QUEUE를 사용하여 스케줄링 할 수 있습니다.. 
 JOB을 스케줄링 하기 위해서 ORACLE의 DBMS_JOB 패키지를 이용합니다.. 
 JOB QUEUE PROCESS가 JOB QUEUE 안의 잡을 수행합니다.. 
 JOB으로 등록될 수 있는 것은 PL/SQL 프로시저 또는 패키지가 되며 
 예를 들어소스 디비의 테이블들에서 타겟 테이블로 데이터를 적재하는 프로시저를 생성했는데 
 1분단위로 데이터를 타겟 테이블로 적재를 해야 할 때 DBMS_JOBS에 등록하여 
 스케줄링 할 수 있습니다. 

  

.JOB_QUEUE_PROCESSES 파라미터가 이와 관련된 초기화 파라미터로 0으로 설정되면 
 JOB QUEUE PROCESS는 시작되지 않으며 JOB QUEUE의 어느 잡도 수행되지 않습니다.. 
 JOB_QUEUE_PROCESSES 파라미터의 MAX값이 설정되어야 오라클 인스턴스 위에서 
 동시에 잡을 수행할 수 있다. 설정할 수 있는 최고 값은 1000입니다.. 
 JOB_QUEUE_PROCESSES=60 과 같이 설정할 수 있습니다.. 

  

.등록되거나 수행되는 잡에 대해서는 DBA_JOBS 또는 USER_JOBS 딕셔너리 뷰를 통해 
 확인 할 수 있다. 

.JOB_QUEUE_PROCESSES 는 다이나믹 하게 DB를 SHUTDOWN하지 않고 ALTER SYSTEM 
 명령을 이용해서 설정할 수 있습니다. 
 ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20; 

  

.JOB QUEUE안의 JOB을 스케줄링 하기 위해서는 DBMS_JOBS패키지를 사용할 수 있으며 
 JOB_QUEUE를 사용하기 위해 관련된 DB 권한은 없다. 

  

.다음은 DBMS_JOBS의 패키지를 사용하기 위한 패키지의 프로시져들입니다. 
  
 SUBMIT - 잡큐의 잡을 등록합니다. 
  REMOVE - 잡큐의 잡을 제거합니다. 
  CHANGE - 잡큐의 잡을 변경합니다. 
  NEXT_DATE - 잡의 다음 수행시간을 변경합니다. 
  INTERVAL - 잡 수행 주기를 변경합니다. 
  WHAT - 잡으로 등록된 프로시저 또는 패키지를 변경합니다. 
  RUN - 잡을 수동으로 강제로 수행합니다. 


 .새 JOB을 JOB QUEUE에 등록하기 위해 사용되는 파라미터로 DBMS_JOB.SUBMIT()에 들어가느 
  파라미터 입니다. 
  
  JOB - OUTPUT 파라미터로 생성한 잡에 의해 할당되는 식별자 입니다. 
  WHAT - JOB QUEUE에 등록되는 PL/SQL 프로시저 또는 패키지 입니다. 
  NEXT_DATE - 잡이 수행되는 다음 시간입니다. 
  INTERVAL - 잡이 수행되는 주기로 초 단위까지 지정 가능합니다. 
   
  JOB_QUEUE에 등록하는 예제입니다. 
 VARIABLE jobno NUMBER 
 BEGIN 
   DBMS_JOB.SUBMIT(:jobno, 
                   'SP_IN_EMP_SAL;', 
                   SYSDATE, 
                   'SYSDATE + 1'); 
   COMMIT; 
 END; 
 / 

 위의 PL/SQL문을 SQL PLUS에서 수행합니다. 
  첫번째 파라미터가 JOB NUMBER가 부여되는 부분이고 
  두번째 파라미터가 WHAT으로 SP_IN_EMP_SAL이라는 프로시저를 등록했습니다. 
  세번째 파라미터가 NEXT_DATE이며 4번째 파라미터가 잡 수행 주기로 하루에 한번씩 
  수행하라는 의미입니다. 


  DBMS_JOB을 이용하면 특정시간, 특정요일, 특정일, 30초단위, 매분 정각, 매시정각 
  등 다양하게  잡을 스케줄링 하는 것이 가능합니다. 
  ETL 수행 할 때도 유용하게 사용할 수 있습니다. 


  

잡 수행 간격 조정의 예 
 .SYSDATE+ 7  :  7일에 한번씩 잡이 수행됩니다. 
 .SYSDATE+1/24 : 1시간에 한번씩 잡이 수행됩니다. 
 .SYSDATE+30/86400 : 30초에 한번씩 잡이 수행됩니다. 
 .최초 잡 수행시간이 14:02분일 경우 매시 14:02분에 잡을 수행해야 될 경우 
   =>trunc(SYSDATE,'MI')+1/24  
 .최조 잡 수행시간이 06시 이고 매 8시간마다 정각에 잡이 수행되어야 될 경우 
   =>trunc(SYSDATE,'MI')+8/24 
 .매주 월요일 정각 3시에 잡이 수행되어야 할 경우 
   =>NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/25 
 .각 분기마다 첫번째 월요일에 잡이 수행되어야 될 경우 
   =>NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3),'MONDAY') 

주의> dbms_job으로 잡을 스케줄링하게 될 경우 예를 들어 잡 최초 수행시간이 22일 14:00시이고 
         매시 정각에 잡이 수행되어야 할 경우 SYSDATE+1/24로 간격을 주게 되면 매 정시에 도는 것이 
         아니라 잡 수행 시간에 따라 약간씩 늦어지게 되어 14:00:04 => 15:00:07 => 16:00:10 이런식으로 
         잡 수행시간이 잡히게 됩니다. 따라서 정각에 수행되게 하려면 trunc함수를 이용해서 무조건 분에서 
         잘라내여 00으로 만들어 준다. trunc(SYSDATE,'MI') 

  

잡의 삭제 
 BEGIN 
 DBMS_JOB.REMOVE(14443); 
 END; 
 / 
 14443은 잡 번호 이다. USER_JOBS 데이터 딕셔너리 뷰를 보면 잡 번호를 알 수 있습니다. 

  

잡의 변경 
 BEGIN 
 DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3'); 
 END; 
 / 


잡으로 등록된 프로시저/패키지 변경 
BEGIN 
 DBMS_JOB.WHAT(14144, 
     'SP_IN_EMP_SAL;'); 
END; 
/ 


잡 다음 수행시간 변경 
BEGIN 
 DBMS_JOB.NEXT_DATE(14144, TRUNC(SYSDATE,'MI') + 4); 
END; 
/ 

  


잡 수행 간격 변경 
BEGIN 
 DBMS_JOB.INTERVAL(14144, TRUNC(SYSDATE,'MI')+30/1440); 
END; 
/ 

  


잡 수행 정지 
  잡이 BROKEN되면 잡은 수행되지 않으며 강제로 수행 할 시에는 DBMS_JOB.RUN()을 통해  수행합니다.. 
BEGIN 
 DBMS_JOB.BROKEN(14144, TRUE); 
END; 
/ 

  


잡큐 정보 VIEWING 
 DBA_JOBS, USER_JOBS, ALL_JOBS를 이용합니다. 


SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN 
FROM DBA_JOBS; 

JOB    NEXT_DATE  NEXT_SEC  FAILURES   B 
------- ---------  --------   --------   - 
9125    01-JUN-01   00:00:00     4       N 
14144   24-OCT-01   16:35:35    0       N 
9127    01-JUN-01   00:00:00    16       Y 
3 rows selected. 


  

DBMS_JOB의 활용예제를 주기적으로 소스테이블에서 타켓 테이블로 

적재할 시 어떻게 사용할 수 있는지 간단하게 예를 만들었습니다 

  

--수행될 잡의 목록이 들어갈 테이블 
CREATE TABLE JOB_LIST 
(JOB_ID VARCHAR2(2), 
 JOB_TYPE VARCHAR2(1), 
 JOB_NAME VARCHAR2(30), 
 JOB_EXEC_HOUR VARCHAR(2), 
 JOB_PARENTID VARCHAR2(2), 
 CONSTRAINTS JOB_LIST_PK PRIMARY KEY(JOB_ID) 
 USING INDEX 
 TABLESPACE CYS_INDEX 
 PCTFREE 0 
 STORAGE(INITIAL 32K NEXT 32K PCTINCREASE 0) 
 NOLOGGING) 
 TABLESPACE CYS_DATA 
 PCTFREE 0 
 STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0); 
 / 
  

CREATE UNIQUE INDEX JOB_LIST_IDX01 
ON JOB_LIST(JOB_NAME,JOB_EXEC_HOUR) 
TABLESPACE CYS_INDEX 
PCTFREE 0 
STORAGE(INITIAL 64K NEXT 64K PCTINCREASE 0); 
/ 


-잡 수행 히스토리 테이블로 하루에 한번씩 JOB_LIST 테이블에서 LOG테이블로 JOB_LIST가 복사된다. 
CREATE TABLE JOB_LOG 
(JOB_ID VARCHAR2(2), 
 JOB_EXEC_DATE VARCHAR2(8), 
 JOB_START_TIME DATE, 
 JOB_END_TIME DATE, 
 JOB_DATASTART_TIME DATE, 
 JOB_DATAEND_TIME DATE, 
 JOB_STATUS VARCHAR2(1), 
 JOB_ERR_MSG VARCHAR2(100), 
 CONSTRAINTS JOB_LOG_PK PRIMARY KEY(JOB_ID,JOB_EXEC_DATE) 
 USING INDEX 
 TABLESPACE CYS_INDEX 
 PCTFREE 0 
 STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0) 
 NOLOGGING, 
 CONSTRAINTS JOB_LIST_FK FOREIGN KEY(JOB_ID) 
 REFERENCES JOB_LIST(JOB_ID)) 
 TABLESPACE CYS_DATA 
 STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0) 


--JOB_ID를 부여하기 위한 시퀀스 
CREATE SEQUENCE JOB_NUM 
START WITH 1 
INCREMENT BY 1 


--하루에 한번 DBMS_JOB을 이용하여 JOB_LIST의 JOB들을 JOB_LOG에 INSERT하기 위한 프로시져 
--다음날 수행할 JOB을 LOG로 넣는다. 
CREATE OR REPLACE PROCEDURE 
SP_IN_JOB_LOG(V_INDATE IN VARCHAR2 DEFAULT NULL) 
AS 
BEGIN 
  INSERT INTO JOB_LOG 
  SELECT JOB_ID, 
    NVL(V_INDATE,TO_CHAR(SYSDATE+1,'YYYYMMDD')), 
   NULL, 
   NULL, 
   NULL, 
   NULL, 
   'N', 
   NULL 
  FROM JOB_LIST; 
  
  COMMIT; 
END SP_IN_JOB_LOG; 

  

--SP_IN_JOB_LOG 프로시저를 DBMS_JOB에 등록한다. 
DECLARE 
    JOB_NUMBER NUMBER; 
BEGIN 
  DBMS_JOB.SUBMIT(JOB_NUMBER,  --JOB번호 
        'SP_IN_JOB_LOG;',  --프로시저명 
         TO_DATE('20050208180000','YYYYMMDDHH24MISS'),  --NEXT_DATE 
        'TRUNC(SYSDATE,''MI'')+1');  --잡 수행 간격(매일 정각 6시) 
END; 


--JOB_LIST에 수행할 프로시져(JOB)을 등록 
--DBA_USERS테이블을 이용해서 24개의 로우를 만들어 낸 후 카테시안 프러덕을 이용 
INSERT INTO JOB_LIST 
SELECT JOB_NUM.NEXTVAL JOB_ID, 
    JOB_TYPE, 
    JOB_NAME, 
    B.CNT JOB_EXEC_HOUR, 
    NULL 
FROM( 
  SELECT NULL JOB_ID, 
      'F' JOB_TYPE, 
      'SP_IN_F_SALE_SUM' JOB_NAME, 
      NULL JOB_EXEC_HOUR, 
      NULL 
  FROM DUAL) A, 
 ( SELECT LPAD(ROWNUM-1,2,'0') CNT FROM DBA_TABLES 
     WHERE ROWNUM<25) B 

COMMIT; 

--JOB_LIST를 JOB_LOG로 INSERT(현재 SP_IN_JOB_LOG 가 다음일을 INSERT하도록 되어 있으므로 해당일을 넣어줌) 
EXEC SP_IN_JOB_LOG('20050208'); 


--제대로 들어갔는지 확인 
SELECT * FROM JOB_LOG; 


--SOURCE 테이블을 시간단위로 섬머리 해서 TARGET 테이블로 적재하기 위한 프로시져 
--ERROR없이 매 시간 돌 때는 파라미터 없이 SP_IN_F_SALE_SUM으로 수행 되고 수동으로 어떤 데이터의 
--범위를 적재해야 할 경우 시간의 범위를 파라미터로 넘겨줌 
CREATE OR REPLACE PROCEDURE 
SP_IN_F_SALE_SUM(V_STARTTIME IN VARCHAR2 DEFAULT NULL, 
        V_ENDTIME IN VARCHAR2 DEFAULT NULL) 
AS 
  D_STARTTIME DATE; 
  D_ENDTIME DATE; 
  V_ERR_MSG VARCHAR2(100); 
BEGIN 
  
 --프로시저가 파라미터 값이 없이 수행될 경우 
 IF V_STARTTIME IS NULL AND V_ENDTIME IS NULL THEN 
  SELECT NVL(JOB_DATAEND_TIME,TRUNC(SYSDATE-1/24,'HH24')) 
  INTO D_STARTTIME 
  FROM JOB_LOG 
  WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
             WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
       AND JOB_EXEC_HOUR=TO_CHAR(SYSDATE-1/24,'HH24')) 
  AND JOB_EXEC_DATE=TO_CHAR(SYSDATE,'YYYYMMDD') 
  AND JOB_STATUS='Y'; 

  D_ENDTIME:=TRUNC(SYSDATE,'HH24'); 
 ELSE 
  SELECT NVL(JOB_DATAEND_TIME,TO_DATE(V_STARTTIME,'YYYYMMDDHH24MISS')) 
  INTO D_STARTTIME 
  FROM JOB_LOG 
  WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
             WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
       AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2)) 
  AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8) 
  AND JOB_STATUS='Y'; 

  D_ENDTIME:=TO_DATE(V_ENDTIME,'YYYYMMDDHH24MISS'); 
 END IF; 
  
  
 --수행되는 프로시저의 START시간을 찍어주고 RUNNING으로 표시 
 UPDATE JOB_LOG 
 SET JOB_START_TIME=SYSDATE, 
  JOB_STATUS='R' 
 WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
         WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
      AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2)) 
 AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8); 
  
 -- DML------ 
  
  --수행되어질 INSERT문 
  
 -- DML----- 
  
 COMMIT; 
  
 --프로시저가 ERROR없이 수행이 끝나면 END 시간과 가져온 데이터의 범위를 찍어줌 
 UPDATE JOB_LOG 
 SET JOB_END_TIME=SYSDATE, 
  JOB_DATASTART_TIME=D_STARTTIME, 
  JOB_DATAEND_TIME=D_ENDTIME, 
  JOB_STATUS='Y' 
 WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
         WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
      AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24')) 
 AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8); 
  
 COMMIT; 
  
EXCEPTION 
   WHEN OTHERS THEN 
      V_ERR_MSG:= SUBSTRB(SQLERRM, 1, 80); 
     
     UPDATE JOB_LOG 
     SET JOB_END_TIME=SYSDATE, 
      JOB_STATUS='E', 
      JOB_ERR_MSG=V_ERR_MSG 
           WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
                  WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
         AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24')) 
           AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8); 
     
     COMMIT; 
END SP_IN_F_SALE_SUM; 
/ 


--SP_IN_F_SALE_SUM 프로시저를 DBMS_JOB에 등록합니다. 
DECLARE 
    JOB_NUMBER NUMBER; 
BEGIN 
  DBMS_JOB.SUBMIT(JOB_NUMBER,  --JOB번호 
        'SP_IN_F_SALE_SUM;',  --프로시저명 
         TO_DATE('20050209000000','YYYYMMDDHH24MISS'),  --NEXT_DATE 
        'TRUNC(SYSDATE,''MI'')+1/24');  --잡 수행 간격(매시간 정각) 
END; 


JOB의 시간이나 간격 등록된 프로시저등을 변경하고자 할 때 DBMS_JOB의 다른 프로시져를 이용해서 변경합니다. 
2번째 정리 부분에 설명되어 있습니다.

[출처] http://blog.naver.com/gnpado/40017478314 

-- 프로시저 생성
CREATE OR REPLACE PROCEDURE INSERT_TEST_JOB
IS
BEGIN
   INSERT INTO TEST_JOB (NUM, REGDATE)
   VALUES ( (SELECT MAX (NVL (NUM, 0)) + 1 FROM TEST_JOB), SYSDATE);
   COMMIT;
END INSERT_TEST_JOB;

--프로시저 삭제
DROP PROCEDURE insert_test_job;

-- JOB 생성
DECLARE
   JOBNO   NUMBER;
BEGIN
   DBMS_JOB.SUBMIT (JOBNO,
                    'USERS.INSERT_TEST_JOB;',
                    SYSDATE + 1 / 24 / 60,                  -- job이 실행될 시간 1분후
                    'TRUNC(ADD_MONTHS(SYSDATE, 1))+6/24', -- job이 실행되는 간격  매달 1일 6시
                    FALSE);
   COMMIT;
END;

--JOB 삭제
execute dbms_job.remove(잡번호);

--JOB 조회
SELECT WHAT, JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM USER_JOBS;

* job queue 정보 VIEWING
 SELECT * FROM DBA_JOBS
 SELECT * FROM USER_JOBS
 SELECT * FROM ALL_JOBS