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.
- Oracle & Delphi software developer based in Perth, Western Australia
- Australian Delphi User Group – President
- Australian Oracle User Group – WA Committee Member