Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

 Oracle PLSQL Data Types :

PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types.

PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types.

Pre-defined data types are categorized into the four types:

Composite

LOB

Reference

Scalar

Scalar Datatypes:

It stores the values without the internal components. It holds the single values such as the number or the character string. It can have sub-types which is subset of another datatype which is its base type. Sub-type has the valid operation. Datatype and its subtype comprise the data type families.

It is categorized into four types:

• Boolean
• Character, Strings and National Character types: It is categorized into below types:

CHAR
CHARACTER
LONG
LONG RAW
NVAR
NVARCHAR2
RAW
ROWID
STRING
UROWID
VARCHAR
VARCHAR2

• Date, time &Interval: It is categorized into below types:

DATE
TIMESTAMP:

It extends the date which will be going to store the year,month,day,hour,minute and second.

SYNTAX:

 TIMESTAMP[(precision)]

Default timestamp format is set by the oracle initialization parameter. Integer literal is not used in the range 0 to 9. Default is 6. Default timestamp format is set by the NLS_TIMESTAMP_FORMAT.Symbolic constant or variable is not used to specify the precision.

2025-01-06 07:48:53:275

In the above, optional parameter of timestamp is the number of digits in the fractional part of the second field is 275.

TIMESTAMP with TIMEZONE:

It extends TIMESTAMP and includes the time zone displacement. Time zone displacement is the difference between local time and coordinated local time called as Greenwich Mean time. Default timestamp with timezone format is set by the NLS_TIMESTAMP_TZ_FORMAT

Syntax:

TIMESTAMP[(precision)] WITH TIME ZONE

2025-01-06 08:00:00:00 -08:00

TIMESTAMP with LOCAL TIMEZONE:

It extends TIMESTAMP and includes the local time zone displacement. Time zone displacement is the difference between local time and coordinated local time called as Greenwich Mean time.

Syntax:

TIMESTAMP[(precision)] WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH:

It is used to store and manipulate the intervals of year and months.

Syntax:

INTERVAL YEAR[(precision)] to MONTH

Here, precision is the number of digits in the year field. Symbolic constant or variable is not used to specify the precision. Integer literals are used in the range 0 to 4. Default is 2.

lifetime_period=INTERVAL YEAR(2) TO MONTH;

lifetime_period= INTERVAL '50-2' YEAR TO MONTH;

INTERVAL DAY TO SECOND:

It is used to store and manipulate the intervals of day,hour,minutes and seconds.

Syntax:

INTERVAL  DAY[(leading_precision)] TO SECOND[(fractional_seconds_precision)]

leading_precision is the number of digits in the day field.

fractional_seconds_precision is the number of digits in the second field. Symbolic constant or variable is not used to specify the precision. Integer literals are in the range 0 to 9. Default is 2 & 6 respectively.

lag_time INTERVAL DAY(2) TO SECOND(3);

• Number

Character:

Another name for the character is CHAR.

It is used to store the alpha-numeric data such as word and text which manipulate the character strings.

It is used to store the alpha-numeric character in the string format. Literal values are used in the single inverted commas or single quotes while assigning or using for the character data type. It is classified into below mention types:

Char datatype: Fixed string size.

Varchar datatype: Variable string size.

It is another name for varchar2.

Varchar2 datatype: Variable string size.

It is used to store variable length character data.

