sql11

SET ECHO OFF VERIFY OFF FEEDBACK OFF HEADING OFF
SET SERVEROUTPUT ON

-- Capture flag silently with no prompt
COLUMN p_flag_col NEW_VALUE p_flag
SELECT NVL('&1','') AS p_flag_col FROM dual;

BEGIN
  IF UPPER('&p_flag') = 'Y' THEN
    DBMS_OUTPUT.PUT_LINE('Creating functions...');

    EXECUTE IMMEDIATE '
      CREATE OR REPLACE FUNCTION func1 RETURN VARCHAR2 IS
      BEGIN RETURN ''Func1''; END;';

    EXECUTE IMMEDIATE '
      CREATE OR REPLACE FUNCTION func2 RETURN VARCHAR2 IS
      BEGIN RETURN ''Func2''; END;';

    DBMS_OUTPUT.PUT_LINE('Functions created.');
  END IF;
END;
/

-- Suppress errors if func2 doesn't exist (flag wasn't Y)
WHENEVER SQLERROR CONTINUE
DESC func2
WHENEVER SQLERROR EXIT SQL.SQLCODE