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

From: MacMadsen <jesper_madsen_at_mac.com>
Date: Thu, 06 Jun 2002 12:00:40 +0200
Message-ID: <B924FF68.90D6%jesper_madsen@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
>
>
>
Received on Thu Jun 06 2002 - 05:00:40 CDT