varchar2(maximum_size[char byte]

small varchar2 variables are used for performance and large varchar2 variables are optimized for efficient memory use. If you specify the maximum size of the variable values in bytes, it is too short to hold the n multibyte characters. Latest release of plsql with emerging sql standards to meet, varchar2 is used instead of varchar.

Varchar2 subtypes

String and varchar are the subtypes of varchar2.Subtypes are having the same range values as just like the varchar2.Subtypes are compatible with ANSI/ISO and IBM standards.

Long: Large text

It is used to store the variable length character strings. It is like the VARCHAR2.It can store the short documents. Maximum size of LONG value is 32760 bytes. LOB is used instead of it.Maximum width of long column is 2**31.Long column can be referenced in the select, update and insert statements but it is not referenced in sql function calls and clauses like where, group by and connect by. It is not used in the expression. In the sql statements,PLSQL bind the long values as VARCHAR2 not as long.

Long Raw: Large raw data.

It is used to store the binary data or byte strings. It is like the LONG.It is not used starting from 9i version. Maximum size of LONG Raw value is 32760 bytes. BLOB is used instead of it.Maximum width of long raw column is 2**31.

Nchar:Native fixed string size.

It is used to store fixed length, and blank padding is necessary like CHAR.Due to multibyte characters, it can hold any Unicode character data. Default size is 1 byte, if you don't specify the size. You can do interchange between CHAR & NCHAR.You can turn the CHAR value into NCHAR is safer, but you can turn the NCHAR value into CHAR value which leads to the data loss, if the character set for the CHAR value cannot represent all the characters in the NCHAR value.

Nvarchar2: Native variable string size.

It is used to store variable length Unicode character data. Due to multibyte characters, it can hold any Unicode character data. You can do interchange between Varchar2 and Nvarchar2.You can turn the VARCHAR2 value into NVARCHAR2 is safer, but you can turn the NVARCHAR2 value into VARCHAR2 value which leads to the data loss, if the character set for the VARCHAR2 value cannot represent all the characters in the NVARCHAR2 value.

ROWID:It is physical row identifier which is the address of the row in an ordinary table. Every record in the database has physical address or ROWID.Every database has rowid pseudocolumns internally which stores the binary values which is called as ROWID.Each rowid represents the storage address of the row. Logical rowid identifies a row in an index organized table. It can store the only physical rowids.It is used for the backward compatibility for the old applications.

  

Built in function ROWIDTOCHAR is used to convert the binary value into 18-byte character string, when rowid is selected or fetched into rowid variable by you. Function CHARTOROWID is used to convert rowid character string into rowid.If the conversion fails because the character string does not represent the valid row id.predefined exception SYS_INVALID_ROWID is raised.Here,implict conversion takes place.

UROWID:It is universal row identifier which is physical, logical and foreign row identifier. It is used in the new applications. In order to convert UROWID and character string, regular assignment statements is used without any function call.

Physical rowids:

It provides the fast access to the particular and required rows. Physical rowids does not change as long the row exists. It is having two formats i.e.

10 byte extended row id format: It can identify the rows in the partitioned and non-partitioned tables. It supports tablespace and relative block addresses.

6-byte restricted row id format: It is provided for backward compatibility.

It uses the base 64 encoding of the physical address for each row selected. It implicitly converts the rowids into the character strings.

select rowid,ename from emp where empno='7369';

The format AAAS/4AABAAAIyJAAA has four parts:

AAAS/4: It is called as the data object number which identifies the database segment. Data object number is same in the same segment of the schema objects such as cluster of the tables.

AAB: It is said as the file number which identifies the data file that contains the row. It is unique in the database.

AAAIyJ:It is called as the block number which identifies the data block which contain the rows. It is relative to its data files but not their tablespaces. Two rows having the same tablespace, but different data files can have the same block number.

AAA :It is called as the row number which identifies the row in the block.

Logical Rowids:

It is used to provide the fastest access to the rows. It is used to construct secondary index on index-organized tables. It has no permanent physical address. Due to which, it is moved across the data blocks when the new rows are inserted.DBMS_ROWIDS is the supplied package for doing manipulation on the rowids.If the physical location of the rows are changed then also its logical rowids remains valid. It includes guess for identifying the data block of the row. When the new rows are inserted, guesses become stale or slow down to search the block of the row. In order to overcome this problem, new guesses are required which can be accomplished by rebuilding the secondary index.

ROWID pseudo column is used to select the logical row ids which are opaque values from an index organized tables. Logical rowids are inserted into the column of urowid having maximum size of 4000 bytes. ANALYZE statement is used to track the staleness of the guesses. It is useful for an application which are storing rowid with guesses in urowid column,rowid are used to fetch the rows. 

RAW: It is used to store the binary data or byte strings. It is used to store the sequence of graphics characters or digitized picture. It is like VARCHAR2.PLSQL does not interpret the raw data. Character set conversions is not done, when you transmit raw data from one system to another system. Maximum of RAW columns is 2000 bytes.

Data type	Description	Syntax and Explanation
CHAR	It stores the string value, and the size of the string is fixed at the time of declaring variable value. It takes the alphanumeric values which represent single character or string of characters. Oracle will be going to support the blank padding if the variable will not occupy the entire space which has been declared. Hence, oracle will occupy the entire space memory even though the entire space has not been occupied by it. Minimum size is 1 byte, and maximum size is 2000 bytes. It is favorable to use whenever the fixed size of the data will be handled. If the variable value assigned is greater than the char size ,Pre defined exception is raised VALUE_ERROR.	Name CHAR; Position char(10):=’Manager’ Explanation: The first declaration statement declared the variable NAME of the CHAR datatype is having the maximum 1 byte in size which is default. The second declaration statement declared the variable Position of the CHAR datatype is having the maximum 10 bytes in size and assigned the value MANAGER which is of 7 bytes. Oracle will allocate the memory of 10 bytes rather than 7 bytes in this case.
VARCHAR2	It stores the string in which the length of the string is variable. Minimum size is 1 byte, and maximum size is 4000 bytes for the table column size. Minimum size is 1 byte, and maximum size is 32767 bytes for the variables. Size of the variable is defined at the time of variable declaration. Oracle will allocate memory when the variable is defined. It will take the memory allocation for the length of the string that is stored in variable rather than the size that has been given in the declaration part. For the sake of optimizing memory usage, VARCHAR2 is better than CHAR.	Location varchar2(20)=’Bihar’; The above declaration statement declared the variable Location of the varchar2 datatype is having the maximum 20 bytes in size and assigned the value Bihar which is of 5 bytes. Oracle will allocate the memory of 5 bytes rather than 20 bytes.
VARCHAR	It is synonymous with the VARCHAR2 data type. It is recommended to use VARCHAR2 instead of VARCHAR.	job varchar(10)=’IT’; The above declaration statement declared the variable job of the varchar datatype is having the maximum 10 bytes in size and assigned the value IT which is of 2 bytes. Oracle will allocate the memory of 2 bytes rather than 10 bytes.
NCHAR	It is same as CHAR data type. Character set will be of national character set. Character set is defining the session using NLS parameters. Character set can be either UTF16 or UTF8. Minimum size is 1 byte and maximum size is 2000 bytes.	state NCHAR (10); The above declaration statement declared the variable state of the NCHAR datatype is having the maximum 10 bytes in size. The variable length will be depending on the total number of characters present or total no of bytes occupied by the variable to assign the value.
NVARCHAR2	It is similar to VARCHAR2 data type. Character set will be of national character set. Character set is defining the session using NLS parameters. Character set can be either UTF16 or UTF8. Minimum size is 1 byte and maximum size is 4000 bytes.	State NVARCHAR2(10):=’IT’; The above declaration statement declared the variable state of the NVARCHAR2 datatype is having the maximum 10 bytes in size.
LONG AND LONG RAW	It is used to store the large text or the raw data upto size of maximum 2GB. It is used in the data dictionary. Long data type is used to store the character set data. LONG RAW is used to store the raw data into the binary format.           LONG RAW data type accepts the media object and images.         LONG works only on the data which can be stored using the character set.      It supports the backward compatibility.	LargeText LONG; LargeRaw LONG RAW;  The above declaration statement declares the variable LargeText and LargeRaw of data type LONG and LONG RAW respectively. It is not recommended in Oracle, instead LOB data type is used.

DATA TYPE	MAXIMUM SIZE IN PLSQL	MAXIMUM SIZE IN SQL
CHAR	32767 bytes	2000 bytes
NCHAR	32767 bytes	2000 bytes
RAW	32767 bytes	2000 bytes
VARCHAR2	32767 bytes	4000 bytes
NVARCHAR2	32767 bytes	4000 bytes
LONG	32760 bytes	2GB-1
LONG RAW	32760 bytes	2GB
BLOB	128TB	(4GB-1)* database_block_size
CLOB	128TB	(4GB-1)* database_block_size
NCLOB	128TB	(4GB-1)* database_block_size

National Character types:
PLSQL supports the two-character sets:

Database character set: It is used for identifiers and source code.

National character set: It is used for national language data.NCHAR and NVARCHAR2 store the character string from this character set.

It represents data as Unicode either UTF8 or AL16UTF16 encoding. Each character in the AL16UTF16  will take upto 2 bytes. It is used to calculate the length of the string in order to avoid an error of truncation when combine with different programming languages. Extra storage overhead is required to store strings composed of ASCII characters. Each character in UTF8 will be taking 1,2 or 3 bytes. Truncation error possibility will arise, when the data will transferred to the buffer in bytes.

 NUMBER:

It is used to store numeric data i.e. integers, real numbers and floating-point numbers which represent the quantities, and it is useful for performing operations. Numeric overflow or underflow will be occurring when the assigned value is not coming in the range of NUMBER data type. Precision is the total number of digits which occurred before the decimal point and scale is the total no of digits comes after the decimal point.

In order to declare the floating-point numbers, precision and scale are not used because decimal can also float to any position or point.So,only the below is preferred to use in this case.

NUMBER

To declare the integers which are not having any decimal value, so below is preferred to use in this case.

NUMBER(precision)

NUMBER(precision,0)

Constant or variable is not used to specify precision or scale, integer literals is used.

It is used to store fixed- or floating-point numbers which is having 38 decimal digits of precision. If you don't specify precision, default is 38 decimal digits, or maximum can be less in case of system supporting. Range of scale lies from -84 to 127 and support the rounding concept of mathematics. Default value of the scale is 0. It is used to work with the fields which is having the number data type. Variables can be declared either with the precision and decimal digits or without this information. Assigned value need to enclose within the quotes. Number has sub-type data type called as Integer.

X NUMBER(9,3);

Y NUMBER(8);

Z NUMBER:

Syntax Explanation:

In the first declaration, the variable X is of number data type with total precision 9 and decimal digits 3(scale).

In the second declaration, the variable Y is of number data type with total precision 8.

In the third declaration, the variable Z is of number data type with total and maximum precision 38 digits. There is no restriction in the precision or the decimal places.

It can be categorized into below sub-types

DEC, DECIMAL & NUMERIC are used to declare the fix point numbers which is having the maximum precision 38 digits.

BINARY_DOUBLE

BINARY_FLOAT

BINARY_INTEGER

DEC

DECIMAL

DOUBLE PRECISION

FLOAT

INT

INTEGER

NATURAL

NATURALN

NUMBER

NUMERIC

PLS_INTEGER

POSITIVE

POSITIVEN

REAL

SIGNTYPE

SMALLINT

BOOLEAN:

It stores the logical values on which the logical operation is performed. It represents either TRUE OR FALSE. It is mostly used in the conditional statements. Value assigned will not enclosed within the quotes. It's not used in the following situations such as: SQL statements, built in sql functions such as TO_CHAR & PLSQL functions which is invoked from SQL statements. There is no data type which is equivalent to SQL as like this.

Variable1 BOOLEAN.

Syntax Explanation:

Variable variable1 is declared as BOOLEAN data type and output or result will be TRUE OR FALSE OR NULL based on the condition occurs. It stands for the missing, unknown or inapplicable value. It has no parameters.

Note:

You cannot insert the values such as TRUE and FALSE into the database column.

You cannot select or fetch the column values into the BOOLEAN variable.

IF-THEN AND CASE construct are used to translate the BOOLEAN values into 0 or1,TRUEor FALSE & Y or N.

DATE:

It is used to store the fixed length date time which includes all the data since midnight to the time duration of the day in seconds. It stores the value in the date format as date, month and year. Whenever the variable is defined with this DATE data type, it can hold the date information and as well as time information. By default, the time information will set as 12:00:00 if not mentioned. It needs to enclose within the single quotes while assigning values to its variable. Standard format for oracle time input and output format is 'DD-MON-YY' which can be set as per the NLS parameters.NLS_DATE_FORMAT is at the session level.

Newyear Date='01-JAN-2025';

current_date DATE=sysdate;

Syntax Explanation:

In the above variable Newyear is declared as the DATE data type and it is assigned as value 1st January 2025.In the second declaration, the variable current_date is declared as the DATE type and it is assigned as the current value as the sysdate.Both the variables will hold the time information.

Variable that has date and time datatypes which holds the value of datetime.Date function sysdate which returns the current date and time. Variable that has interval data type which holds the value is called as intervals.

Numeric:

Its value is useful for performing arithmetic operation. It is floating type with the maximum precision of 38 decimal digits.

Classification of Numeric Data types:

DOUBLE PRECISION:

It is ANSI specific floating-point type with the maximum precision of 126 binary digits which is equivalent to approx. 38 decimal digits.

FLOAT:

It is ANSI and IBM specific floating-point type with the maximum precision of 126 binary digits which is equivalent to approx. 38 decimal digits.

INT:

It is ANSI specific with the maximum precision of 126 binary digits which is equivalent to approx. 38 decimal digits.

INTEGER:

It is IBM & ANSI specific with the maximum precision of 126 binary digits which is equivalent to approx. 38 decimal digits.

SMALLINT:

It is IBM & ANSI specific with the maximum precision of 126 binary digits which is equivalent to approx. 38 decimal digits.

REAL:It is floating point type with the maximum precision of 63 binary digits which is equivalent to approx. 18 decimal digits.

Composite Datatypes:

It has the internal components that can be accessed and manipulated individually such as element of an array, record or table. Collection and records are the examples. It is composed of the individual data which can be manipulated, processed and separate as well.

• Binary
• Binary double: Double precision IEEE 754 format floating point number. It is commonly used in the high scientific computation.
• Binary Float: Single precision IEEE 754 format floating point number. It is commonly used in the high scientific computation.
• Number: It is represented by Number (Precision, Scale).). It is used for fixed- and floating-point numbers. Number variable can also represent 0. Its absolute values lie in the range 1E-130 to 1.0E126 but not including.
• PLS Integer: It is used to store signed integer which lies in the range between -2147483648 and 2147483648 and it is represented in the 32 bits. It has less storage than NUMBER values. Its operation is performing arithmetic with the help of machine. It is faster than Binary Integer and Number which uses the library arithmetic. When the calculation will overflow, an exception is raised. It is used in the new application for better performance.

