Adam schrieb:

> To answer directly your question. You need to commit after making any
> DDL changes before executing DML. It is a known source of data weirdness.

Thanks, that's what I wanted to know.

> > If I'm right, does this have a reason?
>
> Some things are bigger than transactions, indexes are global for
> instance. What about constraints? If you were to add a unique
> constraint, and another user is active, should they be allowed to add
> a duplicate to the table before you commit? What about foreign keys?
> All of these items are new restrictions other transactions need to
> rely on.

Now it's getting a little religious: "There are bigger things in life,
my son..." :-)
The problem you describe is one you are bound to have anyway unless you
have exclusive access. If you don't, the problem cannot be fixed by the
above mentioned rule. You always have one moment in time when the new
database object comes into existence, regardless if it's the moment of
committing the creation or the moment of creating it. So, I still don't
see the necessity of the rule.

> General running of your system should not require DDL changes,
> certainly they may be required if a fix or enhancement is made, so
> this approach should work fine.

I was talking about enhancements. I have several hundred customers using
my client application and its database, and if there is a new feature
(and sometimes this goes along with creating new tables and
constraints), all of them must have their databases updated. It would be
nice if I could pack the whole updating process into one transaction and
afterwards have either the older version (in case of an error) or the
newer version, but not something inbetween (e.g. the table has been
created, but the foreign keys haven't been applied etc.).

Stefan