Literals in SQL Statements

Read this Oracle Tutorial Point article to know different types of literals in SQL statement. A Literal and a Constant value are synonyms to atleast one another and refer to a fixed data value. The types of Literals recognized by Oracle are,

• Text Literal
• Integer Literal
• Number Literal
• Interval Literal

Text Literals:
Text Literal specifies a text or character literal. It used to specify values wherever ‘text’ or CHAR appear in

• Expression
• Condition
• SQL Function
• SQL Statement

It should be enclosed in singe quotes. They have properties of both CHAR and VARCHAR2 data types. A text literal will have a maximum length of 4000 Bytes.

Examples:  ‘Employee Information’
                    ‘Managers Specification’
                    N ‘n char Literal’

Using Literal Character String:
A Literal is declared in a SELECT list can be a character, a Number or a Date. A literal isn't a column name or a column alias. A literal is printed for all rows that are retrieved by the SELECT statement. Literal String of Free-Test form is enclosed in the query as per the requirement. Date and character literals must be enclosed within the single quotation marks(' '). Literals improve the readability of the output.

Examples:

1. SQL> SELECT ENAME || '.' ||' Month Salary='|| SAL as SALARIES FROM EMP;

2. SQL> SELECT ' The Designation of ' ENAME || ' is ' ||JOB as DESIGNATION FROM EMP;

3. SQL> SELECT ' The Annual Salary of ' ENAME || ' is ' ||JOB as SAL * 12 as ANNUAL_SALARY FROM EMP;

4. SQL> SELECT DNAME ||' Department is Located at ' || LOC FROM DEPT;

5. SQL> SELECT DNAME ||' Joined the Organization on ' || HIREDATE FROM EMP;

6. SQL> SELECT ENAME ||' Works in Department Number ' || DNO || ' as ' || JOB FROM EMP;

Applying Concatenation Operator:
Concatenation operator link columns to other columns are constant values or arithmetic expressions. Columns on either side of the operator are combined to create a single output column. The resultant column is treated as character expression. The concatenation operator is pictured in Oracle by double pipe symbol (||).

Examples:

1. SQL> SELECT ENO || ENAME || '. Designation is ' || JOB || ' Employee Details ' FROM EMP;

2. SQL> SELECT ' The Basic Salary of ' || ENAME || ' is RS ' || SAL FROM EMP;