Re: Get Package, Re-write, Re-compile...

Perhaps something to try. Notwithstanding the fact that you must be careful when executing DDL with the dbms_sql package, there is (at least in the 10G version) an overloaded 'parse' function which takes an array of varchar2 in lieu of the customary single varchar2 as the text for the statement (basically, you have an array with one row per line of statement). The comments say that 'conceptually' it behaves as if the various elements were concatenated. The question is whether the concept extends as far as breaking at 32K. Because otherwise you can probably go as far as 32K rows of 32K each (I hope you wont blow the limit ;-)). I would have a look there if I were you.

SF

Post, Ethan wrote:

>OK, this one is going to AskTom as soon as his queue clears up. Would
>anyone actually want his job? We need to put Tom in for a Nobel prize
>or something, he exhibits an amazing amount of patience and effort with
>ol AskTom.
>
>Will keep you posted.
>
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Post, Ethan
>Sent: Thursday, November 18, 2004 2:38 PM
>To: oracle-l_at_freelists.org
>Subject: Get Package, Re-write, Re-compile...
>
>Here is the situation, I have written a PL/SQL package that grabs source
>code for other packages from DBA_SOURCE, goes through each line, inserts
>instrumentation among other things, and then inserts that source into a
>temporary table. This can all be accomplished using a simple call to
>the package. However, to recompile the new package I spool out the new
>source to a file and run the file, this happens via SQL*Plus.=3D20
>
>Ideally I would like to recompile the new package using PL/SQL however I
>think DBMS_SQL has a limit of 32K and I think varchar2 limit is also 32K
>which I could use for execute immediate. Some of these packages are >
>1MB in size. Besides writing some sort of java/c wrapper can anyone
>think of a way to do this?
>
>Thanks!
>
>

--
http://www.freelists.org/webpage/oracle-l