PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural language extension for SQL which allows us to procedurally tie SQL statements to effectively define business logic.In this course, we will talk about collection types available in PL/SQL.So the problem was in executing the MULTISET-related functionality.We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error.Here is an example of a function with a record in the parameter list: DECLARE -- Declare a basic Table Type Array TYPE a_char_data IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; -- Declare a complex record type with an embedded index by table. v_data(1):= '123456789'; v_data(1):= '01-JAN-1900'; -- Populate the first and second rows of the name table in the first row of the v_data variable.v_data(1).name(1) := 'Lewis'; v_data(1).name(2) := 'Joe'; --Finally, display the ssn of the first row of the v_data variable and then looped through the name table of the first row of the v_data variable.
One option to implement this is by using an AFTER INSERT trigger that will perform this additional stuff, but I really dislike this option (mainly because the code becomes hidden in a way, and there may be a negative impact on performance).
Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA).
SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session.
Once you have declared a record, you can then manipulate the data in these fields (or the record as a whole) as you can see below:.
This technique allows you to shrink down the size of a parameter list (pass a single record instead of a lengthy and cumbersome list of individual values). TYPE r_data IS RECORD ( ssn VARCHAR2(9) NOT NULL := -1, name a_char_data, -- Notice the table_type used here dob DATE ); -- Declare an "Associative Array (or index-by table)" using the complex record type. TYPE a_multi IS TABLE OF r_data INDEX BY BINARY_INTEGER; -- Declare a variable using the complex array v_data a_multi; BEGIN -- Populate the ssn and dob columns of the first record of the v_data variable.