Is there any (possibly Oracle-specific) way to tell in advance if given ALTER TABLE change will succeed and not fail because of too long values
I would say it is not a good design when you need to create/modify database objects on the fly. Having said that, If the DDL fails, an ORA-error will be associated with it. You need to retry with required changes. Modifying a table is not a regular thing, you create a table once and then you would alter it only when there is a business need and you need to go through a release so that the application is not affected. So, I wonder how would it help you to know prior to execution whether the DDL would be successful or not? If your tool is doing these modifications, then your tool should handle it programmatically. Check the type and size of the columns before altering it.
If you are doing it using an external script, then you need to build your own logic. You could use the metadata views like user_tab_columns to check the data_type, data_size, data_precision, data_scale etc.
A small example of the logic to check for the size of a VARCHAR2 data type before issuing an ALTER statement(For demonstration purpose, I am doing this in PL/SQL, you could apply similar logic in your script or tool):
SQL> CREATE TABLE t (A VARCHAR2(10));
Table created.
SQL> DESC t;
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(10)
SQL> SET serveroutput ON
SQL> DECLARE
2 v_type VARCHAR2(20);
3 v_size NUMBER;
4 new_size NUMBER;
5 BEGIN
6 new_size:= 20;
7 SELECT data_type,
8 data_length
9 INTO v_type,
10 v_size
11 FROM user_tab_columns
12 WHERE table_name='T';
13 IF v_type ='VARCHAR2' THEN
14 IF new_size > v_size THEN
15 EXECUTE IMMEDIATE 'ALTER TABLE T MODIFY A '||v_type||'('||new_size||')';
16 DBMS_OUTPUT.PUT_LINE('Table altered successfully');
17 ELSE
18 DBMS_OUTPUT.PUT_LINE('New size should be greater than existing data size');
19 END IF;
20 END IF;
21 END;
22 /
Table altered successfully
PL/SQL procedure successfully completed.
Ok, so the table is successfully altered, lets check:
SQL> DESC t;
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(20)
SQL>
I have seen few applications using groovy script which does all the check and prepares the ALTER statements based on the checks on the data_type, data_size, data_precision, data_scale etc.
For different checks, you need to add more IF-ELSE blocks. It was one example to increase the size of the VARCHAR2 data type. You need to raise exception while decreasing the column size, depending whether the column has any existing data or not...and so on...
You could create separate functions to check the metadata and return a value.
For example,
Numeric types:
CREATE OR REPLACE FUNCTION is_numeric (i_col_name)...
<using the above logic>
IF v_type ='NUMBER' THEN
<do something>
RETURN 1;
Character types:
CREATE OR REPLACE FUNCTION is_string (i_col_name)...
<using the above logic>
IF v_type ='VARCHAR2' THEN
<do something>
RETURN 1;
