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

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 6 Jun 2002 06:44:54 -0700
Message-ID: <adnp0m0tma@drn.newsguy.com>

In article <3CFF0382.6070909_at_xs4all.nl>, Geert says...
>
>
>
>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
>
>
>

I did not see the original post -- however, I can only guess that - your scripts under 2000 bytes were DIFFERENT then the ones over 2000 bytes. They attempted to do something you did not have the privelege to do!

Try this example on your system:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare   2 l_stmt varchar2(32767);
  3 begin

  4          l_stmt := 'declare
  5                                   i number := 0;
  6                             begin ';
  7  
  8          for i in 1 .. 3000
  9          loop
 10                  l_stmt := l_stmt || 'i := ' || i || ';';
 11          end loop;
 12  
 13          l_stmt := l_stmt || ' end;';
 14  
 15          dbms_output.put_line( 'length = ' || length(l_stmt) );
 16          execute immediate l_stmt;

 17 end;
 18 /
length = 28938

PL/SQL procedure successfully completed.

Most likely, what is happening is you have the execute immediate buried in a procedure and you are trying to use a priv you have via a role.

http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Jun 06 2002 - 08:44:54 CDT