Oracle Package inside a CLOB with length > 32767 characters. How to "execute immediate" it?

Question

Please suppose that I have a package creation script stored inside a table ALPHA, in a column BETA of type CLOB. The CLOB length is > 32767 characters.

Using PL/SQL code, I would like to "execute immediate" the package creation script.

I could I achieve this?

Thank you in advance for your kind help.

I am using Oracle 10G

Answer 1

execute immediate didn't support CLOBs until 11gR2. You can use DMBS_SQL to handle larger statements. In earlier versions you could build up the statement, but 11g allows you to parse a CLOB.

There is an example here.

Creating a package dynamically seems like an odd requirement though.

---

Since you're on 10g, you will need to use the version of DBMS_SQL.PARSE that lets you build up large statements:

The PARSE procedure also supports the following syntax for large SQL statements:

DBMS_SQL.PARSE ( 
   c                  IN   INTEGER, 
   statement          IN   VARCHAR2S, 
   lb                 IN   INTEGER, 
   ub                 IN   INTEGER, 
   lfflg              IN   BOOLEAN, 
   language_flag      IN   INTEGER); 

Note: The procedure concatenates elements of a PL/SQL table statement and parses the resulting string. You can use this procedure to parse a statement that is longer than the limit for a single VARCHAR2 variable by splitting up the statement.

An example of that approach here.