Скачать или смотреть How to Perform a SELECT in a Loop with Oracle SQL

Discover how to loop through a string, retrieve IDs from a database, and correct common SQL errors in your procedures with this step-by-step guide.
---
This video is based on the question https://stackoverflow.com/q/62532569/ asked by the user 'Quân Trần Văn' ( https://stackoverflow.com/u/13018834/ ) and on the answer https://stackoverflow.com/a/62532594/ provided by the user 'Popeye' ( https://stackoverflow.com/u/11565629/ ) 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: select in a loop in oracle sql?

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.
---
Mastering Loops in Oracle SQL: Extracting IDs from a String

If you're working with Oracle SQL and need to extract numeric IDs from a comma-separated string of names, you're not alone. It’s a common requirement, especially when dealing with database records. In this guide, we will explore a specific scenario and how to resolve it effectively while avoiding common pitfalls.

The Problem: Extracting IDs

In the given stored procedure example, the goal is to take a string of role names and convert it into a string of corresponding ID values. Here’s a breakdown of the database table structure and expected input-output scenario.

Sample Table: AUTH_GROUPS

idname1role_12role_23role_3Example Input and Output

Input: 'role_1,role_2,role_3'

Expected Output: '1,2,3'

In your stored procedure, you're experiencing issues due to a common mistake: trying to access a loop variable incorrectly. Let’s dive into the solution.

The Solution: Correcting the SQL Procedure

To achieve the task, we need to modify the SQL stored procedure to correctly access the loop variable. Here are the steps and explanations for each part.

Step 1: Adjusting the Loop Variable

Inside the loop, the loop variable is i, which is a record of the selected value. However, to retrieve the string value l generated in the loop, you must access it with i.l instead of trying to use i directly.

Corrected SQL Code Snippet:

Here’s an updated version of the procedure focusing on how to use the loop variable correctly:

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

Step 2: Explanation of the Changes

Understanding i.l:

In the loop, i refers to each result of the query. The actual value you want is accessed by appending .l, which is the alias for the trimmed role string.

Error Prevention:

Using the correct reference for the loop variable avoids the expression is of wrong type error that was previously encountered.

Step 3: Finalizing Output

After the loop, we concatenate all retrieved IDs into lvOutPut.

Finally, we remove the trailing comma to ensure a clean output format.

Conclusion

By following the corrected approach outlined in this guide, you can efficiently extract IDs from a string of role names in Oracle SQL using loops. Remember to always access loop variables with the correct syntax to avoid common errors.

With these adjustments, your stored procedure should run smoothly and produce the desired results. Happy coding!