Create a Case Sensitive Table in Oracle / Table Name in Lower Case

advertisements

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
In the same oracle schema you can create 2 tables with same name one is in small case and second one with upper case. By default all the DML, DDL command applicable to the table name with upper case. If you wanted to access the table with lower case you have to specify the table name with double quote (“)

See some examples here.

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE 

SQL> create table "dept" as select * from dept; 

Table created. 

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
dept                           TABLE

10 rows selected.

SQL> select count(*) from dept; 

  COUNT(*)
----------
         4
SQL> delete from "dept" where rownum<2 span="">

1 row deleted.

SQL> select count(*) from dept;

  COUNT(*)
----------
         4

SQL> select count(*) from "dept";

  COUNT(*)
----------
         3

_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)
 

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer