Re: Execute immediate problem. Dear Mr. Morgan...

MacMadsen <jesper_madsen_at_mac.com> wrote in message news:<B924FF68.90D6%jesper_madsen_at_mac.com>...
> Can it be a goofy error message you get because the server only tries to
> execute the first 2000 chars as a script?? does V_Script contain the whole
> script?
>
> There are a limit on the SQL string you send to Oracle and as far as i know,
> that limit is around 2k. What we have been used to doing is, if we need to
> set data larger than 1k, we use a bind variable, but i'm not sure you can
> execute a bind variable... execute immediate (:mybind), you might be able to
> wrap it into a PLSQL procedure and execute it in the database, but not
> through execute immediate, since it handles dynamic sql...
>
> Hope this helps
>
> in article 3CFF0382.6070909_at_xs4all.nl, Geert Roelof at grploeg_at_xs4all.nl
> wrote on 06/06/02 8:38:
>
> >
> >
> > Daniel Morgan wrote:
> >
> >> Geert Roelof wrote:
> >>
> >>
> >>> Hello,
> >>>
> >>> I build a pl/sql procedure which generates pl/sql. I use a dummy script
> >>> and some variables. Those variables are the replace and the whole
> >>> generated pl/sql code is executed by using EXECUTE IMMEDIATE (V_SCRIPT)
> >>>
> >>> V_Script is defined as varchar2(32767)
> >>>
> >>> The following problem occurs. When I generate a script which is less
> >>> then 2000 characters long everything works fine. But when i generate a
> >>> script which is larger then 2000 characters it fails with the error: Not
> >>> enough privileges. (?) I check the code and its correct, but i cannot
> >>> get it into the database with an execute immediate.
> >>>
> >>> The documentation states that execute immediate can handle scripts as
> >>> large as 32K so what is happening here?
> >>>
> >>> Does anyone out there have a clue?
> >>>
> >>> Any suggestions are welcome
> >>>
> >>> Thanks in advance
> >>>
> >>> G.R. van der Ploeg
> >>> Senior developer
> >>> Geove/RZG
> >>>
> >>
> >> I don't mean to be harsh here but this is a rant that has been repeated
> >> many times in this group.
> >>
> >> I am stricl by the fact that under your name you wrote "Senior developer"
> >> but you didn't think it important to give us the actual error message.
> >> Neither do I see a platform, an operating system, or an Oracle version. Any
> >> chance you could make it easier on those that might wish to help you?
> >>
> >> And as long as I am both trying to help you and ranting a bit ... please
> >> don't multipost. This message should only be posted to c.d.o.server. It is
> >> not miscellaneous and it has nothing to do with Oracle tools.
> >>
> >> Daniel Morgan
> >>
> >>
> >
> >
> > So you want some more information?
> >
> >> I don't mean to be harsh here but this is a rant that has been repeated
> >> many times in this group.
> >
> > I dont know with you but i don not search every time through the whole
> > newsgroup. So if it is repeated and a known problem why not just state
> > that fact and give me a help.
> >
> >> I am stricl by the fact that under your name you wrote "Senior
> >> developer"
> >> but you didn't think it important to give us the actual error message.
> >
> > I thought i wrote the error was 'not enough priviliges' . But if you
> > want a number thats fine with me. I posted this message from my home and
> > not from the desk i work. I did it from memory. The error should be:
> > insufficient privileges. Just don't tell me i am not a senior developer
> > because i don't give you all the numbers. The problem can be a general
> > one, and i didn't find anything on metalink. Besides that fact I am not
> > a DBA so i just don't know all of the hardware facts.
> >
> > The numbers mr. Morgan:
> >
> > ORA-1031: insufficient priviliges
> > Platform UNIX
> > but then you want to know the exact version and which flavour I
> > suppose
> > RDBMS 8.1.7
> > Hardware I don't know but if you insist on getting the numbers right
> > then i need to ask a DBA for that information.
> >
> > Finally, as you can see: for your convenience i only posted it on
> > cdo.server.
> >
> > With regards,
> >
> > G.R. van der Ploeg
> >
 

GR, I double checked Metalink and was also unable to find any reports that match this problem. So here are two possibilities to check:

1 - The long SQL does not inadvertantly end in a semicolon ';'

2 - That the procedure executing the 'execute immediate' owner has full directly granted (not inherited through role) object level privileges on all tables in the SQL statement

3 - If the SQL or part of it is being passed in that this information is not being truncated prior to being used to construct the SQL statement

HTH -- Mark D Powell -- Received on Thu Jun 06 2002 - 09:39:15 CDT