Binary Integer: It is signed integer which lies in the range between -2147483648 and 2147483648 and it is represented in the 32 bits. Its value occupies less storage than NUMBER values. Arithmetic operation on the binary integer is also faster than the number arithmetic. PLS_integer was faster than Binary integer in the earlier release which was running on the older databases. When the calculation will overflow, an exception is not raised since the result is assigned to the number variable. It is used in the old application for its compatibility.

Binary Integer subtypes:

Base type from which subtype is derived. Base type is the data type. Subtype has constraint and set of subset values which is attached or linked with the base type.

Binary_Integer is categorized into sub types:

NATURAL

NATURALN

POSITIVE

POSITIVEN

SIGNTYPE

NATURAL & POSITIVE restrict the integer variable values to positive values.NATURALN & POSITIVEN prevent the null value assigning to the integer variable.SIGNTYPE will restrict the integer value to -1,0 and 1.

DEC: It is represented by DEC(Precision, Scale). It is ANSI specific with fixed point type with maximum precision limit is 38 decimal digits.

DECIMAL(Precision, Scale): It is IBM specific with fixed point type with maximum precision limit is 38 digits.

Reference Datatypes:

It is pointed to the other data types. It is useful in storing the address of the other program types.

Reference data type is used to hold values called pointers that distinguish or designate other program items. It is classified into two types:

