PL/SQL - Language fundamentals (identifiers, literal and keyword) and Data Types

In previous post we covered features of PL/SQL, structures of PL/SQL program (Block structure of PL/SQL) and understood the same by writing a sample program. Here we will be covering PL/SQL from programming language aspect.

Identifiers and literals:

Like other programming languages, PL/SQL also imposes certain restriction with name of identifier used to declare any Variable,Object, Exception, Record,Reserved Keyword,etc. Properties of an identifier are summarized below:-

1. It should not be a reserved keyword - Keywords END,IF, LOOP,etc are reserved for PL/SQL
2. It 's maximum length should not exceed 30 characters. 
3. Must start with a letter. -  1Name is invalid identifier,starting with number.
4. It can include $ (dollar sign), _ (underscore), and # (pound sign) but should not start with it. 
5. It cannot contain spaces. However, Identifiers must be separated by at least one space

Note : PL/SQL is a case-insensitive language.-- identifiers <Name, name or NAME> all are same for PL/SQL. However, literals 'Name' and 'name' are different.
A literal is a value which can be of following types: Number , String, Boolean. For example: Number- 234,11.6, or NULL  / String- `nikhil' or `01-Dec-2015' or NULL  / Boolean- TRUE, FALSE, or NULL. A literal does not have a name, though it does have an implied (undeclared) datatype.
Single Quotes Inside a String :-  In order to keep single quotes inside literals as part of literals value  - Put two quotes in place where you want single quote as part of literal value.Following rules are derived form above rule.

• To place a single quote inside the literal, put two single quotes together.
   Example: Actual value required : It''s true , literals will be written as: It''s true
• To place a single quote at the beginning or end of a literal, put three single quotes together.
  Example: literal  = '''Impossible task   =>  'Impossible task'
• To create a string literal consisting of one single quote, put four single quotes together.
  Example: literal ''''  (4 single quotes) => ' (single quotes )
• To create a string literal consisting of two single quotes together, put six single quotes together.

Along with the these identifiers and literals, in PL/SQL various symbols have special meaning and following are list of the symbol and its special meaning :

Symbol	Description
;	Semicolon: statement terminator
%	Percent sign: attribute indicator (cursor attributes like %ISOPEN and indirect declaration attributes like %ROWTYPE). Also used as multibyte wildcard symbol, as in SQL.
_	Single underscore: single-byte wildcard symbol, as in SQL
:	Colon: host variable indicator, such as :block.item in Oracle Forms
**	Double asterisk: exponentiation operator
< > and !=	"Not equals"
||	Double vertical bar: concatenation operator
<< and >>	Label delimiters
<= and >=	Relational operators
:=	Assignment operator
=>	Association operator for positional notation
--	Double dash: single-line comment indicator
/* and */	Beginning and ending multi-line comment block delimiters

There are various keywords reserved for PL/SQL and in coming tutorial posts we will use spot them while writing PL/SQL blocks.Example: RAGMA-  It is used to instruct or pass information to compiler or run time engine.The syntax for using the PRAGMA keyword is as follows:-

PRAGMA <instruction>;
where <instruction> is a statement providing instructions to the compiler.

For Example, pragma EXCEPTION_INIT tells the compiler to associate a particular error number with an identifier.

PL/SQL Datatypes

PL/SQL provides a variety of predefined datatypes, which can be divided into four categories:
1. Scalar data type - A scalar datatype is an atomic, it does not have components. The scalar data types fall into one of four categories: NUMBER, CAHR, BOOLEAN, and DATE TIME.
2. Composite type - A composite datatype has internal structure or components. The two composite types currently supported by PL/SQL are the collections (Associative array, VARRAY, Nested tables) and record . Read Composite data type in PL/SQL
3. Reference types : Reference type in PL/SQL are denoted by cursor, static cursor and cursor variables are like pointers to result sets. Read PL/SQL reference types- Cursors.
4.LOB types(Large object container):- Text blocks or graphic images, that are stored separately from other database data. LOB types include BFILE, BLOB, CLOB, and NCLOB.

Scalar data type

The most commonly used scalar data types are  Number, Char, Varchar2, Varchar, Long ,Boolean and Nvarchar. 
NUMBER datatype:-
When we declare a variable type NUMBER, we can also optionally specify the variable's precision and scale, as follows:- NUMBER (precision, scale)

var_name NUMBER(4,2) ;  -- 4 is precision and 2 is scale.
The precision of a NUMBER is the total number of digits. The scale indicates the number of digits to the right or left of the decimal point at which rounding occurs.

big_whole_number NUMBER;  -- the default precision is 38 and the default scale is 0.

Legal values for the scale range from -84 to 127. Both the precision and scale values must be literal values (and integers at that); you cannot use variables or constants in the declaration.

CHAR datatype:
The CHAR datatype specifies that the character string has a fixed length.When we declare a fixed-length string, we should also specify a maximum length for the string, which can range from 1 to 32767 bytes (it is much higher than that for the CHAR datatype in the Oracle RDBMS, which is only 255). If we do not specify a length for the string, then PL/SQL declares a string of one byte. However in NUMBER, default precision is set to 38. If value assigned to CHAR variable is larger than allocated Maxim size, it throws VALUE_ERROR exception.

