PL/SQL Constants and Literals in Oracle PL/SQL

In this article, we will discuss PL/SQL constants and literals in Oracle PL/SQL tutorial.

If you missed the previous article of Variables in PL/SQL, then please click here.

let’s start now …

What’s inside:
Introduction to Constant
Declaring a Constant
The PL/SQL Literals
Types of Literals

What is PL/SQL constants in Oracle

A PL/SQL constants hold a value that does not change during the whole execution of the program. The declaration of a constant specifies its name, data type, and value, and allocates storage for it.

Declaring a PL/SQL Constants

vconstant_name CONSTANT datatype := VALUE; 
  • constant_name is that we are declaring.
  • The CONSTANT word is restrained, and it ensures that the value does not change throughout the program.
  • VALUE – The value which must be assigned to a constant when it is declared. You can’t give it later.

PL/SQL constants examples:

    co_payment_term   CONSTANT NUMBER   := 45; -- days 
    co_payment_status CONSTANT BOOLEAN  := FALSE; 
If you try to change the co_payment_term in the execution section, PL/SQL will give an error, for example:
    co_payment_term   CONSTANT NUMBER   := 40; -- days 
    co_payment_status CONSTANT BOOLEAN  := FALSE; 
    co_payment_term := 30; -- error
Here is the error message:
PLS-00363: expression 'CO_PAY_TERM' cannot be used as an assignment target
constant in pl/sql

The PL/SQL Literals

Oracle PL/SQL, a literal is an equivalent as a constant. PL/SQL, literals are case-sensitive. A PL/SQL literal is a value that is not represented by an identifier; it is simply a value.

PL/SQL supports the following kinds of literals −

  1. Numeric Literals
  2. Character Literals
  3. String Literals
  4. BOOLEAN Literals
  5. Date and Time Literals
S.No.Literal Type & Example
1Numeric Literals can be up to 38 digits. It literals can be either positive or negative numbers; also, it can be integers or floats.
e.g. 050 78 -14 0 +32767
6.6667 0.0 -12.0 3.14159 +7800.00
6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3

String Literals are always enclosed by single quotes (‘).

e.g. ‘Hewlett Packard’ ’28-MAY-03
3Character literals are string literals consisting of single characters.
e.g., ‘A’ ‘%’ ‘9’ ‘ ‘ ‘z’ ‘(‘
4BOOLEAN Literals are predetermined constants. The preferences that can be allocated to these data types are: TRUE, FALSE, and NULL.
5Date and Time Literals Date and time are enclosed in single quotes (‘).e.g.DATE ‘1978-12-25’;
When dealing with date/time values, you will want to use the TO_DATE function to convert a literal to a date.
e.g. SELECT TO_DATE(‘2015/04/30’, ‘yyyy/mm/dd’) FROM dual;

FAQs in PL/SQL Constants:

1.The PL/SQL is a completely portable, ………………. transaction processing language.

Answer: The Oracle PL/SQL is a completely portable, high-performance transaction processing language.

2. How many types Literals are present in PL/SQL?

Answer: Five types of Literals present in PL/SQL.

3. ………….is not a PL/SQL unit.

Answer: Table does not a PL/SQL unit.

4. Which operator has the highest precedence among the following −AND, NOT, OR?

Answer: NOT

5. What is a PL/SQL package?

Answer: A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions.

6. Which data type is not allowed in PL/SQL?

Answer: A Collection datatype is not allowed in PL/SQL record.

In this article, we are discussed about the Constant in PL/SQL and also different types of Literals in PL/SQL. It is the basics concept of Oracle PL/SQL Tutorials. In the next article, we will discuss the different operators of PL/SQL.

Thank You 🙂

11 thoughts on “PL/SQL Constants and Literals in Oracle PL/SQL

  1. This blog always help me … It’s all articles are completely informative and interesting …
    I want to say thanks to this blog owner.. for providing this beautiful information to me..

Leave a Reply

Your email address will not be published. Required fields are marked *