Re: nasty one...

Hello Niall,

I agree with what you just said. The
problem is that the procedure to be
deployed in the Database is PL/SQL
automatically generated from a compiler.

The user provides a rule, the rule is compiled with a Java parser (JavaCC parser deployed as JSP) which generates a PL/SQL procedure that represents the source code of the rule.

The code I generate from within Java is passed back as a CLOB (huge PL/SQL) then since was very dark how to make an "execute immediate" from PL/SQL passing a clob and of course converting to char is not possible because of lossing data then I created another java procedure JSP that receives the CLOB and excutes it as DDL within the current user.

The procedure is created but with compilation errors when it does not have compilation errors. The ALTER PROCEDUTE <X> COMPILE does not work as the very exactly same generated thing re-executed from sqlplus compiles...

Best regards,
Giovanni

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3fd0c106$0$23820$cc9e4d1f_at_news.dial.pipex.com...
> In general (and there is very little detail in your post) doing DDL within
a
> stored procedure is a pretty good sign of bad coding.
>
> Your objects should already exist and the relevant users given access to
> them.
>
> Is there any chance of seeing what you are actually doing?
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
> "Giovanni Azua" <bravegag_at_hotmail.com> wrote in message
> news:bqqa1n$25us2d$1_at_ID-114658.news.uni-berlin.de...
> > Hello all,
> >
> > I have a PL/SQL procedure within user X. This
> > procedure calls another procedure JSP say "A"
> > also within X but this X java procedure does have
> > to execute a DDL which is passed as a CLOB within
> > ANOTHER user connection...
> >
> > The question is whether from PL/SQL or Java how do
> > I get a connection from user X to user Y given that
> > I am within X. The thing gets even more complicated
> > because chances are that Y is defined as a link to
> > a remote database...
> >
> > No way to hardcode or store the thin connection
> > params.
> >
> > Best regards,
> > Giovanni
> >
> >
>
>
Received on Fri Dec 05 2003 - 12:19:21 CST