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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle PL/SQL How to concatenate rows into a single CLOB in PL/SQL Working with substitution variables and using EXECUTE IMMEDIATE in PL/SQL Converting Long Raw to Blob Parsing in Oracle ORA-01422 error when procedure returns more than one row Definition of force view ORA-04082: NEW or OLD references not allowed in table level triggers Execute SQL statement from table in other schema Script to revoke access from user PL/SQL procedure to load CSV file into database table Using Oracle PL/SQL Oracle tutorial library: SearchOracle.com's learning guides SELECT statement syntax and examples Oracle PL/SQL tutorial PL/SQL datatypes in Oracle Stored procedures in PL/SQL PL/SQL functions and triggers in Oracle How to concatenate rows into a single CLOB in PL/SQL Working with substitution variables and using EXECUTE IMMEDIATE in PL/SQL Oracle's free SQL Developer adds database migration tool Confused about Oracle certification exams

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary PL/SQL  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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).