CODEWITHSUNDEEP
sqloracle-databaseviewaltercolumn-sizing
Danish
Danish

Reputation: 199

How to alter column size of a view in Oracle

I am trying to alter the column size of a view with the same command that we use for table like :

alter table 
STUDENT
modify (
    ROLL_NO VARCHAR2(80)
);

But its throwing error

SQL Error: ORA-00942: table or view does not exist

So how we can alter the column size of a view?

Upvotes: 2

Views: 11925

Answers (2)

Danish
Danish

Reputation: 199

Here is the procedure that I followed :

1- First find the base table for that view by running the following query

SELECT * FROM DBA_DEPENDENCIES
WHERE OWNER = '<scheman_name>'
AND NAME = '<view_name>'
AND TYPE = 'VIEW';

2- Above query will you a table where you will find the base table under the column name 'REFERENCED_NAME'.

3- Now Change the column size of that base table.

NOTE: The view can be made up of 1 or more than 1 tables, so you need to change the column size of all those base tables.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

A view is simply saved query and "inherits" column type from underlying base table. So if you need to change metadata you should alter view definition:

ALTER VIEW view_students
AS
SELECT CAST(roll_no AS VARCHAR2(80)) AS roll_no,
     ...
FROM tab_students;

If you want to change data type to store longer strings, then you need to locate base table and alter it instead:

ALTER VIEW tab_students
MODIFY (ROLL_NO VARCHAR2(80));

Upvotes: 5

Related Questions