Add a datafile without errors

(via sqlplus) /* celte.sql
|| mauro rossolato, 2004
||
|| ----------------------------------------------------------------
|| 29.11.2004 mauro creates
*/

WHENEVER SQLERROR EXIT
SET SERVEROUT ON SIZE 1000000
SET ECHO OFF
SET HEADING OFF
SET FEEDBACK OFF
SET TERMOUT ON
SET VERIFY OFF
COL file_name FORMAT A50

SPOOL celte.log

SELECT
    tablespace_name
FROM
    dba_tablespaces
/
  
ACCEPT tblspc PROMPT 'which tablespace to expand: '

SELECT
    file_name, bytes/1024/1024
FROM
    dba_data_files
WHERE
    tablespace_name = UPPER('&tblspc')
/
  
ACCEPT nomefile PROMPT 'datafile name  (with estension): '
ACCEPT dimensione PROMPT 'dimension (in MB): '

VARIABLE s_mystmt VARCHAR2(132);
VARIABLE i_isdba NUMBER;

DECLARE
    i_counte NUMBER;
BEGIN
    SELECT
        count(*) INTO i_counte
    FROM
        dba_data_files
    WHERE
        file_name = UPPER('&nomefile');
    IF i_counte > 0
    THEN
        DBMS_OUTPUT.PUT_LINE ('the datafile ' || UPPER('&nomefile') || ' already exists!');
        SELECT 1/0 INTO i_counte FROM DUAL;
    ELSE
        IF TO_NUMBER(&dimensione) > 4096
        THEN
            DBMS_OUTPUT.PUT_LINE ('I cannot create (fatti miei) datafile greater than ' ||
                TO_CHAR(TO_NUMBER(&dimensione) * 1024 * 1024) || ' bytes!');
        ELSE
            :s_mystmt := 'ALTER TABLESPACE &tblspc ADD DATAFILE ''&nomefile'' SIZE &dimensione'||'M';
            SELECT
                COUNT(*) INTO :i_isdba
            FROM
                SYS.DBA_ROLE_PRIVS
            WHERE
                GRANTEE=(SELECT USER FROM DUAL)
            AND
                GRANTED_ROLE='DBA';
            IF :i_isdba = 0
            THEN
                DBMS_OUTPUT.PUT_LINE ('-- You''re not currently a dba: run as sys/system.');
            ELSE
                DBMS_OUTPUT.PUT_LINE ('-- You can copy, paste and run!');
            END IF;
        end if;
    END IF;      
END;
/


ACCEPT runnable PROMPT 'would you execute? (y/n): '

DECLARE
    s_sqldyn VARCHAR2(132);
BEGIN
    IF UPPER( '&runnable' ) = 'Y' AND :i_isdba > 0
    THEN
        DBMS_OUTPUT.PUT_LINE (:s_mystmt);
        SELECT
            REPLACE (:s_mystmt,chr(39),chr(39)) INTO s_sqldyn
        FROM
            DUAL;
        DBMS_OUTPUT.PUT_LINE (s_sqldyn);
        EXECUTE IMMEDIATE s_sqldyn;
    END IF;
END;
/

SELECT
    file_name, bytes/1024/1024
FROM
    dba_data_files
WHERE
    tablespace_name = UPPER('&tblspc')
/

SPOOL OFF