Let's have a cup of coffee.

문제 : 오라클 클라이언트 사용하여 Unload 시 10자리 초과의 숫자에 대해 지수형으로 저장하여 csv 파일을 다시 aurora로 이행시 지수형부분이 아래와 같이 뒷부분이 "000000"로 저장되는 문제 발생, 또한 99999999999 숫자의 경우 100000000000로 저장되는 문제 발생

Case 1)

오라클 ASIS 데이터 : 12345678912
Unload CSV 파일 : 1.23456E+10
Aurora 에서 데이터 : 1234000000

Case 2)

오라클 ASIS 데이터 : 99999999999
Unload CSV 파일 : 1.0000E+11
Aurora 에서 데이터 : 100000000000

해결 방안1)

SET NUM[WIDTH] {10|n}

SPOOL 파일에 SET NUMWIDTH 38 옵션을 추가, Default 값은 10, 11자리 이상은 지수로 표현함

n은 38까지 설정가능( 2~50 까지 가능하다고 하는데 테스트 해봐야함)

38자리로 설정하면 숫자는 공백을 포함하여 저장되는데 TOBE의 데이터 타입이 숫자형이라서 공백은 제외하고 데이터는 잘 들어간다.    

ex) 123 -> '                             123' -> 123

spool on

SET PAGES 0
SET FEEDBACK OFF
SET HEADING ON
SET ECHO OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET LONG 1000000
SET LONGCHUNKSIZE 100000
SET NUMWIDTH 38

ALTER SESSION SET NLS_DATE_FORMAT='YYYYYMMDDHH24MISS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYYMMDDHH24MISSXFF';

spool [PATH]

[SQL]
;

spool off

ex) numwidth 설정 예제 

SQL> show numwidth 
numwidth 10

SQL> SELECT 1234567891 num1, 12345678912 num2 from dual;

num1        | num2       |
--------------------------
1234567891  | 1.2346E+10 |

SQL> set numwidth 20

num1        | num2        |
---------------------------
1234567891  | 12345678912 |

해결방안2)

SET NUMF[ORMAT] format

NUMFORMAT을 설정한다. NUMFORMAT은 TO_NUMBER 형식 모델과 같은 방식으로 작동한다. 기본값은 ""(설정되지 않음) 이다.

SQL> select 12345 from dual;

12345
------
12345

SQL> set numformat 0000000000
SQL> select 12345 from dual;

     12345
----------
0000012345

SQL> set numformat 99999.00
SQL> select 12345 from dual;

    12345
---------
 12345.00

해결방은 3)

Unload 하기 전에 SQL에 숫자형 데이터에 to_char() 로 문자형으로 형변환한다. 문자형은 지수변환하지 않고 데이터 그대로 저장하고 TOBE에 입력할 때 숫자형으로 변환된다.

하지만, 숫자 컬럼이 많아서 해당 방법은 적용하지 않았다.

결론은 1번 방안으로 선택하였다.

개요. 배치 파일 생성 01.BAT 하여 윈도우에서 쉘스크립트 처럼 실행한다. 여러개의 사용자로 접속하여 SQL을 실행하여야 할때 편리하게 사용할 수 있다

1.BAT 파일

-- .bat 파일
@ECHO OFF 
sqlplus scott/tiger@orcl       @"D:\SPOOL\ASIS\spool_01.SQL" > "D:\SPOOL\ASIS\LOG.TXT"
sqlplus scott/tiger@tobe_orcl  @"D:\SPOOL\TOBE\table_01.SQL" > "D:\SPOOL\TOBE\LOG.TXT"
sqlplus hr/hradm@tobe_orcl     @"D:\SPOOL\TOBE\table_02.SQL" >> "D:\SPOOL\TOBE\LOG.TXT"
EXIT

2. SPOOL.SQL 작성

bat 파일을 사용해서 1, 2번이 차례대로 실행되게 한다.

1. SQL문을 추출할 서버에 접속하여 SELECT LIST절에 필요한 구문을 추출하여 저장(table_01, table_02)

2. 추출한 문장을 실행할 서버에 접속하여 실행하도록 설정

--SPOOL.SQL 
SET ECHO OFF
SET HEADING OFF 
SET LONG 64000
SET LONGCHUNKSIZE 20000
SET PAGESIZE 0 
SET FEEDBACK OFF 
SET VERIFY OFF
SET LINESIZE 1200 
SET TRIMSPOOL ON
--REM 컬럼명 등이 안잘릴려면 TEXT 컬럼사이즈 설정이 중요하다. 
COLUMN TEXT FORMAT A1200

exec dbms_get_metadata.set_transform_param…

SPOOL  table_01.SQL
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') TEXT FROM DUAL;
SPOOL OFF;

SPOOL  table_02.SQL
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','HR') TEXT FROM DUAL;
SPOOL OFF;

exit;

3. Metadata 스크립트 추출 가능한 objects 유형

