Re: Execute immediate problem

On Thu, 06 Jun 2002 06:43:22 GMT, John Russell <netnews3_at_johnrussell.mailshell.com> 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 <grploeg_at_xs4all.nl>
>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
Received on Thu Jun 06 2002 - 21:01:53 CDT