• REF Cursors
• REFS

User Defined Datatypes:

% TYPE and %ROWTYPE are the example of this. It is used to specify the base type. It provides the database type of the column.

SUBTYPE subtype_name IS base_type;

LOB:

It is used to hold values called LOB locators which is used to specify the location of large objects such as text blocks or graphic images.They are stored differently from the other database data. LOB is categorized into four types i.e.

BLOB

CLOB

NCLOB

BFILE

It is used to store and manipulate the large blocks of unstructured data like images and multi-media files. Oracle is preferring LOB instead of long data type. LOB is more flexible than LONG Data type.

It points to the large objects which are stored separately from the other data items like text, graphic images, video clips and sound waveforms. It allows efficient, random & piecewise access to the data. It is different than LONG & LONG RAW.

It is used to store LOB locators which points to the large objects in an external file, in-line (inside the row) & out of line (outside the row). PLSQL operates LOB through the locators. Package is used DBMS_LOB for operational work.

If you are working on the particular transaction which contains transaction ID which is done by LOB locator cannot be updated in another transaction. LOB locator which is used in one session cannot be saved to reuse for another session.

Comparision of LOB with LONG in terms of advantage:

The number of columns in the table with LONG data type is limited to 1 whereas the table has no restrictions on the number of columns with the LOB data type.

