Path: news.easynews.com!easynews!news-xfer.siscom.net!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed1.uni2.dk!news.get2net.dk!not-for-mail User-Agent: Microsoft-Outlook-Express-Macintosh-Edition/5.02.2022 Subject: Re: Execute immediate problem. Dear Mr. Morgan... From: MacMadsen Newsgroups: comp.databases.oracle.server Message-ID: References: <3CFE976E.8040103@xs4all.nl> <3CFEEA2F.EDD97C06@exesolutions.com> <3CFF0382.6070909@xs4all.nl> In-Reply-To: <3CFF0382.6070909@xs4all.nl> Posted-And-Mailed: yes Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit Lines: 109 Date: Thu, 06 Jun 2002 12:00:40 +0200 NNTP-Posting-Host: 192.38.129.130 X-Complaints-To: abuse@uni2.dk X-Trace: news.get2net.dk 1023357641 192.38.129.130 (Thu, 06 Jun 2002 12:00:41 MET DST) NNTP-Posting-Date: Thu, 06 Jun 2002 12:00:41 MET DST Organization: UNI2 Internet Kunde Xref: easynews comp.databases.oracle.server:149721 X-Received-Date: Thu, 06 Jun 2002 04:27:14 MST (news.easynews.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@xs4all.nl, Geert Roelof at grploeg@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 > > >