How to create dummy NULL column for a Oracle view using function?


The Eucharistic Miracles of the World
Normally we cannot create a view with a dummy null column. The column defined in the view must be present in the base table. If the column is not present in the base tables Oracle will throw ORA-00904: "CARNAME": invalid identifier error. Here I am going to present a demo for creating a view with Null column using a function.

I have two tables dept and emp1. I wanted to create a view with name, department name, location, and a dummy column carname.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from emp1;

NAME                        AGE LAST_NAME                  DEPT
-------------------- ---------- -------------------- ----------
Toad                         52                              10
John                         32                              20

Trying to create a view without base column and it is erroring out.
SQL> create or replace view emp_dept as
  2  select Name, Dname, Loc, CarName
  3  from  emp1, dept
  4  where  emp1.dept=dept.deptno;
select Name, Dname, Loc, CarName
ERROR at line 2:
ORA-00904: "CARNAME": invalid identifier

Create a function carname as the name with return value as null.
SQL> create or replace function carname return varchar2 is
  2  begin
  3     return null;
  4  end;
  5  /

Function created.

  1   create or replace view emp_dept as
  2    select Name, Dname, Loc, CarName
  3    from  emp1, dept
  4*   where  emp1.dept=dept.deptno
SQL> /

View created.

  1* select * from emp_dept
SQL> /

NAME                 DNAME          LOC           CARNAME
-------------------- -------------- ------------- ----------
Toad                 ACCOUNTING     NEW YORK
John                 RESEARCH       DALLAS


Website Stats


Post a Comment


Oracle (580) General (60) Unix (47) Script (41) Blog (23) OCI (3) SQL* Loader (3) Datapump (2) Software (1)
DBA Tips Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer