How to change a column data type in Oracle View? [duplicate]

Question

I am trying to change a data type of a column in a Oracle View by executing the following statement:

ALTER VIEW <view_name> 
MODIFY (ID VARCHAR2(100));

I get the following error:

Error starting at line : 1 in command -
ALTER VIEW <view_name>
MODIFY (ID VARCHAR2(100))
Error report -
ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:

I referred to the post here. What's the correct way to achieve this? I guess Oracle expects CONSTRAINT keyword after MODIFY. The column I am modifying the data type of is one of the primary keys in the table on which this view stands.

Answer 1

You can not change the column data type of the VIEW.

Instead, you can change the data type of underlying base table's column (In that case refer the answer of the link mentioned in your question) or you can create a view query with that data type.

-- Your view
CREATE OR REPLACE VIEW <YOUR VIEW> AS
SELECT ID, 
...
FROM <TABLE>;

You can change the SELECT query of the view.

CREATE OR REPLACE VIEW <YOUR VIEW> AS
SELECT cast(ID as varchar2(20)) as ID, 
...
FROM <TABLE>;

Hope, this will help.

Cheers!!