1

I noticed that the view column data type doesn't change when I change the datatype of the underlying table. Are there any extra steps I need to take?

Here is an example:

CREATE TABLE test ( amount NUMBER(10,2) );
CREATE VIEW test_vw AS SELECT * FROM test;

SELECT table_name, column_name, data_type, data_precision, data_scale 
FROM user_tab_columns 
WHERE table_name = 'TEST_VW';

ALTER TABLE test MODIFY ( amount  NUMBER(19,2) );

SELECT table_name, column_name, data_type, data_precision, data_scale 
FROM user_tab_columns 
WHERE table_name = 'TEST_VW';

Both queries return the same result:

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                10          2 
| improve this question | | | | |
3

Dependent objects are invalidated when their parent changes:

select status from user_objects where object_name = 'TEST_VW';
-------    
INVALID

Invalid objects are automatically validated when they are next used:

select * from test_vw;

SELECT table_name, column_name, data_type, data_precision, data_scale 
FROM user_tab_columns 
WHERE table_name = 'TEST_VW';

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                19          2 

Alternatively, you can recompile the object yourself:

alter view test_vw compile;

It is worth noting that no data or precision is lost after modifying the table, but before re-validation:

SELECT table_name, column_name, data_type, data_precision, data_scale 
FROM user_tab_columns 
WHERE table_name = 'TEST_VW';

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                10          2 

insert into test (amount) values (123456789012345);

select status from user_objects where object_name = 'TEST_VW';
-------    
INVALID

select * from test_vw;
---------------
123456789012345

select status from user_objects where object_name = 'TEST_VW';
-------    
VALID
| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.