Notes: Prior to Oracle7, the CHAR datatype was variable-length and Oracle did not support a fixed-length character string datatype. However, to improve compatibility with IBM relational databases and to comply with ANSI standards,Oracle7 reintroduced CHAR as a fixed-length datatype and offered VARCHAR2 as the variable-length datatype. It is advised to avoid use of CHAR,if possible.

Question: If  PL/SQL CHAR type is declared of size 1000, however Oracle DBMS can accommodate only 255 bytes, how does PL/SQL data can be stored in oracle DBMS ? Or
How PL/SQL char type can be inserted in Database char?
Answer: Using SUBSTR function we can get 255 chars and store in Database. Lets say V_char_large is PL/SQL char identifier, SUBSTR can be written as : SUBSTR (V_char_large, 1, 255))  --  returns char form index 1 to 255.

VARCHAR2 and VARCHAR datatypes:-
VARCHAR2 and VARCHAR data types is used to store variable length strings as contrast to CHAR type which is used to store fixed length string.When we declare a variable-length string, we must also specify a maximum length for the string, which can range from 1 to 32767 bytes, however VARCHAR2 datatype in the Oracle RDBMS is only 2000. Same as discussed above, here also we need to use SUBSTR if we want to store PL/SQL type in Database.

VARCHAR is also used for storing variable length strings and synonymous with VARCHAR2 with the same range (1 to 32767) of values storage capacity. Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage. The fundamental difference between them is : VARCHAR2 is Oracle specific data type (NULL and empty string is same), however VARCHAR is ASCII compliant(NULL and empty string are different) and IBM database compatible. Read in detail: What is difference between CHAR, VARCHAR, VARCHAR2.

Notes: CHAR data type size is different from VARCHAR, VARCHA2 in PL/SQL and at Database level . At database level : CHAR - max 255 bytes , VARCHAR2 -  2000 bytes and in PL/SQL context- CHAR - max 32767 bytes and VARCHAR2 -  32767 bytes. So, when we deal with CHAR and VARCHAR in our PL/SQL code and data is inserted or fetched from database, we should be careful and should have sound understanding of interactions between these two data types. Read Interaction between Fixed-length (CHAR) and variable-length (VARCHAR2) strings in PL/SQL.

LONG datatype:-
In PL/SQL a variable declared LONG can store variable-length strings of up to 32760 bytes as contrast to Oracle database server can store two gigabytes. Long in oracle database is used to store  multimedia information - graphic images. In PL/SQL we can use LONG data type without any restriction as contrast to restriction imposed on LONG data type in oracle database. It is recommended to use always VARCHAR2 instead of LONG for variable length storage, LONG is most relevant for being used at database level, storing images another multimedia details.   

Boolean Datatype:-   

PL/SQL support a logical data type - Boolean, which is unavailable in native SQL. Boolean data can have following values: TRUE, FALSE, or NULL.Oracle database does not have Boolean datatype.

Question: How does Oracle database handle deal with Boolean requirement -TRUE and FALSE ?
Answer: Oracle table creates a column of datatype CHAR(1) and store either "Y" or "N" in that column to indicate TRUE or FALSE. 

Boolean datatype is extensively used in PL/SQL, since PL/SQL support conditional construct to make decision regarding the of flow execution of PL/SQL program. 

Date-Time Datatype:-

 PL/SQL provides a DATE datatype which corresponds directly to the RDBMS DATE.
Oracle database stores the date in a standard, internal format. - a fixed-length value which uses seven bytes. It stores  both date and time implicitly and we cannot modify internal or literal value with an assignment. PL/SQL allows to leave off time part , if we just want to store  date value. 

1. PL/SQL DATE datatype(So as Oracle database) does not store sub-seconds information(minimum time value in second). PL/SQL uses GET_TIME function of DBMS_UTILITY package to obtain sub-seconds value.

2. Arithmetic on date variables can be performed - subtraction of one date from another or  addition/subtraction of numbers from a date.For more details refer this.

3. SYSDATE function return the current system date and time.
4. Date to character representation using TO_CHAR function.

NCHAR, NVARCHAR and NVARCHAR datatype :-
When we deal with Chinese and  Japanese languages, all characters in these languages cannot be represented by ASCII (It is represented by 8 bits). In higher languages like Java, Unicode 16 bit notation is used to represent all possible characters. On the same line, oracle supports National Language Support (NLS) and offer two types of character sets :
1. Database character set- used to represent identifiers and source code.
2. National character set: - used for NLS data.
PL/SQL offers two data types, NCHAR and NVARCHAR2, to store character strings formed from the national character set. NCHAR datatype is used to store fixed-length nls character data. The maximum length for NCHAR variables is 32767.  VVARCHAR2 datatype is used to store variable-length nls character data.The maximum length allowed for nvarchar2 variables is 32767.

For other scalar data types supported in PL/SQL refer O'Reilly' PL/SQL programming.In next post we will see the variables declaration, constants and naming convention used for PL/SQL program unit.Other data types like composite type, reference type has been discussed in separate post. Refer below URL:
PL/SQL Reference types(Explicit and Implicit cursor, Cursor variable):- 

1. PL/SQL Cursor - Explicit and Implicit cursor
2. PL/SQL Cursor variable

PL/SQL Composite data type (Associative array, VARRAY and Nested Tables & Record)

1. PL/SQL Collections (Associative array, VARRAY and Nested Tables )
2. PL/SQL Record

Previous: PL/SQL Introduction  Next: PL/SQL - Declaration and Operators