• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » PLSQL Constants

PLSQL Constants

September 10, 2022 by techgoeasy Leave a Comment

In this post we will discuss about PLSQL Constants in Oracle, How do you declare a constant variable in PL SQL, how to Declare a constant in Oracle stored procedure

Table of Contents

  • Oracle PLSQL Constant
  • How do you declare a constant variable in PL SQL
  • General Syntax
  • Example of declaring Constants:

Oracle PLSQL Constant

  • Oracle PLSQL constant is just like any other procedural language and Its value does not change during the execution of the program
  • Constants must be declared for use in procedural and SQL code, although the datatypes available in SQL are only a subset of those available in PL/SQL.

How do you declare a constant variable in PL SQL

  • All constants must be declared before they are referenced.
  • Declaration involves the name of the constant followed by its data type.
  • Remember that each constant must be declared on a separate line and multiple constants cannot be defined in the same statement.
  • All statements must end with a semicolon
  • The declarations of constant definitions must contain the CONSTANT keyword and must be assigned a value as part of the definition. Subsequent attempts to assign a value to a constant will result in an error

General Syntax

variable_name CONSTANT datatype [NOT NULL := value ];

(1) variable_name is the name of the variable.

(2) datatype is a valid PL/SQL datatype.

PLSQL Constants

Example of declaring Constants:

v_global constant number := 200; 
V_DEPT  CONSTANT NUMBER (6) DEFAULT 160;
  • Constants cannot be changed.
  • You must initialize constants at the time of declaration.

Example as full Plsql Block

DECLARE
c_limit CONSTANT REAL := 1000.00; -- SQL data type
max_days_in_year CONSTANT INTEGER := 266; -- SQL data type
u_legend CONSTANT BOOLEAN := TRUE; -- PL/SQL-only data type
BEGIN
NULL;
END;
/

Let’s try to assign some value to the constant variable

DECLARE
c_limit CONSTANT REAL := 1000.00; -- SQL data type
max_days_in_year CONSTANT INTEGER := 266; -- SQL data type
u_legend CONSTANT BOOLEAN := TRUE; -- PL/SQL-only data type
BEGIN
c_limit :=1;
END;
/
PLS-00363: expression 'c_limit' cannot be used as an assignment target

So it has received an error if we assign any value to the constant in the begin block

PLSQL Constants are an important construct and are quite used in PLSQL Programming. I hope you this post on PLSQL Constants in Oracle, How do you declare a constant variable in PL SQL, how to Declare a constant in Oracle stored procedure

Related Articles
Oracle PLSQL Variable: Check out this post on Oracle PLSQL Variable, how to declare variable in stored procedure oracle
Oracle PLSQL records: Check out this article on the working of oracle plsql records. Also, find out the various ways to define it and assign value to it
PLSQL Tables: Check out this post for a detailed description of PLSQL Tables.How to manipulate it and work on it in Oracle PLSQL block and benefits
What is cursor in oracle : Check out this post to get a great overview of What is cursor in oracle.Details about Cursor parameter, cursor loop, cursor for an update

Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us