Manage Learn to apply best practices and optimize your operations.

How to open a ref cursor in a PL/SQL procedure

Get tips from new PL/SQL expert Dan Clamage on how to use a ref cursor correctly when executing a PL/SQL procedure.

Here's my question: I drop and create an existing table in PL/SQL proc using EXECUTE IMMEDIATE. Later I declare a ref cursor, to process data from the table created. The problem is, the procedure executes correctly, but after execution the procedure becomes invalid. How can I solve this issue?
It's a really bad idea to drop and recreate a table from an application, for a number of reasons. The biggest reason is because it's a relatively expensive operation. If this is part of a data mart or data warehouse load, then it's not so bad – there are other more expensive processes going on there.

If the table structure doesn't change, consider truncating the table instead. If a foreign key points to this table then you'll have to temporarily disable the foreign key first. See my stored procedure control_fkeys for how to do this.

If you open a ref cursor for a query that uses the table, drop the table and then try to fetch from the ref cursor, your results cannot be guaranteed. You'll likely get an error about an inconsistent fetch or invalid cursor, or a fetch out of sequence. This may happen sporadically or not at all for small data volumes, then suddenly occur with large result sets. The reason for this is related to the way Oracle marks data blocks "dirty." This is another reason why dropping and recreating the table should be avoided.

Oracle always invalidates program units whenever a change is made to an underlying object. When you next reference the procedure, Oracle will automatically attempt to recompile the procedure. Note the last_ddl-time column of the data dictionary user_objects for your procedure. I bet it keeps changing, indicating a recompile.

The only way to avoid this is to open a ref cursor for a query dynamically – that is, a string representing your query:

v_stmt := 'select col1, col2, col3 from some_table where key = :k';
v_key := 3;
open v_ref for v_stmt using v_key;

The table is only known at run-time, so the procedure won't get invalidated (so long as there are NO references to it).

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.