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 🙂

35 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..

  2. Thanks on your marvelous posting! I truly
    enjoyed reading it, you may be a great author.I will ensure that I bookmark your blog and will eventually
    come back sometime soon. I want to encourage you to continue
    your great posts, have a nice afternoon!

  3. Hola! I’ve been reading your weblog for
    some time now and finally got the bravery
    to go ahead and give you a shout out from
    Dallas Texas! Just wanted to tell you keep up the
    excellent work!

  4. What’s Going down i’m new to this, I stumbled upon this I have discovered It absolutely helpful
    and it has helped me out loads. I hope to contribute & assist other customers like
    its aided me. Good job.

  5. I simply could not depart your web site before suggesting that I actually loved the usual information a person supply for your guests?
    Is going to be again regularly to check out new posts

  6. It’s awesome to go to see this website and reading
    the views of all colleagues on the topic of this article, while I am
    also zealous of getting experience.

  7. Whats up very nice site!! Guy .. Beautiful .. Wonderful ..
    I will bookmark your web site and take the feeds also?
    I am glad to find so many useful information here within the post, we’d like work out more techniques on this regard, thanks for sharing.

    . . . . .

  8. Excellent article. Keep posting such kind of info on your
    page. Im really impressed by your site.
    Hi there, You have performed a great job. I’ll definitely digg it and in my opinion recommend to my
    friends. I am confident they will be benefited from this website.

  9. Wonderful blog! I found it while browsing on Yahoo News.
    Do you have any suggestions on how to get listed in Yahoo News?
    I’ve been trying for a while but I never seem to get there!

    Thank you

  10. It’s difficult to find knowledgeable people on this subject, but you seem like you know
    what you’re talking about! Thanks

  11. That is a really good tip especially to those new to the blogosphere.
    Simple but very accurate information… Thank you for sharing this one.

    A must read post!

  12. This is really interesting, You’re a very skilled blogger.

    I have joined your rss feed and look forward to seeking more of your fantastic post.

    Also, I’ve shared your web site in my social networks!

  13. I love what you guys are up too. This sort of clever work and exposure!
    Keep up the fantastic works guys I’ve added you
    guys to blogroll.

  14. I relish, result in I found exactly what I used to be taking a look for.
    You have ended my four day long hunt! God Bless you
    man. Have a great day. Bye

  15. Good day! I could have sworn I’ve visited your blog before but after looking at many of the articles I realized it’s new to me.
    Regardless, I’m definitely happy I stumbled upon it and I’ll be book-marking it and checking back regularly!

  16. Hey! This is my first visit to your blog! We are a team of volunteers and starting a new project in a community in the
    same niche. Your blog provided us useful information to work
    on. You have done a marvellous job!

  17. I have been surfing online more than 3 hours today, yet I never found any interesting article
    like yours. It’s pretty worth enough for me.
    In my opinion, if all webmasters and bloggers made good content as you did, the net will be a
    lot more useful than ever before.

  18. Everything is very open with a precise clarification of the issues.
    It was definitely informative. Your site is very helpful.
    Thanks for sharing!

  19. Nice post. I was checking continuously this blog and I am impressed!
    Extremely helpful information specifically the last part :
    ) I care for such information a lot. I was seeking this certain information for a long time.
    Thank you and best of luck.

  20. I think this is among the most vital info for me.
    And i’m glad reading your article. But wanna remark on some general things,
    The site style is ideal, the articles is really excellent : D.
    Good job, cheers

Leave a Reply

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