Ref Cursor - 1

Is ref cursor the only light at end of the tunnel?

Consider a requirement: Based on the given condition, the base table of the query will change whereas select statement will remain intact.

Say for example we need to select
Account number,
Dr_Cr ,
Currency and
Amount

From Account_Posting_USD table when condition Local_Currency is True or
From Account_Posting table when condition Local_Currency is False.

In Static cursor I cannot parameterize the table name but still this
is a simple requirement if you are aware of dynamic cursor/REF cursor!!! Isn’t it?

So let’s implement this with ref cursor.

CREATE TABLE Account_Posting (
Posting_Txn_Ref INTEGER,
Account_Number NUMBER,
Dr_Cr VARCHAR2(1),
Currency VARCHAR2(3),
Amount NUMBER,
CONSTRAINT Account_Posting_PK PRIMARY KEY
(Posting_Txn_Ref));

CREATE TABLE Account_Posting as SELECT * FROM Account_Posting_Usd;

CREATE OR REPLACE PROCEDURE Accounting_Refcur_Example(i_Local_Ccy BOOLEAN) AS
Ret_Val SYS_REFCURSOR;
l_Val Account_Posting%ROWTYPE;
BEGIN
-- IF local Currency is true means select from Account_Posting_Usd table.
IF i_Local_Ccy THEN
OPEN Ret_Val FOR
SELECT Account_Number,
Dr_Cr,
Currency ,
Amount
FROM Account_Posting_Usd;
ELSE
OPEN Ret_Val FOR
SELECT Account_Number,
Dr_Cr,
Currency ,
Amount
FROM Account_Posting;
END IF;
LOOP
FETCH Ret_Val INTO l_Val;
EXIT WHEN Ret_Val%NOTFOUND;
Dbms_Output.Put_line(l_Val.Account_Number);
END LOOP;
CLOSE Ret_Val;
END Accounting_Refcur_Example;


Problem at hand solved. But is this implementation more efficient?

While looking behind the hoods of PL/SQL ref cursors are just pointers to the cursors (Query result set). They are not parsed at “Compile Time”.

More importantly the queries are not parsed and stored in Shared Pool. This means
each time the query will under go hard parse (plan and row data generation will occur again).

For more details on hard/soft parse refer :
http://asktom.oracle.com

Question for your thought:
1. What are the other ways to implement the same requirement?

Word of Caution:
Though this performance burden is there on ref cursor, it is advisable to use ref cursor to pass between procedures, between client/server applications etc..