PL/SQL tables

[beingchinmay]

One of the first questions I ever heard posed about PL/SQL Version 1.1 was, "Where are the arrays?"Programmers who coded in the nonprocedural interface of SQL*Forms, after spending a decade with languages like FORTRAN and C, got all excited when they heard about PL/SQL: they thought they'd get all the good stuff they had in their 3GL environments −− particularly arrays. Imagine the shock and disappointment when PL/SQL not only lacked the ability to read and write disk files, but also did not support arrays!

[yoyolt]

It's true that the introduction of PL/SQL, especially in its early iterations (like version 1.1), might have been a stark departure for programmers coming from languages that supported arrays and file I/O operations. PL/SQL, which stands for Procedural Language/SQL, is Oracle Corporation's proprietary server-side, procedural extension to the SQL (Structured Query Language). Its integration with SQL makes it highly suited to manipulate data and process queries in Oracle databases.

As for arrays, PL/SQL did not initially support typical Array data structures that developers were accustomed to from other programming languages. However, Oracle eventually introduced "collections" in PL/SQL, which are somewhat similar to arrays. Collections come in three types: nested tables, varrays, and associative arrays (also called index-by tables). These types offered some of the functionality of traditional arrays and have been expanded and improved over the various versions of the language.

Associative arrays, in particular, are the closest to arrays found in other languages, as they allow programmers to define an array with an index of PLS_INTEGER or VARCHAR2 type.

Regarding I/O operations, due to the nature of PL/SQL as a server-side language specifically designed for SQL tasks, it does not fully support file read and write operations like a traditional programming language would. However, Oracle provides packages like UTL_FILE that developers can use for such I/O operations.

It's also worth noting that Oracle has consistently evolved and enhanced PL/SQL over the years, adding features that make it more powerful and versatile, albeit within its intended use case as a language tightly coupled with the Oracle RDBMS. More recent additions include enhancements to collections, among other features. For specific application needs around file handling or data structure manipulation, many organizations still use a mix of traditional programming languages along with PL/SQL to leverage the strengths of each.

In the context of PL/SQL and arrays, it is important to understand why standard array structures were not part of its early versions. Oracle designed PL/SQL for database operations, and traditional arrays, as known from other programming languages, may not fit naturally into such a context. As a language closely tied to relational database management systems, PL/SQL's focus is primarily on handling and manipulating sets of data returned in SQL queries, rather than individual array elements.

Even so, Oracle did understand the need for structures similar to arrays and so introduced collections, which are similar to arrays but especially designed for use in a database context. Following are the common types of Collections that PL/SQL supports:

1. **Associative arrays** - They are the closest that PL/SQL gets to traditional arrays. Also known as index-by tables, associative arrays are sets of key-value pairs, where each key is unique and used to locate a corresponding value.

2. **Varrays (Variable-Size Arrays)** - Varrays are single-dimensional structures that have a maximum size. They can hold a fixed number of elements, and those elements are stored and retrieved based on their index number.

3. **Nested Tables** - Nested tables are similar to varrays, but with no upper size limit. They initially do not have a size but rather increase and decrease dynamically.

File read/write operations were also not originally a part of PL/SQL, as PL/SQL is primarily designed for server-side stored procedures, not system programming or scripting. However, Oracle included the UTL_FILE package that allows PL/SQL to read and write to files, albeit with some restrictions compared to conventional programming languages.

Keep in mind that characteristics of PL/SQL reflect its design purpose: to manipulate, control, and handle database operations within the Oracle relational database realm. It brings a procedural programming outlook to SQL operations, enabling much higher control compare to raw SQL.

Moreover, traditional programming languages and PL/SQL often complement each other in enterprise software systems. PL/SQL logically handles the data manipulation, transaction control, and database-specific procedures, while traditional languages handle the file I/O, user interface, and other system-level tasks. This separates concerns and allows each language to do what it does best.

