Oracle EXECUTE IMMEDIATE into a cursor

Question

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?

Frecklefaced

How long is "very long"? More than 32K? – Mew

Answer 1

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.

Crab