.
\nI’ll present here 3 ways to run a query for each result of another query. Let’s take an exemple: get all executions plan (select from dbms_xplan.display_cursor) for each of my queries (identified from v$sql). The 90’s way was to run the first query, which generates the second queries into a spool file, and execute that file. Here are easier ways, some of them coming from 12c new features lateral join and implicit statement result.<\/p>\n
I’m running 2 queries and will identify them from their module (APPINFO from sqlplus) and user:<\/p>\n
SQL> connect scott\/tiger\nConnected.\n\nSQL> set appinfo DEMO\n\nSQL> select * from DEPT;\nSQL> select * from EMP;\n<\/code><\/pre>\nI don’t show the result – you know it – but here is how I identify the queries:<\/p>\n
SQL> select sql_id,child_number,sql_text from v$sql where plan_hash_value>0 and module='DEMO' and parsing_schema_name='SCOTT';\n\nSQL_ID CHILD_NUMBER SQL_TEXT\n------------- ------------ ------------------------------\n5aht0fch310ca 0 select * from EMP\n18wrqtcj3ksap 0 select * from DEPT\n<\/code><\/pre>\nAnd I want to run dbms_xplan.display_cursor(sql_id,child_number) for each of them. Here are 3 ways to do it<\/p>\n
Cursor expression<\/h3>\n
Do you know cursor expressions? In your SELECT expressions, you can have a query. Your client will retrieve it as a cursor and have to run it. sqlplus and sqldeveloper does that. Here is an example:<\/p>\n
SQL> select\n cursor (select * from table(dbms_xplan.display_cursor(sql_id,child_number,format=>'TYPICAL'))) PLAN\n from\n (select * from v$sql where plan_hash_value>0 and module='DEMO' and parsing_schema_name='SCOTT')\n \/\n\n<\/code><\/pre>\nwhich gives the following result:<\/p>\n
PLAN\n--------------------\nCURSOR STATEMENT : 1\n\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------\nSQL_ID 5aht0fch310ca, child number 0\n-------------------------------------\nselect * from EMP\n\nPlan hash value: 3956160932\n\n--------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n--------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 3 (100)| |\n| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |\n--------------------------------------------------------------------------\n\n13 rows selected.\n\nCURSOR STATEMENT : 1\n\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------\nSQL_ID 18wrqtcj3ksap, child number 0\n-------------------------------------\nselect * from DEPT\n\nPlan hash value: 3383998547\n\n--------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n--------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 3 (100)| |\n| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |\n--------------------------------------------------------------------------\n\n13 rows selected.\n\n<\/code><\/pre>\nThis is possible on all versions, and the output can change depending on the tool you use. sqlplus handles it as above, running each cursor.<\/p>\n
implicit statement result<\/h3>\n
You want to do it from pl\/sql? That’s possible. But you have to format the output yourself with dbms_output.<\/p>\n
Except if you are in 12c and use the ‘implicit statement result’ new feature:<\/p>\n
SQL> declare\n r sys_refcursor;\n begin\n for c in\n (select * from v$sql where plan_hash_value>0 and module='DEMO' and parsing_schema_name='SCOTT')\n loop\n open r for select * from table(dbms_xplan.display_cursor(c.sql_id,c.child_number));\n dbms_sql.return_result(r);\n end loop;\n end;\n \/\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nwhich gives the following result:<\/p>\n
ResultSet #1\n\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------\nSQL_ID 5aht0fch310ca, child number 0\n-------------------------------------\nselect * from EMP\n\nPlan hash value: 3956160932\n\n--------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n--------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 3 (100)| |\n| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |\n--------------------------------------------------------------------------\n\n13 rows selected.\n\nResultSet #2\n\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------\nSQL_ID 18wrqtcj3ksap, child number 0\n-------------------------------------\nselect * from DEPT\n\nPlan hash value: 3383998547\n\n--------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n--------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 3 (100)| |\n| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |\n--------------------------------------------------------------------------\n\n13 rows selected.\n\n<\/code><\/pre>\nThis is similar to the previous approach in the way that the client has to manage the result.<\/p>\n
Lateral inline view<\/h3>\n
This is my preferred way which is only SQL and do not depend on the client formatting. A lateral inline view is a subquery that you put in the FROM clause, using the LATERAL keyword, and which can use data from the other tables on the left of it in the FROM clause:<\/p>\n
SQL> select plan_table_output from\n (select * from v$sql where plan_hash_value>0 and module='DEMO' and parsing_schema_name='SCOTT'),\n lateral (select * from table(dbms_xplan.display_cursor(sql_id,child_number)))\n ;\n<\/code><\/pre>\nwhich gives the following result:<\/p>\n
PLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------\nSQL_ID 5aht0fch310ca, child number 0\n-------------------------------------\nselect * from EMP\n\nPlan hash value: 3956160932\n\n--------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n--------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 3 (100)| |\n| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |\n--------------------------------------------------------------------------\n\nSQL_ID 18wrqtcj3ksap, child number 0\n-------------------------------------\nselect * from DEPT\n\nPlan hash value: 3383998547\n\n--------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n--------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 3 (100)| |\n| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |\n--------------------------------------------------------------------------\n\n<\/code><\/pre>\nThis is a 12c new feature. It existed on previous versions but not enabled by default and not documented, but you can set it with the following event:<\/p>\n
SQL> host oerr ora 22829\n\nSQL> alter session set events '22829 trace name context forever';\n<\/code><\/pre>\n12c came with a lot of nice improvement in SQL and PL\/SQL and they are available in all editions.<\/p>\n
Update<\/h3>\n
What is very cool about twitter is that sometimes I have to update my blog post as soon as it is published. In that case it seems that I ignored the simplest way to do that since 9i:<\/p>\n