Oracle and PL/SQL Recipes: A Problem-Solution Approach

8-11 Storing Dynamic SQL in Large Objects

Problem

The SQL code that you need to assemble at runtime is likely to exceed the 32KB limit that is bound to VARCHAR2 types. You need to be able to store dynamic SQL text in a type that will allow more for a large amount of text.

Solution #1

Declare a CLOB variable, and store your SQL string within it. After the CLOB has been created, execute the SQL. This can be done using either native dynamic SQL or the DBMS_SQL package. For the example, assume that a block of text is being read from an external file, and it will be passed to a procedure to be processed. That text will be the SQL string that will be dynamically processed within the procedure. Since the external text file can be virtually any size, ...