Getting Error while creating a view in oracle DB

Question

I am a rookie in sql. I am trying to run the below query

Create view ReturnCode as 
(
select ret_code, to_char(creation_dt,'DD-MON-YYYY HH24:MI:SS')“Created_Date”,current_timestamp,buff.log.* 
from buff.log
 WHERE ret_Code= '90'
 order by creation_dt
);

Getting the error

must name this expression with a column alias

Please help what I am missing here

Answer 1

You must give current_timestamp a column name in your view.

Isolating the error so it is more obvious:

SQL> create view v1 as select current_timestamp from emp;
create view v1 as select current_timestamp from emp
                         *
ERROR at line 1:
ORA-00998: must name this expression with a column alias

(SQL*Plus even shows you exactly WHICH expression must be named - it would do the same on your view definition, if you were using SQL*Plus.)

Add a column name for this expression:

SQL> create view v1 as select current_timestamp as current_ts from emp;

View created.

Answer 2

Use this :

CREATE VIEW ReturnCode
AS
   (SELECT t.ret_code,
           TO_CHAR (t.creation_dt, 'DD-MON-YYYY HH24:MI:SS') Created_Date,
           CURRENT_TIMESTAMP Curr_tmp,
           t.* 
      FROM buff.LOG t
     WHERE t.ret_Code = '90' 
     order by t.creation_dt
);