Exploring this topic further, it's important to realize that PL/SQL, like its SQL foundation, is set-oriented. It's designed to work most efficiently when dealing with sets of rows rather than individual rows. Thus, the concept of the traditional array, as found in procedural languages, doesn't fit as naturally with this paradigm. The Oracle database engine is optimized to manage data in large blocks and to operate on sets of data, and PL/SQL is designed to fit seamlessly into this architecture.

When programmers required such array-like functionality, this led to the development of PL/SQL collections:

- **Associative Arrays**: These can be thought of as hashmaps or dictionaries, where you have a unique key associated with a value. They work in a similar way to arrays but give the flexibility of arbitrary indexing rather than continuous numeric index.

- **Nested Tables**: These represent sets of values and are primarily intended to be used when the number of elements is not known in advance and can change over time. You can use Nested Tables when you need to put an emphasis on column subscripts.

- **Varrays**: These are an ordered set of elements. They are similar to arrays and represent another type of collection but with a fixed maximum size. These are good when you know the collection size upfront and want to maintain the orderness of your elements.

Each of these types of collections has its uses, depending on the particular requirements of the problem you're trying to solve.

Concerning file I/O, while PL/SQL does not natively support it, Oracle has provided several ways to accomplish this, such as the UTL_FILE PL/SQL package, which can read from and write to files on the server file system, and the Oracle built-in BFILE data type which can point to a file on the server system and read from it. More complex and flexible file operations should still be handled in a local programming environment, using a language that's designed for general-purpose programming.

Lastly, Oracle (as well as the wider SQL community) has invested significant resources not only to improve PL/SQL, providing concepts like collections, but also to provide interfaces that allow easy interoperation with other popular programming languages that developers might be used to and that complement SQL's data storage and manipulation capabilities. It allows the creation of external procedures or use of APIs that can be written in third-party languages (like Java or C) to interact with the database, hence carrying out tasks that might not be a good fit for PL/SQL.

To quantify the nuances further, PL/SQL can work exceptionally well with procedural logic and at the same time exploit SQL's power of manipulating sets of data. Here are some unique features of PL/SQL that might be of interest:

1. **Error Handling**: PL/SQL provides a sophisticated error handling mechanism. Whenever an error occurs, an exception is raised by PL/SQL. This exception can either be dealt with by the developer-provided error handling routine or by default error handling routine.

2. **Encapsulation and Data Hiding**: Using PL/SQL, the application developer can encapsulate and hide information, which maintains the integrity of data. A named PL/SQL block is known as a subprogram, which can be either a procedure or a function. These named PL/SQL blocks can be stored in an Oracle database and reused.

3. **Triggers**: In Oracle, a trigger is a stored PL/SQL block attached to a table. A trigger is implicitly fired when a particular event takes place on the table. The event could be an insert, update or delete operation.

4. **Cursors**: PL/SQL allows defining cursors, which are like pointers to rows in the database. Cursors let PL/SQL manage processing groups of rows returned in a query one row at a time, much like you would see in traditional file IO operations or arrays.

Despite these useful and powerful features, PL/SQL shines the brightest when used for what it's designed for - getting maximum efficiency out of the Oracle Database, and should primarily be used to perform operations where the task utilizes the strengths of SQL. The tasks that require sequential and conditional flow of control, especially complex cursor handling, are also better handled inside the Oracle Database using the PL/SQL language.

On the other hand, if tasks involve file I/O operations, intensive computations, text processing, and images, it is recommended to use third-generation programming languages such as Java or Python. These general-purpose languages offer better user-interfaces, web-based interactivity, computation-intensive operations, file manipulations, interactions with OS, etc., with more ease and efficiency.

So essentially, while Oracle's PL/SQL has evolved over time to offer a variety of features, it remains closely tied to the Oracle database and its primary role in server-side processing and manipulation of data. For wider system-level operations, a combination of PL/SQL with other general-purpose programming languages often serves best.