Dynamic SQL in a SELECT … using an embedded function in Oracle 12

You can include dynamic SQL in a SELECT statement in Oracle 12 by using an embedded function. Ill show you how

For example, lets start with a query that shows a list of tables that are owned by the current schema.

SELECT   table_name
from     user_tables
order by table_name

TABLE_NAME   
EMPLOYEES    
DEPARTMENT   
INVOICES     
STOCK

Now we will add some dynamic SQL to the query.

For an example, we will use a dynamically generally SELECT statement to get a count of the rows in the table that is listed in the first column.

To do this, we will use a function that is embedded directly in the SELECT statement.
This custom function allows you to pass any SQL statement that you like into the function as a parameter. The function will run the SQL and return the result from the first column in the first row.

WITH
    function LOCAL_SQL_NUMBER (p_sql in varchar2) return number is
      v_result varchar2(4000);
    begin
      execute immediate P_SQL into v_result;
      return v_result;
    end;
SELECT   table_name,
         LOCAL_SQL_NUMBER (
             'select count(1) from ' || T.table_name  -- Dynamic SQL !
             ) row_count
from user_tables T
where table_name not like 'BIN$%' -- skip tables in the recycle bin
order by table_name

TABLE_NAME   ROW_COUNT
EMPLOYEES    1234
DEPARTMENT   16
INVOICES     1701
STOCK        256

This is an alternative version, where the function accepts a table name


WITH
         function GET_ROW_COUNT (
                     p_table_name in varchar2
                     )
                     return number is
         v_result varchar2(4000);
         begin
            execute immediate
               'SELECT COUNT(1) from ' || p_table_name
                into v_result;
           return v_result;
         end;
SELECT   table_name,
         GET_ROW_COUNT (T.table_name) row_count
from     user_tables T
where    table_name not like 'BIN$%' -- skip tables in the recycle bin
order by table_name

Add your own error handling if you want to make the code more robust, for example it is possible that the table is not accessible so the SELECT row count will fail… you could add error handling code to deal with that.