SET SERVEROUTPUT ON;
SET TRIMSPOOL ON;
SET LINESIZE 32767
SET FEEDBACK OFF;
SPOOL result.sql;
DECLARE
   vColumns  VARCHAR2(32767) := '';
   vTempCol  VARCHAR2(256)   := '';
   vSorts    VARCHAR2(32767) := '';
   --ファイル情報
   CURSOR vCur IS
         SELECT
              T1.TABLE_NAME AS TABLE_ID
         FROM
              USER_TABLES T1
         ORDER BY
              T1.TABLE_NAME;
   vRec vCur%ROWTYPE;
--
   --テーブル情報
   CURSOR vCur2(pI_Table VARCHAR2) IS
         SELECT
              *
         FROM
              USER_TAB_COLUMNS T1
         WHERE
              T1.TABLE_NAME = pI_Table
         ORDER BY
              T1.COLUMN_ID;
   vRec2 vCur2%ROWTYPE;
--
BEGIN
   -- 共通パラメータ出力
   DBMS_OUTPUT.PUT_LINE('SET WRAP OFF');
   DBMS_OUTPUT.PUT_LINE('SET COLSEP ''	''');
   DBMS_OUTPUT.PUT_LINE('SET TRIMSPOOL ON');
   DBMS_OUTPUT.PUT_LINE('SET TRIMOUT ON');
   DBMS_OUTPUT.PUT_LINE('SET LINESIZE 32767');
   DBMS_OUTPUT.PUT_LINE('SET PAGESIZE 0');
   DBMS_OUTPUT.PUT_LINE('SET FEEDBACK OFF');
   DBMS_OUTPUT.PUT_LINE('SET SERVEROUTPUT ON');
--
   FOR vRec IN vCur
   LOOP
      FOR vRec2 IN vCur2(vRec.TABLE_ID)
      LOOP--
          -- 動的SQL組み立て
          IF vRec2.DATA_TYPE IN ('CHAR','VARCHAR2') THEN
             vTempCol := vRec2.COLUMN_NAME;
          ELSE
             vTempCol :=  'TO_CHAR('||vRec2.COLUMN_NAME||')';
          END IF;
          
          IF (vRec2.COLUMN_ID = '1') THEN
             vColumns := vTempCol;
             vSorts := vRec2.COLUMN_NAME;
          ELSE
             vColumns := vColumns ||'||'',''||'||vTempCol;
             vSorts := vSorts || ',' || vRec2.COLUMN_NAME;
          END IF;
      END LOOP;
      --各テーブルの出力SQL
      DBMS_OUTPUT.PUT_LINE('spool '||vRec.TABLE_ID|| '.csv');
      DBMS_OUTPUT.PUT_LINE('EXEC DBMS_OUTPUT.PUT_LINE( '''||vSorts ||''');');
      DBMS_OUTPUT.PUT_LINE('SELECT '||vColumns ||' FROM ' || vRec.TABLE_ID ||' ORDER BY '|| vSorts ||';');
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('spool off');
   END LOOP;
END;
/
SPOOL OFF;