Dynamic SQL in a SELECT … using an embedded function in Oracle 12
Scott Hollows - 24/Jan/2017
Scott Hollows - 24/Jan/2017
[SHOWTOGROUPS=4,20]
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.
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.
TABLE_NAME ROW_COUNT
EMPLOYEES 1234
DEPARTMENT 16
INVOICES 1701
STOCK 256
This is an alternative version, where the function accepts a 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.
[/SHOWTOGROUPS]
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.
[/SHOWTOGROUPS]
