In my personal experience, quite often I came across the need to test and optimize queries used by REF CURSORS. Sometimes it’s a really painless operation, other times it can really be a very long and complex operation that can make you waste a lot of time. Let’s see what we would normally do in a simple case: consider a procedure GET_DATA which returns a SYS_REFCURSOR
|
1 2 3 4 5 6 7 8 9 10 |
create or replace procedure get_data(pi_table_name in VARCHAR2, po_resultset out SYS_REFCURSOR) is begin open po_resultset for select atc.OWNER, atc.COLUMN_NAME, atc.DATA_TYPE from all_tab_columns atc where table_name = pi_table_name order by column_id; end get_data; / |
The query used for the ref cursor is quite simple, it extracts 3 columns and uses just one parameter as a filter. In such case, if I wanted to test the query, I would probably take it outside and run it manually to perform my test.
Another possibility is to write a simple code to fetch the resultset into a row type variable, something like this
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
declare type rt_cols is record(owner varchar2(30), colname varchar2(30), coltype varchar2(30)); l_cols rt_cols; l_cur sys_refcursor; l_row number; begin get_data(pi_table_name => 'USER_TABLES', po_resultset => l_cur); l_row := 0; loop fetch l_cur into l_cols; exit when l_cur%notfound; l_row := l_row + 1; dbms_output.put_line(l_row||': '||l_cols.owner||'-'||l_cols.colname||'-'||l_cols.coltype); end loop; end; / |
Quite easy and straightforward till now. Problems start when the query used to return the resultset extracts a high number of column and uses many variables calculated in other procedures.
In such case, our GET_DATA procedure would look like
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
create or replace procedure get_data(pi_table_name in VARCHAR2, po_resultset out SYS_REFCURSOR) is l_var1 varchar2(30); l_var2 number; l_var3 number; l_var4 varchar2(30); ... ... begin l_var1 := get_var1(pi_table_name); if l_var1 is not null then l_var2 := get_var2(l_var1); esle ... end if; l_var3 := ... ... open po_resultset for select atc.OWNER, atc.COLUMN_NAME, atc.DATA_TYPE, c1, c2, c3, c4, c5, c6, .....c110, c111, c113 from all_tab_columns atc, tab1, tab2, tab3 where tab1.c1 = NVL(l_var2, l_var1) and tab3.c7 = l_var4 and ... and table_name = pi_table_name order by column_id; end get_data; / |
How do we test that query (e.g. run it in order to “capture” the execution plan)? The first option is to take the query out and just run it manually after replacing the variables. But the query uses many variables calculated from external procedures, in some cases this part is very long and tricky and retrieving all the values can require a lot of time. So let’s forget about taking out the query and just call the GET_DATA procedure and fetch the resultset as we did in the second example. Well, in this case we need to create a record TYPE which reflects the column number and type returned by the ref cursor. Have you seen how many columns from different tables are in the select statement? Over 100, and we don’t want to check them one by one, do we? This would require again a lot of time, too much. What can we do then? The answer is…we can use dynamic SQL!
Starting from version 11g Oracle introduced a function to convert a ref cursor to a numeric one. This opens a new possibility that will make us save a valuable amount of time
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
declare l_cur number; l_ref_cur sys_refcursor; l_row number; begin get_data(pi_table_name => 'USER_TABLES', po_resultset => l_ref_cur); l_row := 0; l_cur := dbms_sql.to_cursor_number(l_ref_cur); while dbms_sql.fetch_rows(l_cur) > 0 loop l_row := l_row + 1; end loop; dbms_output.put_line(l_row||' total rows'); end; / |
Easy! We didn’t have to worry about all those internal variables neither about the number and type of columns returned, sweet! In case we want to read the resultset we can still add some code to extract column values
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
declare l_cur number; l_ref_cur sys_refcursor; l_cols number; l_desc dbms_sql.desc_tab; l_varchar varchar2(4000); begin get_data(pi_table_name => 'USER_TABLES', po_resultset => l_ref_cur); l_cur := dbms_sql.to_cursor_number(l_ref_cur); -- Read columns information dbms_sql.describe_columns(l_cur, l_cols, l_desc); for i in 1..l_cols loop dbms_sql.define_column(l_cur, i, l_varchar, 4000); end loop; while dbms_sql.fetch_rows(l_cur) > 0 loop -- Extract column values for i in 1..l_cols loop dbms_sql.column_value(l_cur, i, l_varchar); dbms_output.put_line('Row '||dbms_sql.last_row_count||': '||l_desc(i).col_name||' = '||l_varchar); end loop; end loop; dbms_output.put_line(dbms_sql.last_row_count||' total rows'); end; / |
A very important aspect is that if the query contains select statements in the column list like
|
1 2 |
select atc.OWNER, atc.COLUMN_NAME, (select ... from ... where) as c3 from ... |
those select statements are not executed if you don’t extract the column values as we did with dbms_sql.column_value(l_cur, i, l_varchar); in the last example. Therefore, if in such case you want to capture the full execution plan of the ref cursor, you have to parse all the column values.
The conclusion is that thanks to dynamic SQL it’s possible to fetch data from a ref cursor without worrying about the complexity behind it. A huge time saver!
Thank you very much,
you really helped me
I’m glad it helped 🙂