oracle - Is there a way to test if modifying table column will succeed in SQL? -

i have tool applies lot of changes database. many changes concern modifying column types, sizes, etc. there (possibly oracle-specific) way tell in advance if given alter table change succeed , not fail because of long values, functional indices , on?

with non-ddl modifications simple: start transaction, execute changes , rollback. answer known whether exception or not. however, ddl modifications cannot part of transactions, cannot follow same procedure here.

is there (possibly oracle-specific) way tell in advance if given alter table change succeed , not fail because of long values

i not design when need create/modify database objects on fly. having said that, if ddl fails, ora-error associated it. need retry required changes. modifying table not regular thing, create table once , alter when there business need , need go through release application not affected. so, wonder how know prior execution whether ddl successful or not? if tool doing these modifications, tool should handle programmatically. check type , size of columns before altering it.

if doing using external script, need build own logic. use metadata views user_tab_columns check data_type, data_size, data_precision, data_scale etc.

a small example of logic check size of varchar2 data type before issuing alter statement(for demonstration purpose, doing in pl/sql, apply similar logic in script or tool):

sql> create table t (a varchar2(10));  table created.  sql> desc t;  name                                      null?    type  ----------------------------------------- -------- ----------------------------                                                   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    v_type,  10      v_size  11    user_tab_columns  12    table_name='t';  13    if v_type       ='varchar2'  14      if new_size   > v_size  15        execute immediate 'alter table t modify '||v_type||'('||new_size||')';  16        dbms_output.put_line('table altered successfully');  17      else  18        dbms_output.put_line('new size should greater existing data size');  19      end if;  20    end if;  21  end;  22  / table altered  pl/sql procedure completed. 

ok, table altered, lets check:

sql> desc t;  name                                      null?    type  ----------------------------------------- -------- ----------------------------                                                   varchar2(20)  sql> 

i have seen few applications using groovy script check , prepares alter statements based on checks on data_type, data_size, data_precision, data_scale etc.

for different checks, need add more if-else blocks. 1 example increase size of varchar2 data type. need raise exception while decreasing column size, depending whether column has existing data or not...and on...

you create separate functions check metadata , return value.

for example,

numeric types:

create or replace function is_numeric (i_col_name)... <using above logic> if v_type       ='number' <do something> return 1; 

character types:

create or replace function is_string (i_col_name)... <using above logic> if v_type       ='varchar2' <do something> return 1;