Re: create package

Karsten Farell <kfarrell_at_medimpact.com> wrote in message news:<vcFo9.1533$zs6.192978065_at_newssvr13.news.prodigy.com>...
> 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)
> >

Any reason you cannot use VARCHAR2(32767)??

More importantly... Why exactly you want to create a package on the fly? Only reason I could think of creating stuff on the fly is if you don't know it's behavor at design/compile time. Here is something you might want to consider,

1. If you are creating your package at runtime...then there must be something that you are changing in package specification/body every time you re-create it. In this case, what exactly are you changing in it? Package variables, Function/Procedure parameters, Number of Functions/Procedures etc etc. If it is this complicated...maybe you ought to look at your design one more time.
2. Is the package created for other processes to be used? If so, What about execute grants for other processes spawned by different users?

>but anyway, why shuld they complain if i create a package on the fly?

As you can see...lot of questions can come up. Can it be done? Yes. Should it be done? Analyze the problem and see if there is a simple solution that can be implemented at design time. A well thought out robust and simple solution is a winner any day over a complicated mess that no one can proof read. Specially someone who will manage your code after you have left the job.

Regards
/Rauf Sarwar Received on Tue Oct 08 2002 - 18:24:18 CDT