Datatypes – ORACLE PL/SQL Chapter Wise Interview Questions

Datatypes – ORACLE PL/SQL Chapter Wise Interview Questions

Question 1:
How Many Categories of Data Types are there in PL/SQL?
Answer:
PL/SQL data types are grouped into 4 categories:

1. Scalar Data Types: A scalar data type holds a single value.
2. Composite Data Types: A composite data type has internal components, such as the elements of an array.
3. LOB Data Types: A LOB data type holds a lob locator that specifies the location of a large object.
4. Reference Data Types: A reference data type holds a pointer that points to another data object.

Question 2:
How Many Scalar Data Types Are Supported in PL/SQL?
Answer:
PL/SQL supports many scalar data types divided into 4 groups:

1. Numeric Types: BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN, REAL, SIGNTYPE, SMALLINT.
2. Character Types: CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2, RAW, ROWID, STRING, UROWID, VARCHAR, VARCHAR2.
3. Boolean Types: BOOLEAN.
4. Date Types: DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND.

Question 3:
What are the LOB datatypes?
Answer:
The LOB (large object) datatypes BFILE, BLOB, CLOB, and NCLOB let you store blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size. And, they allow efficient, random, piece-wise access to the data.

Question 4:
What is a Collection in Oracle?
Answer:
A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

Question 5:
List various Collection types?
Answer:
There are various collection types:

1. Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values, (similar to hash tables in programming languages.)
2. Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
3. Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Question 6:
Explain Associative arrays or Index-by tables.
Answer:
Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.
Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique, either by using the primary key from a SQL table, or by concatenating strings together to form a unique value. Associative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements. It is like a simple version of a SQL table where you can retrieve values based on the primary key. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.
Because associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as INSERT and SELECT INTO. You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body.

Question 7:
Explain VARRAY Datatype.
Answer:
Items of type VARRAY are called varrays. They allow you to associate a single identifier with an entire collection. This association lets you manipulate the collection as a whole and reference individual elements easily. To reference an element, you use standard subscripting syntax. For example, Grade(3) references the third element in varray Grades.

A varray has a maximum size, which you must specify in its type definition. Its index has a fixed lower bound of 1 and an extensible upper bound. For example, the current upper bound for varray Grades is 7, but you can extend it to 8, 9,10, and so on. Thus, a varray can contain a varying number of elements, from zero (when empty) to the maximum specified in its type definition.

Question 8:
Explain Nested Tables.
Answer:
Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows. PL/SQL nested tables are like one-dimensional arrays. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables.

Question 9:
What are the Large object types supported by Oracle?
Answer:
BLOB and CLOB.

Question 10:
How to Convert Character Types to Numeric Types?
Answer:
You can convert character types to numeric types in two ways:

1. Explicitly by using TO_NUMBER() function.
2. Implicitly by putting character data in a numeric operation.

The sample script below shows you how to convert character types to numeric types:

PROCEDURE Proc_Convert AS 
start_time CHAR(5); 
finish_time CHAR(5); 
elapsed _time NUMBER(5);
BEGIN
start_time := '12052';
finish_time := '15314';
diff_time := TO_NUMBER(finish time)
- TO_NUMBER(start_time);
diff_time := finish_time - start_time; -- same as above
END;