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

Geert Roelof wrote:

> 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

With 8.1.7 a VARCHAR2 is 32K so I don't think the variable is the issue.

Others, many more competent than I, have given their input below so I won't rehash their suggestions. And I know of no Oracle privilege that relates to the length of a string.

Here's what I would do if I were in your position.

1. Build a table with a single VARCHAR2(4000) field.
2. Pass the string and insert it into the table. Substring it into multiple records if need be.
3. Examine it carefully, character by character, to make sure it is what you think it is.
4. Spool it to an ASCII file, open in a text editor, and send it to a printer. or
5. Skip the insert altogether and use UTL_FILE to write it to an operating system file.

I worked on an XML application that was capturing all kinds of interesting things in a passed string that were invisible to the unaided eye. Little things the C programmers didn't think were important like new-line and end-of-file characters.

Another possibility ... log on as SYS - SYSDBA with all the privileges there are ... and try running it. If it fails that pretty much answers any questions about system privileges being part of the problem.

Let us know what you find out.

Daniel Morgan Received on Thu Jun 06 2002 - 15:40:36 CDT