Silly and Interesting – III

Querying the column name that is not present in the table gives error:-

ORA-00904: "COLUMN_NAME": invalid identifier

Example:-

20:05:28 SYS @ oracle >desc scott.emp
 Name                       Null?     Type
 -------------------------- --------  --------------
 EMPNO                      NOT NULL  NUMBER(4)
 ENAME                                VARCHAR2(10)
 JOB                                  VARCHAR2(9)
 MGR                                  NUMBER(4)
 HIREDATE                             DATE
 SAL                                  NUMBER(7,2)
 COMM                                 NUMBER(7,2)
 DEPTNO                               NUMBER(2)

20:08:32 SYS @ oracle >select d_ename from scott.emp;
select d_ename from scott.emp
 *
ERROR at line 1:
ORA-00904: "D_ENAME": invalid identifier

Elapsed: 00:00:00.06
20:08:49 SYS @ oracle >select ename from scott.emp;

ENAME
----------
JACK
JILL
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

16 rows selected.

Elapsed: 00:00:00.15
20:08:57 SYS @ oracle >

Then how am i able to get the output of the below query:-

20:15:51 SYS @ oracle >select database_name from v$database;

DATABASE_NAME
---------------------------------------------------------------
ORACLE

Elapsed: 00:00:00.03

No such column name in V_$DATABASE table whose synonym is V$DATABSE.

20:17:35 SYS @ oracle >select name from v_$database;

NAME
---------
ORACLE

Elapsed: 00:00:00.23
20:17:44 SYS @ oracle >select database_name from v_$database;

DATABASE_NAME
------------------------------------------------------------------------------
ORACLE

Elapsed: 00:00:00.04
20:17:49 SYS @ oracle >desc V_$DATABASE
 Name                                                                             Null?     Type
 -------------------------------------------------------------------------------- -------- --------
 DBID                                                                                      NUMBER
 NAME                                                                                      VARCHAR2(9)
 CREATED                                                                                   DATE
 RESETLOGS_CHANGE#                                                                         NUMBER
 RESETLOGS_TIME                                                                            DATE
 PRIOR_RESETLOGS_CHANGE#                                                                   NUMBER
 PRIOR_RESETLOGS_TIME                                                                      DATE
 LOG_MODE                                                                                  VARCHAR2(12)
 CHECKPOINT_CHANGE#                                                                        NUMBER
 ARCHIVE_CHANGE#                                                                           NUMBER
 CONTROLFILE_TYPE                                                                          VARCHAR2(7)
 CONTROLFILE_CREATED                                                                       DATE
 CONTROLFILE_SEQUENCE#                                                                     NUMBER
 CONTROLFILE_CHANGE#                                                                       NUMBER
 CONTROLFILE_TIME                                                                          DATE
 OPEN_RESETLOGS                                                                            VARCHAR2(11)
 VERSION_TIME                                                                              DATE
 OPEN_MODE                                                                                 VARCHAR2(10)
 PROTECTION_MODE                                                                           VARCHAR2(20)
 PROTECTION_LEVEL                                                                          VARCHAR2(20)
 REMOTE_ARCHIVE                                                                            VARCHAR2(8)
 ACTIVATION#                                                                               NUMBER
 SWITCHOVER#                                                                               NUMBER
 DATABASE_ROLE                                                                             VARCHAR2(16)
 ARCHIVELOG_CHANGE#                                                                        NUMBER
 ARCHIVELOG_COMPRESSION                                                                    VARCHAR2(8)
 SWITCHOVER_STATUS                                                                         VARCHAR2(20)
 DATAGUARD_BROKER                                                                          VARCHAR2(8)
 GUARD_STATUS                                                                              VARCHAR2(7)
 SUPPLEMENTAL_LOG_DATA_MIN                                                                 VARCHAR2(8)
 SUPPLEMENTAL_LOG_DATA_PK                                                                  VARCHAR2(3)
 SUPPLEMENTAL_LOG_DATA_UI                                                                  VARCHAR2(3)
 FORCE_LOGGING                                                                             VARCHAR2(3)
 PLATFORM_ID                                                                               NUMBER
 PLATFORM_NAME                                                                             VARCHAR2(101)
 RECOVERY_TARGET_INCARNATION#                                                              NUMBER
 LAST_OPEN_INCARNATION#                                                                    NUMBER
 CURRENT_SCN                                                                               NUMBER
 FLASHBACK_ON                                                                              VARCHAR2(18)
 SUPPLEMENTAL_LOG_DATA_FK                                                                  VARCHAR2(3)
 SUPPLEMENTAL_LOG_DATA_ALL                                                                 VARCHAR2(3)
 DB_UNIQUE_NAME                                                                            VARCHAR2(30)
 STANDBY_BECAME_PRIMARY_SCN                                                                NUMBER
 FS_FAILOVER_STATUS                                                                        VARCHAR2(21)
 FS_FAILOVER_CURRENT_TARGET                                                                VARCHAR2(30)
 FS_FAILOVER_THRESHOLD                                                                     NUMBER
 FS_FAILOVER_OBSERVER_PRESENT                                                              VARCHAR2(7)
 FS_FAILOVER_OBSERVER_HOST                                                                 VARCHAR2(512)

Database version is 10.2.0.4.Not checked in other versions yet.

Advertisements

4 thoughts on “Silly and Interesting – III

  1. because “database_name” is a function defined in the sys schema as:

    function database_name return varchar2 is
    begin
    return dbms_standard.database_name;
    end;

    so select database_name from dual; works if you are connected as sys.

    Interestingly there is a public sysnonym to this called ORA_DATABASE_NAME

    So
    select ORA_DATABASE_NAME from dual
    works from any user

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s