Re: Very Large Dynamic SQL

Craig wrote:
> 10gR2
>
> In my app, I've been using "OPEN some_cursor FOR some_dyn_sql ;"
>
> Now I'm facing a limitation problem.... I have to generate a VERY LARGE
> dynamic SQL.
> (bigger than "VARCHAR2(32767)")
>
> And "OPEN...FOR..." does not seem to take CLOB.
>
> What other options do I have ?
>
> Thanks !

Simple solution ... use DBMS_SQL.

DBMS_SQL has had, for many generations, the ability to run dynamic SQL statements of near infinite size. You will find demo code for this on my website, Morgan's Library, at www.psoug.org.

Click on DBMS_SQL.
Then scroll down to: Executing CLOBS Demo Tables Create the tables then build the execute_plsql_block procedure. It may not be pretty but it can easily handle a 3GB dynamic SQL statement.

A variation on the theme is currently production code in a commercial CRM application.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org