2

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?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Danish
  • 189
  • 2
  • 3
  • 14

2 Answers2

4

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));
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • but how i will get the table name for that view? We need base table name first for that view right? – Danish Sep 05 '18 at 16:57
  • 1
    @Danish Please describe what are you trying to achieve. If you want to extend field to store longer strings, then yes, you need to locate base table and alter table instead of view – Lukasz Szozda Sep 05 '18 at 16:59
  • Yes, M increasing the column size. – Danish Sep 05 '18 at 17:00
1

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.

Danish
  • 189
  • 2
  • 3
  • 14