How to Update Multiple Tables in PL/SQL Using Dynamic SQL

Discover how to efficiently update several tables in PL/SQL using dynamic SQL to handle table names dynamically. Learn the solution step-by-step!
---

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: PL/SQL update several table using select values as a table name

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Updating Multiple Tables in PL/SQL: A Comprehensive Guide

Updating multiple tables in PL/SQL can be quite a challenge, especially when you're trying to work with a list of table names dynamically. If you've found yourself receiving errors like ORA-00942: table or view does not exist, you’re not alone. In this guide, we're going to explore how to properly use dynamic SQL to update several tables based on values stored in another table.

The Problem

Imagine you have multiple tables you want to update based on some conditions, and you've created a temporary table (dgtmp_nama_tbl) to store the names of those tables. You've tried to create a PL/SQL script to loop through the names and perform updates, but you're hitting a snag with errors indicating that the tables do not exist.

Here's a brief overview of the steps you're trying to execute:

Insert various table names into a temporary table (dgtmp_nama_tbl).

Use a cursor to loop through those table names.

Attempt to perform an update on those tables.

Sample Code Causing Errors

You might have started with code similar to this:

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

This approach fails because you cannot directly reference table names using a cursor like this in PL/SQL.

The Solution: Using Dynamic SQL

To resolve this issue, you'll need to harness the power of dynamic SQL. This allows you to build the SQL statement as a string and then execute it. Here's how you can rewrite the original code:

Step-by-Step Solution

Declare a Cursor: This cursor will retrieve the table names from your temporary table.

Create a Dynamic String: Build an SQL string that contains your update command using the retrieved table names.

Execute the String: Use EXECUTE IMMEDIATE to run the constructed SQL command.

Updated PL/SQL Code

Here’s how your updated PL/SQL block should look:

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

Explanation of the Code

Cursor: It selects table names from dgtmp_nama_tbl.

Dynamic String: The variable l_str composes the SQL UPDATE command with the correct table name from the cursor.

EXECUTE IMMEDIATE: This command runs the SQL stored in l_str as if it were a regular SQL statement, allowing dynamic execution.

Conclusion

With dynamic SQL in PL/SQL, you can efficiently update multiple tables without running into errors related to table existence. Remember, the critical takeaway is to construct the SQL statement as a string and execute it with EXECUTE IMMEDIATE. This not only prevents errors but also enhances the flexibility of your code.

If you have any questions or need further clarification, feel free to leave a comment below. Happy coding!