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
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
Expert Dan Clamage explains how to use SQL SELECT and SQL UNION ALL statements to sort and visualize a set of sales figures. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.