LOB can store upto 128 TB whereas LONG can store upto 2 GB.

Oracle is updating LOB data type in every release as per the changing requirement whereas LONG data type is constant, and it is not getting updated as per changing requirement.

During the data replication, the data interface tool is accepting the LOB data type of the table instead of LONG data type. LONG data type needs to replicate in the manual way.

LOB supports random access to the data whereas LONG supports sequential access to the data.

BLOB:

It is used to store the LOB data into the binary format. It can store the data upto maximum limit is 128 TB and minimum limit is 8TB.It does not store the data which is based on the character set details. It can store the unstructured data such as multimedia objects and images. When you select the column value as BLOB, then only the locator is returned. You can convert into RAW and vice-versa. It can store large binary object inside the database, in-line and out-line. Every BLOB variable stores a locator which points to the large object. It is fully participated in the transaction which are replicated and recoverable. Changes made by package DBMS_LOB can be committed or rollback.

Syntax:

binary_data BLOB;

Here, variable binary_data is declared as BLOB.

CLOB:

It is used to store the LOB data into the native character set. It is used to store the character set based storage. It cannot store the data like multimedia and images. It cannot put into the character string. Maximum limit is 128 TB, and minimum limit is 8 TB.You can convert CLOB datatype into CHAR and VARCHAR2 and vice versa. Locator is not to use to span session but can span transaction for read only.

BFILE:

It is used to store the large binary objects which is outside the operating system. It is system dependent and cannot exceed than 4GB.It is used to store the unstructured binary format data which is outside the database as operating system. Its size is limited to the operating system which is ready only file and which cannot be modified. Every BFILE locator which stores the file locator which points to the large binary file on the server.

 It includes the directory alias which specifies the full path name not the LOGICAL path.DBA make assured that BFILE should exit for read only operation.OS should maintain the file integrity. It will not participate in the transaction i.e. not recoverable and not replicated. Maximum number of open BFILES is set by the oracle initialization parameter SESSION_MAX_OPEN_FILES which is system dependent.

NCLOB:

It is used to store the data in the native character data set. It is used to store the character set based storage. It cannot store the data like multimedia and images. It cannot put into the character string. Maximum limit size is 128 TB and minimum limit is 8TB.It is used to store the large blocks of NCHAR data in the database, in-line or out-line. Both fixed-width and variable width character sets are supported. Every NCLOB variable stores the locator which points to the large block of NCHAR data. It is participated fully in the transaction which is recoverable and replicated. Locator is not to used to span session but can span transaction for read only. Changes made by package DBMS_LOB can be committed or rollback.

JSON :

I