SPOOLファイル作成用PLSQL

PLSQLのお勉強用

#!/bin/ksh

for i in SERVER
do
 echo "sqlplus";
 sqlplus -s appadm/appadm @a $i  > ${i}.sql
done

SET SERVEROUTPUT ON;
SET FEEDBACK OFF;
SET verify off;
DECLARE
        cursor cursor_a is
                 select * from all_tab_columns where table_name = '&1' order by column_id;
        cursor_b cursor_a%ROWTYPE;
        nom number;
        aaa number;

        -- SETコマンドのためのPROCEDURE
        PROCEDURE echo_set IS
        BEGIN
                dbms_output.put_line('SET HEAD OFF');
                dbms_output.put_line('SET LINE 20000');
                dbms_output.put_line('SET PAGES 0');
                dbms_output.put_line('SET FEEDBACK OFF');
        END;

BEGIN
        --テーブル存在チェック
        SELECT 1 INTO aaa FROM SERVER where rownum <= 1;
        echo_set;


        SELECT count(1) INTO nom from all_tab_columns where table_name = 'SERVER' order by column_id;

                dbms_output.put_line('SELECT');
        for cursor_b in cursor_a loop
                dbms_output.put_line('  ' || cursor_b.COLUMN_NAME || ',');
                IF(cursor_a%ROWCOUNT = nom) THEN
                        dbms_output.put_line('  ' || cursor_b.COLUMN_NAME);
                END IF;
        end loop;
                dbms_output.put_line('FROM');
                dbms_output.put_line('SERVER');

END;
/
exit;