Скачать или смотреть How to Append Records in a Table Type Object with Bulk Collect in PL/SQL

Learn how to effectively append records to a table type object using bulk collect inside a cursor loop in PL/SQL, avoiding record overwrites.
---
This video is based on the question https://stackoverflow.com/q/62993791/ asked by the user 'MAK' ( https://stackoverflow.com/u/12284745/ ) and on the answer https://stackoverflow.com/a/63882839/ provided by the user '0xdb' ( https://stackoverflow.com/u/6571020/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to append the records in table type object using bulk collect inside the cursor loop

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Appending Records in Table Type Objects with Bulk Collect in PL/SQL

When working with table type objects in Oracle PL/SQL, it’s common to encounter the issue of overwriting data while attempting to append records. This often causes the frustration of only seeing the last record added, rather than an accumulation of all desired records. This guide will guide you through effectively appending records into a table type object using bulk collect inside a cursor loop—solving the issue of data loss and ensuring all records are retained.

Understanding the Problem

In the original PL/SQL code, a function attempts to collect various records from a cursor query into a table type object. However, instead of appending new records, the implementation leads to overwriting the previously stored value, resulting in only the last processed record remaining in the collection.

The Key Code Snippet

Here’s the code that leads to the problem:

[[See Video to Reveal this Text or Code Snippet]]

This approach overwrites the entire DEM_CNT_LIST with a new value each time the loop iterates, leading to missing data.

The Solution

To effectively append records to your table type object without losing previous entries, you must modify how you store new values in the collection. Instead of using BULK COLLECT INTO, which pulls in data as a whole, you can directly assign a new object instance to the next index in the collection.

Step-by-Step Guide

Initialize Your Loop: Use a cursor to fetch rows one at a time.

Conditionally Append Records: Check if certain conditions are met before appending data to your collection.

Store Each Record Appropriately: Use the syntax that allows you to append without overwriting existing records.

Here’s the revised code snippet that achieves this:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of Changes

DEM_CNT_LIST.EXTEND(): This method increases the size of your collection by one, thus preparing it to hold the new record.

DEM_CNT_LIST(DEM_CNT_LIST.LAST): This correctly assigns a new instance of DEM_CNT_OBJ_TYPE at the end of the current list, preserving all previously stored entries.

Conclusion

By adjusting the way new records are appended to your table type object within your PL/SQL function, you can effectively collect all desired records without the risk of losing previously entered data. Employing structured methods such as extending your collection and placing new records at the end guarantees that your program operates as intended, enhancing its effectiveness in handling database queries.

For any PL/SQL developer facing similar issues, taking this structured approach can save time and effort while ensuring data integrity in your applications.