Path: news.easynews.com!easynews!crtntx1-snh1.gtei.net!cpk-news-hub1.bbnplanet.com!news.gtei.net!news.maxwell.syr.edu!sunqbc.risq.qc.ca!wesley.videotron.net!wagner.videotron.net.POSTED!not-for-mail From: Bill Thorsteinson Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools Subject: Re: Execute immediate problem Message-ID: References: <3CFE976E.8040103@xs4all.nl> X-Newsreader: Forte Agent 1.9/32.560 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 58 Date: Thu, 06 Jun 2002 22:01:53 -0400 NNTP-Posting-Host: 24.200.3.104 X-Complaints-To: abuse@videotron.ca X-Trace: wagner.videotron.net 1023415553 24.200.3.104 (Thu, 06 Jun 2002 22:05:53 EDT) NNTP-Posting-Date: Thu, 06 Jun 2002 22:05:53 EDT Xref: easynews comp.databases.oracle.misc:82611 comp.databases.oracle.server:149816 comp.databases.oracle.tools:51693 X-Received-Date: Thu, 06 Jun 2002 19:03:21 MST (news.easynews.com) On Thu, 06 Jun 2002 06:43:22 GMT, John Russell wrote: >When I asked someone in the know about big VARCHAR2 variables, they >mentioned declaring VARCHAR2(32500). Don't know if there's a reason >for going less than 32767 (overhead stored along with the variable?) >but you might try using a somewhat smaller length. > When I was researching types all the Oracle types where limited to a size several bytes shorter than the maximum value of the limiting type. Large blobs where limited to a few bytes short of 2Gb or 4Gb. In addition to reserving space for the counter inside the limit, it appeared they also reserved additional space. >BTW, declaring a big VARCHAR2 like this is also a good way to avoid >exceptions when concatenating values where you don't know the length >of the final result. Above a certain limit (about 4K), PL/SQL only >allocates as much memory as needed, rather than the full declared >length of the variable. So it's slightly slower but doesn't waste any >memory. > >John > >On Thu, 06 Jun 2002 00:57:50 +0200, 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) As noted above you are pushing past the limit for a 32k object back off a few bytes. 32750 should be small enough. >> If you are on 8i, I think varchar2 is limited to 2000-4000 characters. You may want to try a long or other extended length string. >>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