Imagine you have a large database where multiple tables contain a column named BOOK_ID. Perhaps you’re tasked with finding how many times a particular book (say, with an ID of 12345) appears across all these tables. How would you approach this?
In this post, I’ll explain a small piece of Oracle PL/SQL code that uses dynamic SQL to search for a specific value in any table that has a column with a specific name.
Finding the relevant tables
First, we need to determine which tables in the schema have a column with the desired name. To do this, we must look at a metadata table or view that contains information about all the tables in the schema and their columns. Most database systems offer such metadata tables, although their names and their structures vary greatly between different systems. In Oracle, the metadata table relevant to our task is called DBA_TAB_COLUMNS. Therefore, to find the names of all tables that contain a column BOOK_ID, you can use the following query:
SELECT table_name
FROM dba_tab_columns
WHERE column_name = 'BOOK_ID';
The output might be:
TABLE_NAME
-----------------
BOOKS
LIBRARY_BOOKS
ARCHIVED_BOOKS
TEMP_BOOKS
OLD_BOOKS
Looping through the tables
Now we want to loop through these tables in order to execute an SQL query for each of them. In Oracle we use a PL/SQL FOR loop to do this:
BEGIN
FOR rec IN (SELECT table_name
FROM dba_tab_columns
WHERE column_name = 'BOOK_ID')
LOOP
-- do something for each record
END LOOP;
END;
/
Dynamic SQL Construction
We can use the loop variable rec to dynamically create an SQL statement as a string by using the string concatenation operator || and assign it to a variable, in this case v_sql:
v_sql := 'SELECT COUNT(BOOK_ID) FROM ' || rec.table_name || ' WHERE BOOK_ID = :val';
The :val part is a placeholder that will become relevant later.
Of course, the variable needs to be declared for the PL/SQL code block first, so we add a DECLARE section:
DECLARE
v_sql VARCHAR2(4000);
BEGIN
-- ...
END;
How do we execute the SQL statement that we stored in the variable? By using the EXECUTE IMMEDIATE statement and two other variables; let’s call them v_result and v_value:
EXECUTE IMMEDIATE v_sql INTO v_result USING v_value;
This will execute the SQL in v_sql, replace the :val placeholder by the value in v_value, and store the result in v_result. The latter will capture the result of our dynamic query, which is the count of occurrences.
Of course, we have to declare these two variables as well. We’ll set v_value to the book ID we are looking for. The whole code so far is:
DECLARE
v_sql VARCHAR2(4000);
v_value NUMBER := 12345; -- Value to search for
v_result VARCHAR2(4000);
BEGIN
FOR rec IN (SELECT table_name
FROM dba_tab_columns
WHERE column_name = 'BOOK_ID')
LOOP
v_sql := 'SELECT COUNT(BOOK_ID) FROM ' || rec.table_name || ' WHERE BOOK_ID = :val';
EXECUTE IMMEDIATE v_sql INTO v_result USING v_value;
END LOOP;
END;
/
Printing the results
If we execute the code above, we might be a little disappointed because it is accepted and executed without any errors, but nothing is printed out. How can we see the results? For that, we need to include DBMS_OUTPUT.PUT_LINE calls:
DBMS_OUTPUT.PUT_LINE('Found in ' || rec.table_name || ': ' || v_result);
But how do we handle the cases if no record was found or if there was an error in the SQL query? We’ll wrap it in an EXCEPTION handling block:
BEGIN
EXECUTE IMMEDIATE v_sql INTO v_result USING v_value;
DBMS_OUTPUT.PUT_LINE('Found in ' || rec.table_name || ': ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL; -- No matching rows found in this table
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in ' || rec.table_name || ': ' || SQLERRM);
END;
There’s still one thing to do. We first have to enable server output to see any of the printed lines:
SET SERVEROUTPUT ON;
This command ensures that any output generated by DBMS_OUTPUT.PUT_LINE will be displayed in your SQL*Plus or SQL Developer session.
Let’s put it all together. Here’s the full code:
SET SERVEROUTPUT ON;
DECLARE
v_sql VARCHAR2(4000);
v_value NUMBER := 12345; -- Value to search for
v_result VARCHAR2(4000);
BEGIN
FOR rec IN (SELECT table_name
FROM dba_tab_columns
WHERE column_name = 'BOOK_ID')
LOOP
v_sql := 'SELECT COUNT(BOOK_ID) FROM ' || rec.table_name || ' WHERE BOOK_ID = :val';
BEGIN
EXECUTE IMMEDIATE v_sql INTO v_result USING v_value;
DBMS_OUTPUT.PUT_LINE('Found in ' || rec.table_name || ': ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL; -- No rows found in this table
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in ' || rec.table_name || ': ' || SQLERRM);
END;
END LOOP;
END;
/
Here’s what the output might look like when the block is executed:
Found in BOOKS: 5
Found in LIBRARY_BOOKS: 2
Found in ARCHIVED_BOOKS: 0
Alternatively, if one of the tables throws an error (for instance, due to a permissions issue or if the table doesn’t exist in the current schema), you might see an output like this:
Found in BOOKS: 5
Error in TEMP_BOOKS: ORA-00942: table or view does not exist
Found in LIBRARY_BOOKS: 2
Conclusion
Dynamic SQL is particularly useful when the structure of your query is not known until runtime. In this case, since the table names come from a data dictionary view (dba_tab_columns), the query must be constructed dynamically.
Instead of writing a separate query for each table, the above code automatically finds and processes every table with a BOOK_ID column. It works on any table with the right column, making it useful for large databases.
Building and running SQL statements on the fly allows you to handle tasks that are not possible with static SQL alone.
Schneide Blog 