SELECT DBMS_METADATA.GET_DDL('PROCEDURE','PRO_EXEC_TEST','SCOTT') TEXT FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('PAKAGE','PRO_EXEC_TEST','SCOTT') TEXT FROM DUAL;
#PACKAGE BODY --> PACKAGE_BODY
SELECT DBMS_METADATA.GET_DDL('PAKAGE_BODY','PRO_EXEC_TEST','SCOTT') TEXT FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TRIGGER,'PRO_EXEC_TEST','SCOTT') TEXT FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('VIEW,'PRO_EXEC_TEST','SCOTT') TEXT FROM DUAL;

select to_char(dbms_metadata.get_ddl ('table', table_name, owner))
from dba_tables
where  owner=upper('&1');

Shown below are tables with the various dbms_metadata objects types and filters and you can experiment with the possibilities.

## set_transform_param 으로 session 설정

# PRETTY : 들여쓰기(indent) 및 개행(line feed)를 통해 보다 알아보기 쉽게 표시할 지를 결정.

default값은 true, indent 및 line feed를 통해 예쁘게 표시.

false로 설정하면 DDL이 한 줄로 표시.

# tablespace정보는 storage clause와 독립적으로 존재할 수 없기 때문에, "STORAGE" parameter값이 "false"로 설정되면, tablespace정보는 "TABLESPACE" parameter를 "true"로 설정해도 표시되지 않는다.

# SEGMENT_ATTRIBUTES" parameter값이 "false"로 설정되면, parameter의 설정과 관계없이 tablespace정보도, storage clause도 표시되지 않는다.

# PARTITIONING : If TRUE, include partitioning clauses in the DDL. If FALSE, omit them. Defaults to TRUE.

# LOB_STORAGE : Specifies the storage type to use for LOB segments.

The options are as follows:

SECUREFILE - LOB storage is returned as SECUREFILE

BASICFILE - LOB storage is returned as BASICFILE

DEFAULT - The keyword (SECUREFILE or BASICFILE) is omitted in the LOB STORE AS clause

NO_CHANGE - LOB segments are created with the same storage they had in the source database. This is the default.

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'pretty',true);

--SQL종료 문자 ';' 를 출력함
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'sqlterminator',true);

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'storage',true);

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'tablespace',false);

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'segment_attributes',false);

방법 : Spool 사용하여 두번 실행 하여 스크립트 추출 한다

[스크립트]

--테스트 테이블 만들기
create table emp_1 as select * from scott.emp where 1=2;

--Insert into values 추출을 위한 SQL
SELECT 'select '''||insert_script||') values(''||' ||column_list||' ||'');'' from '||owner||'.'||table_name||';' 
  FROM(SELECT 'insert into '||owner||'.'||table_name||'_1('||listagg(column_name, ', ') WITHIN GROUP(ORDER BY column_id) OVER() insert_script
                                                      /*실제 실행시 _1 제거*/
             ,listagg(CASE WHEN data_type IN ('NUMBER') AND nullable ='N' THEN column_name
 			               WHEN data_type IN ('NUMBER') AND nullable ='Y' THEN 'decode('||column_name||',null, ''to_number(NULL)'','||column_name||')'
                           WHEN data_type IN ('DATE') AND nullable ='Y' THEN 'decode('||column_name||',null, ''to_date(NULL)'','||'''''''''||'||column_name||'||'''''''''||')'
                           WHEN nullable ='Y' THEN 'decode('||column_name||',null, ''to_char(NULL)'','||'''''''''||'||column_name||'||'''''''''||')'
                           ELSE '''''''''||'||column_name||'||'''''''''
                       END, '||'',''||') WITHIN GROUP(ORDER BY column_id) OVER() column_list
             ,owner, table_name
        FROM all_tab_columns
       WHERE owner IN ('SCOTT') /*owner*/
         AND table_name IN ('EMP')) /*table_name*/
 WHERE ROWNUM<=1;

--추출스크립트1
select 'insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values('||EMPNO||','||decode(ENAME,null, 'to_char(NULL)',''''||ENAME||'''')||','||decode(JOB,null, 'to_char(NULL)',''''||JOB||'''')||','||decode(MGR,null, 'to_number(NULL)',MGR)||','||decode(HIREDATE,null, 'to_date(NULL)',''''||HIREDATE||'''')||','||decode(SAL,null, 'to_number(NULL)',SAL)||','||decode(COMM,null, 'to_number(NULL)',COMM)||','||decode(DEPTNO,null, 'to_number(NULL)',DEPTNO) ||');' from SCOTT.EMP;

--테스트 테이블 데이터 삭제
truncate table emp_1;

--추출스크립트2(추출스크립트1 실행 결과)
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7369,'SMITH','CLERK',7902,'80/12/17',800,to_number(NULL),20);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7499,'ALLEN','SALESMAN',7698,'81/02/20',1600,300,30);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7521,'WARD','SALESMAN',7698,'81/02/22',1250,500,30);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7566,'JONES','MANAGER',7839,'81/04/02',2975,to_number(NULL),20);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7654,'MARTIN','SALESMAN',7698,'81/09/28',1250,1400,30);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7698,'BLAKE','MANAGER',7839,'81/05/01',2850,to_number(NULL),30);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7782,'CLARK','MANAGER',7839,'81/06/09',2450,to_number(NULL),10);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7788,'SCOTT','ANALYST',7566,'82/12/09',3000,to_number(NULL),20);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7839,'KING','PRESIDENT',to_number(NULL),'81/11/17',5000,to_number(NULL),10);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7844,'TURNER','SALESMAN',7698,'81/09/08',1500,to_number(NULL),30);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7876,'ADAMS','CLERK',7788,'83/01/12',1100,to_number(NULL),20);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7900,'JAMES','CLERK',7698,'81/12/03',950,to_number(NULL),30);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7902,'FORD','ANALYST',7566,'81/12/03',3000,to_number(NULL),20);
insert into SCOTT.EMP_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(7934,'MILLER','CLERK',7782,'82/01/23',1300,to_number(NULL),10);
commit;

--실행결과 확인
select * from emp_1;