Identify the Different Datatypes within PL/SQL

Every variable has a datatype, which specifies a storage format, constraints, and a valid range of values. PL/SQL provides a variety of datatypes: SCALAR, COMPOSITE, REFERENCE, and LOB. You define a variable for a particular datatype to be able to manipulate the data. Review the following series of diagrams to identify the different datatypes available within PL/SQL.

PL/SQL datatypes have tight integration with the structured query language (SQL). PL/SQL is Oracle's procedural language extension to SQL, and it is designed to work seamlessly with SQL. PL/SQL datatypes are closely aligned with SQL datatypes, and they are used to manipulate and store data in Oracle databases. PL/SQL datatypes can be categorized into two main groups:

1. Scalar datatypes: These are similar to SQL datatypes and include types such as NUMBER, VARCHAR2, DATE, and TIMESTAMP.
2. Composite datatypes: These are more complex datatypes that can store multiple values, such as arrays and records.

PL/SQL datatypes are used to declare variables, which can then be used to store and manipulate data in Oracle databases. The tight integration with SQL allows for easy exchange of data between PL/SQL and SQL, making it a powerful tool for database development and management.
Here are some examples of PL/SQL datatypes and their corresponding SQL datatypes:

PL/SQL	SQL Datatype
NUMBER	NUMBER
VARCHAR2	VARCHAR2
DATE	DATE
TIMESTAMP	TIMESTAMP
BOOLEAN	(no equivalent)

Note that while there is a high degree of integration between PL/SQL and SQL datatypes, there are some differences in how they are used and manipulated.

SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like statements such as

1. SELECT,
2. INSERT,
3. UPDATE, and
4. DELETE

make it easy to manipulate the data stored in a relational database. PL/SQL is tightly integrated with SQL. With PL/SQL, you can use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudocolumns. PL/SQL fully supports SQL data types and you need not convert between PL/SQL and SQL data types. For example, if your PL/SQL program retrieves a value from a database column of the SQL type VARCHAR2, it can store that value in a PL/SQL variable of the type VARCHAR2. Special PL/SQL language features let you work with table columns and rows without specifying the data types, saving on maintenance work when the table definitions change.
Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages. Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation. Many database features, such as triggers and object types, use PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL and PL/SQL supports both

1. static and
2. dynamic

SQL. "Static SQL" is SQL whose full text is known at compilation time. "Dynamic SQL" is SQL whose full text is not known until run time. Dynamic SQL enables you to make your applications more flexible and versatile.
PL/SQL datatypes include all the valid SQL datatypes as well as "complex datatypes" based on query structures.

Here is a list of scalar datatypes available in PL/SQL: Numeric Datatypes

1. NUMBER (includes integer and floating-point numbers)
2. BINARY_FLOAT
3. BINARY_DOUBLE
4. DEC (deprecated)
5. DECIMAL (deprecated)
6. NUMERIC (deprecated)
7. INTEGER (subtype of NUMBER)
8. INT (subtype of NUMBER)
9. SMALLINT (subtype of NUMBER)
10. FLOAT (subtype of NUMBER)
11. DOUBLE PRECISION (subtype of NUMBER)

Character Datatypes

1. CHAR
2. VARCHAR2
3. NCHAR
4. NVARCHAR2
5. STRING (subtype of CHAR or VARCHAR2, depending on the context)

Date and Time Datatypes

1. DATE
2. TIMESTAMP
3. TIMESTAMP WITH TIME ZONE
4. TIMESTAMP WITH LOCAL TIME ZONE
5. INTERVAL YEAR TO MONTH
6. INTERVAL DAY TO SECOND

Boolean Datatype

• BOOLEAN

Miscellaneous Datatypes

1. RAW (raw binary data)
2. LONG RAW (deprecated)
3. ROWID (a unique identifier for a row in a table)
4. UROWID (a universal rowid, used for foreign tables and non-Oracle tables)

Note: The DEC, DECIMAL, NUMERIC, and LONG RAW datatypes are deprecated and should not be used in new code. Also, some subtypes like INTEGER, INT, SMALLINT, FLOAT, DOUBLE PRECISION are subtypes of NUMBER and can be used interchangeably in most cases.

RECORD TABLE VARRAY

REF CURSOR REF object_type

BFILE BLOB CLOB NCLOB

The correct values and their datatypes are:

1. TRUE: SCALAR datatype
2. RECORD: COMPOSITE datatype
3. %ROWTYPE: REFERENCE datatype
4. BLOB: LOB datatype

There are two generalized types that you define in packages. You can declare

1. static or
2. dynamic datatypes.

Datatypes are typically PL/SQL 1) structures, 2) collections, 3) reference cursors, and 4) cursors. All of these can be dynamic or static datatypes. They are dynamic when their declaration anchors their type to a row or column definition. You use the %ROWTYPE to anchor to a row and %TYPE to anchor to a column, as qualified in Table 2-4. Types are static when they rely on explicitly declared SQL datatypes, such as DATE, INTEGER, NUMBER, or VARCHAR2. As a general rule, package specifications are independent of other schema-level objects. You build dependencies when you anchor package specification–declared types to catalog objects, like tables and views. If something changes in the dependent table or view, the package specification becomes invalid. Changes in package specifications can create a cascade reaction that invalidates numerous package bodies and standalone schema-level programs.

The BLOB datatype stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics. BLOB objects can store binary data up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). If the tablespaces in your database are of standard block size, and if you have used the default value of the CHUNK parameter of LOB storage when creating a LOB column, then this is equivalent to (4 gigabytes - 1) * (database block size). BLOB objects have full transactional support. Changes made through SQL, the DBMS_LOB package, or the Oracle Call Interface (OCI) participate fully in the transaction. BLOB value manipulations can be committed and rolled back. However, you cannot save a BLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.
Every constant, variable, and parameter has a datatype (or type), which specifies a storage format, constraints, and valid range of values.
PL/SQL provides many predefined datatypes. For instance, you can choose from

1. integer,
2. floating point,
3. character,
4. BOOLEAN,
5. date,
6. collection,
7. reference, and
8. large object (LOB) types.

PL/SQL also lets you define your own subtypes. This chapter covers the basic types used frequently in PL/SQL programs. Later chapters cover the more specialized types.
In the next lesson, non-PL/SQL bind and host variables will be discussed.