REM
/*
 * DefaultParams.Sql (c) Tim Onions 2008
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */

store set defparamsqlplussettings.tmp replace
SET SERVEROUT on FEEDBACK off TERMOUT off ECHO off VER off

SPOOL defparams1.tmp
DECLARE
 L_PCount PLS_INTEGER:=&&NumberofParameters;
 L_SQL    VARCHAR2(128):='SELECT NULL ';
BEGIN
   FOR i IN 1..L_PCount LOOP
      DBMS_OUTPUT.PUT_LINE('COLUMN '||i||' NEW_VALUE '||i);
   END LOOP;
   FOR i IN 1..L_PCount LOOP
      DBMS_OUTPUT.PUT(L_SQL||'"'||i||'"');L_SQL:=',NULL ';
   END LOOP;
   DBMS_OUTPUT.PUT_LINE(' FROM dual WHERE rownum=0;');
END;
/
SPOOL off
@@defparams1.tmp
!rm defparams1.tmp

SPOOL defparams2.tmp
DECLARE
 L_PCount PLS_INTEGER:=&&NumberofParameters;
BEGIN
   FOR i IN 1..L_PCount LOOP
      DBMS_OUTPUT.PUT_LINE('UNDEFINE '||i);
   END LOOP;
END;
/
SPOOL off
@defparamsqlplussettings.tmp
!rm defparamsqlplussettings.tmp
REM