SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Oracle Database Administration and PL/SQL Programming Tutorials for Data Professionals

PL/SQL Double Quotes for Space in Table Name in Oracle Database

PL/SQL developers can use space in table name in Oracle database using "double quotes"
But using double quotes for space in table name or other Oracle database objects' names is not a best practice according to PL/SQL naming standards or naming conventions.
Besides, even if as a PL/SQL developer or an Oracle DBA, you use space in a table name, you must be careful against case sensitivity during PL/SQL code execution on Oracle database.

Let's create an Oracle database table using following Create Table PL/SQL code.
Note that the table name Customer Info has a space character in it. The table name is formed of two words seperated by a space and identified using double quotes in PL/SQL code.

create table "Customer Info" (
 "Customer No" Int,
 "Customer Name" varchar(100)

As seen in the above PL/SQL Create Table command, it is possible to give names with spaces to Oracle tables. Database administrators can also create table fields whose names contain space in it using double quote in PL/SQL code.

The negative effect of using double quotes is case sensitivity in PL/SQL code execution. To demonstrate the problem, let's run the following two PL/SQL Select commands one by one.

select * from "Customer Info"
select * from "customer info"

While the first SELECT statement works successfully, the second PL/SQL Select statement will fail. Since we used double quotes for space in table name, the table name is case sensitive. The table name in Select statement must exactly match the name used while creating the Oracle database table.
Otherwise the following Oracle exception will occur and PL/SQL developer will get the ORA-00942 error.

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

As last note, if it is not a requirement it is better to use underscore character "_" or write words without leaving space or blank character between them while naming Oracle objects like database tables or table field names.

Oracle Database

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.