| Weak and Strongly Typed Reference Cursors [message #411435] |
Fri, 03 July 2009 10:15  |
wtolentino
Messages: 421
Registered: March 2005
|
Senior Member |
|
|
Our custom reports has been using a reference cursor. I have inherited the code and not sure why there is a need to use a reference cursor when it can be done by a simple select statements. I see no dynamic select statements passed or any special parameters to reason out for use of reference cursor in our custom reports. Prior developers had reason out the benefits of using reference cursor for performance. I tested this by running a report with reference cursor versus plain select statement and still the report that is using the plain select statement performed better (faster) than the report that has the reference cursor there is a big difference.
I have seen some disadvantage of using reference cursor on the reports that when there is a database object change even if the package that sourced the reference cursor has not been updated or modified the reports needs to be recompiled each time we get this error message coming from the report server queue:
Terminated with error: <br>REP-8: Run time error in the PL/SQL development
environment (DE). PDE-PSD001 Could not resolve reference to <Unknown Program Unit>
while loading <Unknown> <Unknown>. REP-0008: Unexpected memory error while
initializing preferences.
In 9iAS reports prior version the error is occurring rarely. When we moved to 10.1.2.2.0 reports it appears the error occurs most often. We have made an effort to research about the issue and appears to be a bug. One suggestion is to use a strongly typed reference cursor. I have tried to researched about the difference between a weak and strongly typed reference cursor but failed to understand them. I appreciate any help about examples differentiating a weak versus a strongly typed reference cursors.
Thanks,
Warren
|
|
|
|
| Re: Weak and Strongly Typed Reference Cursors [message #411441 is a reply to message #411435] |
Fri, 03 July 2009 11:27  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
the documentation describes pretty well what the difference is. Still given you must be finding the documentation a bit hard to understand, here is my attempt at describing the difference between a weekly typed refcursor and a strongly typed refcursor.
A weekly typed refcursor can hold any select statement.
A strongly typed refcursor must hold a select statment who's returned rows match the type the cursor is defined to.
There are three basic difference between the two:
1) a weekly typed refcursor can be quickly created using the oracle supplied SYSTEM type SYS_REFCURSOR. Whereas the strongly typed refcursor can only be created after first using the old style package type declaration of some kind.
2) a weekly typed refcursor enforces little if any restrictions on the data in its returned rows. Whereas a strongly typed refcursor enforces basic type validation on each column in the refcursor.
3) a strongly typed refcursor cannot be used to open DYNAMIC SQL. So, using a variable to hold a select statement and then trying to open a strongly typed refcursor with the variable will fail with an error, even if the select statement in the variable matches perfectly the type definition of the strongly type refcursor.
#2 above means that in practice, a stongly typed refcursor will enforce a compile time validation on the sql statement used to open the refcursor against its type definition. Hence code using strongly typed refcursors will not compile if the sql statement and the refcursor do not agree in number of columns and
convertability of base datatypes.
Here are some examples:
declare
rc1 sys_refcursor;
begin
open rc1 for select * from dual;
close rc1;
open rc1 for select a.*,b.* from dual a,dual b;
close rc1;
open rc1 for select 1 x,2 y,3 z from dual;
close rc1;
open rc1 for select 'a' x,'b' y,'c' z from dual;
close rc1;
end f_week_refcursor ;
/
Notice the above anonymous block can define a refcursor easily using SYS_REFCURSOR. This is a weekly typed refcursor because there is no restriction on what select statement can be associated to the refcursor. The above shows opening the same refcursor with select statements that clearly have different numbers of columns and different datatypes for those columns.
But the following shows a strongly typed refcursor.
create or replace package pkg_strong_refcursor is
type strong_refcursor is ref cursor return dual%rowtype;
procedure test_refcursor;
end pkg_strong_refcursor ;
/
show errors
create or replace package body pkg_strong_refcursor is
v1 varchar2(3);
procedure test_refcursor is
rc1 strong_refcursor;
begin
open rc1 for select * from dual;
close rc1;
open rc1 for select 'X' dummy from dual;
close rc1;
open rc1 for select 1 dummy from dual;
close rc1;
open rc1 for select 'a' from dual;
close rc1;
open rc1 for select 1 from dual;
close rc1;
-- open rc1 for select 1,2 from dual;
-- close rc1;
open rc1 for select 'XXX' dummy from dual;
fetch rc1 into v1;
close rc1;
-- open rc1 for v2;
-- close rc1;
end test_refcursor ;
end pkg_strong_refcursor ;
/
show errors
Notice it is necessary to use a PLSQL PACKAGE to define the refcursor and that the refcursor is given a specific return type. This refcursor must return rows that map to the table dual. Dual as you know is defined as follows:
SQL> set linesize 80
SQL> desc dual
Name Null? Type
----------------------------------------- -------- ---------------------
DUMMY VARCHAR2(1)
The two open/close pairs in the package body that you see as comments, will each generate one of the two errors discussed above. The select statment "select 1,2 from dual" has two columns in it, and the refcursor was defined as a "record type" that has only one column. This will generate a compile time error. Uncomment the lines and try to see for yourself. The open rc1 for v2 trys to do dynamic sql which is illegal with strongly typed refcursors and will also generate a compile time error. Again, try to uncomment the relevent open/close and compile the package body to see this error.
There are some other notes too. Notice that all other select statements not commented out in the package body are valid both at compile time and run time. We see from this that Oracle is OK with columns defined against a strongly type refcursor as long as their datatypes can be converted at compile time to the type as defined by the refcursor record type. Thus those sql statements above that return one column of type number are OK because a number can be converted to a string. This should clue you however, to one issue with strongly typed refcursors, that implicit datatype conversion will occur as necessary and you cannot stop it.
Another note is the sql statement that returns the value 'XXX'. This statement will succedd with all the other uncommented sql if you compile and run the package procedure.
exec pkg_strong_refcursor.test_refcursor
This tells us that although the refcursor was define as a record type based on table dual and thus has one column of type varchar2(1), oracle did not care that the data being returned was three bytes wide. Thus it seems at first blush that strong typing might should have been called average typing or sort-of-strong typing because it allows values that do not actually fit the size of the original datatypes. At least that is how it seems from this code.
I have no information as to if a strongly typed refcursor is more efficient than a weekly defined refcursor. Maybe you could run a test for that and tell us.
Also, you are correct, a regular cursor is much more efficient than a refcursor and should always be used instead. The only caveat I can think of is when passing the cursor around, you must use a refcursor which was I believe why the refcursor was invented in the first place. So, if you are passing a cursor variable to a java program, you must use refcursor because refcursor = cursor variable.
Hope this help, anyone else have comments?
My last comment would be this. 99% of the time, using a cursor is bad. Almost every situation where a cursor could be used, a plain jane sql statement could be use. A cursor (regular or refcursor) each implies row by row processing of data. This is by its nature slow already. Using single SELECT,INSERT,UPDATE,DELETE to process data rather than a cursor of some kind will always be faster.
Just so no one jumps on me, I agree, from time to time a cursor can make processing of data easier, and there are times when row by row processing is desireable. Additionally, refcursors are a simple way to pass data from oracle to the outside would to .NET apps and JAVA apps so they will see heavy use in this regard.
However, for many developers, cursors have become a crutch. You are using the world's best relational database software, stop with the crutches and start doing it right.
Kevin
[Updated on: Fri, 03 July 2009 11:49] Report message to a moderator |
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
