Re: create package

S2 wrote:
> hi.
>
> i want to dinamically create a package in a pl/sql procedure.
> to do this, i create a varchar2(4000) variable, write the code of the
> procedure in it, and then execute immediate the variable.
>
> my problem is the limit of 4000 chars, because it could be that the
> package body i want to create with execute immediate is a lot larger
> than 4000 chars, and i can't execute immediate a clob variable (doesn't
> work, seems to me that execute immediate does'nt support clobs as
> argument).
>
> any solutions?
>
> (i work on oracle 9i)
>

I assume you are creating the package specification and body in two separate statements (to reduce the size a little)? You might have to carry this step farther and create multiple, smaller packages that call each other. Kinda defeats one of the primary purposes of a package, though.

You could dynamically write a whole bunch of independent stored procedures ... but that really, really defeats the purpose of a package (and removes many of the nice features like overloading and such).

I haven't tried this ... and I don't feel inclined to sit down and type a 4000-byte package to test it ... but can you do something like slicing up the package into 4000-byte pieces, then:

execute immediate piece_1 || piece_2 || ... || piece_n;

I suspect the 'execute immediate' won't be able to handle the length either. But like I said, I haven't tried it ... so...

But my real question is: why? Is there some compelling reason to generate dynamic packages? Oh well, I assume the answer is yes. Received on Tue Oct 08 2002 - 13:10:35 CDT