Let's have a cup of coffee.

방법 : 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;