Path: news.easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newshub.sdsu.edu!west.cox.net!cox.net!news-east.rr.com!wn2feed!worldnet.att.net!204.127.198.204!attbi_feed4!attbi_feed3!attbi.com!sccrnsc02.POSTED!not-for-mail From: John Russell 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.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 51 NNTP-Posting-Host: 12.232.220.154 X-Complaints-To: abuse@attbi.com X-Trace: sccrnsc02 1023345802 12.232.220.154 (Thu, 06 Jun 2002 06:43:22 GMT) NNTP-Posting-Date: Thu, 06 Jun 2002 06:43:22 GMT Organization: AT&T Broadband Date: Thu, 06 Jun 2002 06:43:22 GMT Xref: easynews comp.databases.oracle.misc:82563 comp.databases.oracle.server:149701 comp.databases.oracle.tools:51670 X-Received-Date: Thu, 06 Jun 2002 01:20:32 MST (news.easynews.com) 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. 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) > >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 -- Got an Oracle database question? Try the search engine for the database docs at: http://tahiti.oracle.com/ The opinions expressed above are mine and do not necessarily reflect those of Oracle Corporation.