Using PL/SQL ref cursors

One of the best techniques for improving PL/SQL performance is the use of bulking operators (forall and bulk collect) and the design of PL/SQL to utilize ref cursors.

The ref cursor is a "pointer" data types that allows you to quickly reference any cursor result (usually an internal PL/SQL table array) with data values kept in super-fast RAM.

I'm a big fan of Dr. Hall, one of the world's top PL/SQL programming guru's, and I'm referencing his wonderful book "PL/SQL Tuning" where he has many ref cursor examples and ref cursor tips.

The name "ref cursor" can be confusing because a ref cursor variable is not a cursor, but a variable that points to a cursor. In essence, a ref cursor allows you to store the result of a "bulk collect" from a table (or any PL/SQL cursor return set) into a PL/SQL table (RAM array) and then reference the array with the ref cursor as a pointer.

Click below to learn more about using ref cursors in PL/SQL:

http://www.dba-oracle.com/t_pl_sql_ref_cursor_benefits.htm

For complete details, see Dr. Halls bestseller "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming":

http://www.rampant-books.com/book_2006_1_plsql_tune.htm

*****************************************

Need a Health Check?

You need to ensure that your mission-critical system is running at optimal performance, and BC can help.

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has an expert Oracle health check where we identify all database bottlenecks and certify the performance of your system.

Getting a health check is easy!  Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call me at 800-766-1884 for details, and check-out our on-site Oracle training catalog:

http://www.dba-oracle.com/bc-catalog.pdf