Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic SQL to disable constraints.

Re: Dynamic SQL to disable constraints.

From: Ed prochak <>
Date: 22 Sep 2004 11:35:05 -0700
Message-ID: <> (Louis Frolio) wrote in message news:<>...
> Daniel Morgan <> wrote in message news:<1095730389.636770_at_yasure>...
[snip]> >
> > I want to add my name to the posting by Hans Forbrich.
> >
> > I too sometimes cringe at people's use the English but then I expect
> > more than a few cringe at mine. That said no one here is being paid
> > to do anything at all. Accept that which is offered with grace.
> Daniel, although I disagree with your opinion I respect what you have
> to say because you convey your opinion with respect. Others here are
> nothing less that than arrogant human beings who in my opinion derive
> some pleasure out of flaming people. What gets me is that what we do
> as DBA's is not rocket science, we are simply managing data and using
> a particular software to do it. I am utterly floored at times when I
> see these people strut their stuff like they are cock of the walk;
> again I say this is not rocket science. I want to once gain reiterate
> that my original post was exclusively about PL/SQL and not about dba
> best practices. If you read some of the posts to my question,
> especially the ones where the negativity shines through, you will see
> the response has nothing to do with PL/SQL.

Let's see exactly how negative the initial responses were: first Ana Dent:
>"it is failing"

Too bad you chose not to share with us the actual error message.

>The output properly displays the "alter datbase ..." command though?

What "alter DATABASE" command?

A backdoor way of asking what the real error was and pointing out a typo.
Boy that was a scathing rebuke! Would you agree that this one is neutral? (neither positive nor negative)

then Sybrand Baker:
>I think that I may need to employ
>dbms_sql but I am not sure.

execute immediate works without problem. I would urge you to look into better solutions, like to find out whether you can set the constraints to deferred. This method is, ahem, yes: crap.

first he noted your solution would work (implying it is a compile time problem), hinted at an alternative solution (defferred constraints), and ended with a remark about the first solution's ability to enhance plant growth. 8^)
yes the remark might be more diplomatically worded. Okay, let's call it negative.

then Daniel Morgan:
vString := 'ALTER TABLE job_history DISABLE CONSTRAINT ' || vConName; EXECUTE IMMEDIATE vString;

Your usae of the USING clause serves no purpose as vConName is not a bind variable and can not be a bind variable.

a more detailed response. this is clearly positive.

then Wario:
1. Remove ';' from the end of your string. Execute Immediate fails when commands are terminated with ';'.

Possibly the answer to your original question, but without the error message we don't know. But this too is positive.

On the negative one, you still got PL/SQL info.

> I just hope that people
> can learn to be more sympathetic to others and if they can't say
> something positive or relevant then they should just keep quiet. Even
> across cultural boundaries I would hope that this is self evident and
> within the boundaries of common sense, perhaps I wrong but hopefully I
> am not.
> With that said I will drop this.
> Regards, Louis Frolio

This is the way of newsgroups. IF you read the news.newusers posting, then you might know that you should browse a group for a bit before posting. Then you would know the culture of the group. It is not the group's responsibility to find multicultural-acceptable responses to posts. (especially since that may be impossible.) You would also know to search the group for the FAQ to see if it holds the answer to your question. And nowadays with GOOGLE and other search engines, you can search the group to see if your question was answered before.

Have you ever dealt with a gruff fellow employee at work? In the market? while driving?
Do you really think newsgroups are any different than those other areas of life?

Have a nice day, and keep posting.

   Ed. Received on Wed Sep 22 2004 - 13:35:05 CDT

Original text of this message