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.

Scott Hollows

scott_circle

  • Oracle & Delphi software developer based in Perth, Western Australia
  • Australian Delphi User Group – President
  • Australian Oracle User Group – WA Committee Member
blog email linkedinlogo

Author: Scott Hollows

Enterprise software developer based in Perth, Western Australia. Focused on Oracle, Delphi, Data Warehouse design and ETL, Data Architect, Business Intelligence, Oracle performance tuning. President of the Australian Delphi User Group (ADUG) LinkedIn www.linkedin.com/in/scotthollows

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s