Brilliant Article on ref-cursors
- Courtesy
https://forums.oracle.com/forums/thread.jspa?threadID=886365
What is the point of ref cursors?
A good question. Many people learn about ref cursors and then try and use them everywhere.
Although I personally know how to write and use ref cursors,
I've found very little use for them in my production code.
If you are thinking of using a ref cursor, the first thing you should ask yourself is "Why do I need one?"
If your answer is that you need to write your code to dynamically generate SQL,
then perhaps you should look at what you are trying to achieve. Typically dynamic SQL,
in a lot of cases, is unnecessary and a major cause of unmaintainable and unscalable code as well
as possible performance issues and SQL injection.
If you really have a valid need to dynamically create SQL and you fully understand the
implications and risks involved, then a ref cursor is useful for this task.
What you will find however is that you are limited with ref cursors to a fixed result structure,
so it may not be as generic a solution as you had initially planned for.
If you consider the examples from above, you will see that we had to define a
SQL structure of known columns in order to receive the data from the ref cursor.
So, whilst the function that opens the ref cursor could do so for any piece of query text,
the only way of successfully getting the data out of that ref cursor is to know what columns
we are expecting back from it.
There is a way around this however.
Either use the DBMS_SQL package to dynamically generate and process your queries or,
from 11g onwards, take your ref cursor and convert it to a DBMS_SQL cursor using 11g's
new DBMS_SQL.TO_CURSOR_NUMBER feature
(http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sql.htm#CHDJDGDG).
DBMS_SQL is a very powerful way of writing dynamic SQL, but that's for another article.
(Edit to add)
The most common purpose for ref cursors is where we have a front end application
(e.g. a .NET or Java application) that submits parameters to a function or procedure on the
database and is given a ref cursor back so that it can then retrieve that data and display it
appropriately for that front end. This allows the database layer to be kept seperate from the
user interface layer of the application development. The Application itself does not need to
know how to query that particular database, but just simply expects that the results will be
presented back in a pre-determined format (i.e. it knows what columns to expect to be returned
by the ref cursor).
Summary
In summary, the one key thing to remember is..
REF CURSORS ARE NOT DATA CONTAINERS. ONCE OPENED,
THEY ARE SIMPLY A POINTER TO A QUERY THAT HAS YET TO FETCH DATA.
- Courtesy
https://forums.oracle.com/forums/thread.jspa?threadID=886365
What is the point of ref cursors?
A good question. Many people learn about ref cursors and then try and use them everywhere.
Although I personally know how to write and use ref cursors,
I've found very little use for them in my production code.
If you are thinking of using a ref cursor, the first thing you should ask yourself is "Why do I need one?"
If your answer is that you need to write your code to dynamically generate SQL,
then perhaps you should look at what you are trying to achieve. Typically dynamic SQL,
in a lot of cases, is unnecessary and a major cause of unmaintainable and unscalable code as well
as possible performance issues and SQL injection.
If you really have a valid need to dynamically create SQL and you fully understand the
implications and risks involved, then a ref cursor is useful for this task.
What you will find however is that you are limited with ref cursors to a fixed result structure,
so it may not be as generic a solution as you had initially planned for.
If you consider the examples from above, you will see that we had to define a
SQL structure of known columns in order to receive the data from the ref cursor.
So, whilst the function that opens the ref cursor could do so for any piece of query text,
the only way of successfully getting the data out of that ref cursor is to know what columns
we are expecting back from it.
There is a way around this however.
Either use the DBMS_SQL package to dynamically generate and process your queries or,
from 11g onwards, take your ref cursor and convert it to a DBMS_SQL cursor using 11g's
new DBMS_SQL.TO_CURSOR_NUMBER feature
(http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sql.htm#CHDJDGDG).
DBMS_SQL is a very powerful way of writing dynamic SQL, but that's for another article.
(Edit to add)
The most common purpose for ref cursors is where we have a front end application
(e.g. a .NET or Java application) that submits parameters to a function or procedure on the
database and is given a ref cursor back so that it can then retrieve that data and display it
appropriately for that front end. This allows the database layer to be kept seperate from the
user interface layer of the application development. The Application itself does not need to
know how to query that particular database, but just simply expects that the results will be
presented back in a pre-determined format (i.e. it knows what columns to expect to be returned
by the ref cursor).
Summary
In summary, the one key thing to remember is..
REF CURSORS ARE NOT DATA CONTAINERS. ONCE OPENED,
THEY ARE SIMPLY A POINTER TO A QUERY THAT HAS YET TO FETCH DATA.
No comments:
Post a Comment