code_icon_text

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

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

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

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

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

A very important aspect is that if the query contains select statements in the column list like

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!

Share this post on Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *