Oracle EXECUTE IMMEDIATE into a cursor

sql oracle stored-procedures dynamic-sql sys-refcursor
Ankit picture Ankit · Jan 17, 2011 · Viewed 38.6k times · Source

I have a stored procedure which used the EXECUTE IMMEDIATE command to execute a very long string. How do I support a very long string and return the data into a refcursor?

Answer

Allan picture Allan · Jan 18, 2011

Assuming that your SQL is not longer than 32K (as @Tony Andrews hinted at), you should be able to use something like this:

declare
   SQL_Text varchar2(32760) := 'select * from dual'; --your query goes here
   cur sys_refcursor;
begin
   open cur for SQL_Text;
end;

When working with Ref Cursors, open-for can be used directly, instead of execute immediate.

Related questions

Searching for Text within Oracle Stored Procedures

I need to search through all of the stored procedures in an Oracle database using TOAD. I am looking for anywhere that the developers used MAX + 1 instead of the NEXTVAL on the sequence to get the next ID number. I've …

Read More
Inserting into Oracle and retrieving the generated sequence ID

I have a handful of raw SQL queries for SQL Server which use SCOPE_IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution… INSERT into Batch( BatchName, BatchType, Source, Area ) …

Read More
Paging with Oracle

I am not as familiar with Oracle as I would like to be. I have some 250k records, and I want to display them 100 per page. Currently I have one stored procedure which retrieves all quarter of a million records …

Read More