Just a few simple thoughts...
Can we step back, I'd like to know why doing a commit on creating a
table becomes an issue.
We often use 'execute immediate' to release different version of our
software, providing the flexibility to conditionally create/drop/alter
database object definitions.
Our software upgrade process also includes sections to
load/update/delete data in configuration tables.

We have a requirement that the whole database be backed up before the
upgrade process is initiated, so the database can always be brought
back to the image prior to starting the upgrade.
On a few rare occasion we have also choosen to create a backup of a
specific table (which could be done with something like ...
Select count(*) in my_variab from user_objects
where object_name = 'SPEC_TAB';
if my_variab > 0 then
execute immediate 'Create table SPEC_TAB_BAK as select * from
SPEC_TAB';
execute immediate 'Drop table SPEC_TAB cascade constraints';
execute immediate 'Create table SPEC_TAB ( ...
end if;

If later on a condition occurs that requires that you roll back a
number of the changes,
it can all be done programatically (execute immediate 'Drop table ...')

The short answer to 'can you rollback to a point partially through the
installation' - no - not automatically.

This does not cause us a major issue, since we typically do the
complete installation, or in a disaster case
roll back everything, using a backup.

HTH.

peter.koch.larsen@gmail.com wrote:
> But what if my transaction requires the creation or destruction of ten
> tables? This is what I wrote above, this is what the software does and
> this what caused it to fail: you can't abort it after having created
> the first five tables as those table alerady have been created.
>
> /Peter

In almost 20 years of Oracle development and DBA work I've NEVER seen a
business requirement to create and destroy tables on-the-fly.

If you are trying to duplicate SQL Server in Oracle you are sowing the
seeds of your own destruction. You need to pick up a copy of Tom Kyte's
"Expert one-on-one Oracle" and read it.

What I see is the forced implementation of an architecture that is at
odds with best practice. What is the business requirement as opposed
to your proposed solution.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

peter.koch.larsen@gmail.com wrote:
> Sybrand Bakker skrev:
>
>> On 9 May 2006 14:09:39 -0700, peter.koch.larsen@gmail.com wrote:
>>
>>> Right. But thus time it is not a feature, but a limitation that is not
>>> as i see it is not in any way based on some requirement for logical
>>> consistency - more like the opposite if anything.
>> It rather seems you have a basic lack of understanding of relational
>> and transactional concepts. You probably would better stick to your
>> Mickey Mouse products.
>
> Excuse me, but this is simply false. If you simply must come with
> personal attacks instead of giving evidence to support your cause you
> really should reserve that "Mickey Mouse" adjective to describe your
> own mindset.
>
> /Peter
>
>
>> --
>> Sybrand Bakker, Senior Oracle DBA

Actually Peter my instinct here is to agree with Sybrand. Everything
you've written flies in the face of good practice in an Oracle database.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

peter.koch.larsen@gmail.com wrote:

> The table creation has nothing at all to do with temporary tables. The
> fact is that when we deliver our software we have no idea what tables
> there should be.
>
> /Peter
>> Jim

This statement is horrifying. I read your original post and reread it
again just now. It appears that faced with a business problem you
chose a solution independent of best practice and independent of the
architecture of products, such as Oracle, on which you might implement
it.

I'd suggest that you both reconsider you choices and reconsider the
above statement. The above statement is so bad that I have kept a
copy of it to present to my Oracle students as an example of why
projects fail. You've earned the dubious honor of being a "bad example"
at the University of Washington (though I did remove your name from
the slide).

Seriously ... your statement equates with ... I don't care about
security, stability, scalability, performance, or maintainability.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan skrev:

> peter.koch.larsen@gmail.com wrote:
>
> > The table creation has nothing at all to do with temporary tables. The
> > fact is that when we deliver our software we have no idea what tables
> > there should be.
> >
> > /Peter
> >> Jim
>
Hi Daniel

> This statement is horrifying. I read your original post and reread it
> again just now. It appears that faced with a business problem you
> chose a solution independent of best practice and independent of the
> architecture of products, such as Oracle, on which you might implement
> it.
First let me get this straight with you. This is not a new product as
it has been in production for probably more than fifteen years. It
currently runs on a wide variety of databases and operating systems all
over the world. And it is by most regarded as the market leader in its
domain.
It is also important to add- in case you haven't realised this already
- that this software is not shrink wrap software. We do not sell a new
product every day - and probably not one each month. Also I should add
that the configuration is not performed by ordinary end-users, but by
highly skilled users - most likely IT-professionals in cooperation with
domain specialists.
>
> I'd suggest that you both reconsider you choices and reconsider the
> above statement. The above statement is so bad that I have kept a
> copy of it to present to my Oracle students as an example of why
> projects fail.

As you might guess, this is not a failed product, but a market leading
and highly successful product that thrives very well in its niche.

> You've earned the dubious honor of being a "bad example"
> at the University of Washington (though I did remove your name from
> the slide).

Honor the fool ;-)
So please be explicit when critizicing our product. So far as I guess,
our solution will not differ from e.g. large accounting system such as
SAP where customisation is a major part of the product. Also how could
shrink-wrapped software work properly on Oracle? Typically there will
be an installation phase where the tables are created. If this fails
midway (or the power fails), the poor programmers will now have to
manually undo the part that actually made it. Most unsatisfactory if
you ask me.

>
> Seriously ... your statement equates with ... I don't care about
> security, stability, scalability, performance, or maintainability.

Please motivate that statement. I have a really har time detecting
what e.g. security has to do with all this.

Kind regards
Peter
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

DA Morgan skrev:

> peter.koch.larsen@gmail.com wrote:
> > Sybrand Bakker skrev:
> >
> >> On 9 May 2006 14:09:39 -0700, peter.koch.larsen@gmail.com wrote:
> >>
> >>> Right. But thus time it is not a feature, but a limitation that is not
> >>> as i see it is not in any way based on some requirement for logical
> >>> consistency - more like the opposite if anything.
> >> It rather seems you have a basic lack of understanding of relational
> >> and transactional concepts. You probably would better stick to your
> >> Mickey Mouse products.
> >
> > Excuse me, but this is simply false. If you simply must come with
> > personal attacks instead of giving evidence to support your cause you
> > really should reserve that "Mickey Mouse" adjective to describe your
> > own mindset.
> >
> > /Peter
> >
> >
> >> --
> >> Sybrand Bakker, Senior Oracle DBA
>
> Actually Peter my instinct here is to agree with Sybrand. Everything
> you've written flies in the face of good practice in an Oracle database.

If I were to develop a new application, naturally I would take the
idiosyncracies of the target platform into consideration.. Instead I am
porting an existing one that runs on a wide range of platforms -
including several databases of which I've already mentioned a few.
This should be evident had you read the first post on this thread.

/Peter

> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

peter.koch.larsen@gmail.com wrote:


> First let me get this straight with you. This is not a new product as
> it has been in production for probably more than fifteen years. It
> currently runs on a wide variety of databases and operating systems all
> over the world. And it is by most regarded as the market leader in its
> domain.


This means nothing - people produce and buy poor software all the
time.

*_Generally_* speaking "database neutrality" is a synonym for "crap
system" - the lowest common denominator wins out.


It would appear that you are either going to have to do something in
your application code or forget about using Oracle.


> It is also important to add- in case you haven't realised this already
> - that this software is not shrink wrap software. We do not sell a new
> product every day - and probably not one each month. Also I should add
> that the configuration is not performed by ordinary end-users, but by
> highly skilled users - most likely IT-professionals in cooperation with
> domain specialists.


Who is "we" exactly?



Paul...



> Peter

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

Comments in-line.

peter.koch.larsen@gmail.com wrote:
> DA Morgan skrev:
>
>> peter.koch.larsen@gmail.com wrote:
>>
>>> The table creation has nothing at all to do with temporary tables. The
>>> fact is that when we deliver our software we have no idea what tables
>>> there should be.
>>>
>>> /Peter
>>>> Jim
> Hi Daniel
>
>> This statement is horrifying. I read your original post and reread it
>> again just now. It appears that faced with a business problem you
>> chose a solution independent of best practice and independent of the
>> architecture of products, such as Oracle, on which you might implement
>> it.
> First let me get this straight with you. This is not a new product as
> it has been in production for probably more than fifteen years.

Whether it has been in production for 15 years or 15 minutes you are now
trying to move it to Oracle. And the database world, for all databases,
changes every few years. This is not 1991 and decisions made that long
ago are not going to be best practice in 2006 nor would I expect what I
do today to be best practice in 2021.

> It currently runs on a wide variety of databases and operating systems all
> over the world. And it is by most regarded as the market leader in its
> domain.

Which may all be true but in no way changes my statement. Banner is a
piece of wildly successful software based on Oracle. I can't state my
opinion of it without using four letter words: Several of them.

> It is also important to add- in case you haven't realised this already
> - that this software is not shrink wrap software. We do not sell a new
> product every day - and probably not one each month. Also I should add
> that the configuration is not performed by ordinary end-users, but by
> highly skilled users - most likely IT-professionals in cooperation with
> domain specialists.

Doesn't matter. What you've done in the past says nothing about Oracle
and nothing about what you should be doing in 2006.

>> I'd suggest that you both reconsider you choices and reconsider the
>> above statement. The above statement is so bad that I have kept a
>> copy of it to present to my Oracle students as an example of why
>> projects fail.
>
> As you might guess, this is not a failed product, but a market leading
> and highly successful product that thrives very well in its niche.

Then best wishes. But please don't make a corporate marketing decision
with respect to Oracle and expect Oracle to comply with your
corporation's bad design decision.

>> You've earned the dubious honor of being a "bad example"
>> at the University of Washington (though I did remove your name from
>> the slide).
>
> Honor the fool ;-)
> So please be explicit when critizicing our product.

Gladly. What you are doing precludes optimization and is unscalable.

> So far as I guess,
> our solution will not differ from e.g. large accounting system such as
> SAP where customisation is a major part of the product.

Performing manual customization in a product like Oracle EBS,
PeopleSoft, JD Edwards, Siebel, etc. is done in my experience prior
to the product going into production. There are no transactions taking
place on the system. You seem to be saying that your product's design
is one in which at any point in time, middle of the business day,
someone that buys your product can modify schemas. It is to this I am
reacting. There are far better ways to implement flexibility unless I
am misunderstanding the posts you have made in this forum.

>> Seriously ... your statement equates with ... I don't care about
>> security, stability, scalability, performance, or maintainability.
>
> Please motivate that statement. I have a really har time detecting
> what e.g. security has to do with all this.

You can not implement capabilities such as Fine Grained Auditing and
Fine Grained Access Control on tables you don't know exist. Neither can
you implement many other forms of auditing and security.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

peter.koch.larsen@gmail.com wrote:
> DA Morgan skrev:
>
>> peter.koch.larsen@gmail.com wrote:
>>> Sybrand Bakker skrev:
>>>
>>>> On 9 May 2006 14:09:39 -0700, peter.koch.larsen@gmail.com wrote:
>>>>
>>>>> Right. But thus time it is not a feature, but a limitation that is not
>>>>> as i see it is not in any way based on some requirement for logical
>>>>> consistency - more like the opposite if anything.
>>>> It rather seems you have a basic lack of understanding of relational
>>>> and transactional concepts. You probably would better stick to your
>>>> Mickey Mouse products.
>>> Excuse me, but this is simply false. If you simply must come with
>>> personal attacks instead of giving evidence to support your cause you
>>> really should reserve that "Mickey Mouse" adjective to describe your
>>> own mindset.
>>>
>>> /Peter
>>>
>>>
>>>> --
>>>> Sybrand Bakker, Senior Oracle DBA
>> Actually Peter my instinct here is to agree with Sybrand. Everything
>> you've written flies in the face of good practice in an Oracle database.
>
> If I were to develop a new application, naturally I would take the
> idiosyncracies of the target platform into consideration.. Instead I am
> porting an existing one that runs on a wide range of platforms -
> including several databases of which I've already mentioned a few.
> This should be evident had you read the first post on this thread.
>
> /Peter

It is evident. It should be evident to you from my response and those of
others that have be involved in this thread that we are all consider
your products design, at least as you have presented it, as far from
being best practice. And, further, that Oracle will not change its
concepts and architecture to make what you are trying to do work as you
seemingly wish.

If you want to work with Oracle. And you want to engage in best
practices rather than using duct tape you will need to re-examine the
underlying design decisions.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan skrev:

> Comments in-line.
>
> peter.koch.larsen@gmail.com wrote:

[snip]

> Whether it has been in production for 15 years or 15 minutes you are now
> trying to move it to Oracle. And the database world, for all databases,
> changes every few years. This is not 1991 and decisions made that long
> ago are not going to be best practice in 2006 nor would I expect what I
> do today to be best practice in 2021.

Our product did evolve since 1991 as well.

>
> > It currently runs on a wide variety of databases and operating systems all
> > over the world. And it is by most regarded as the market leader in its
> > domain.
>
> Which may all be true but in no way changes my statement. Banner is a
> piece of wildly successful software based on Oracle. I can't state my
> opinion of it without using four letter words: Several of them.
>
> > It is also important to add- in case you haven't realised this already
> > - that this software is not shrink wrap software. We do not sell a new
> > product every day - and probably not one each month. Also I should add
> > that the configuration is not performed by ordinary end-users, but by
> > highly skilled users - most likely IT-professionals in cooperation with
> > domain specialists.
>
> Doesn't matter. What you've done in the past says nothing about Oracle
> and nothing about what you should be doing in 2006.
>
> >> I'd suggest that you both reconsider you choices and reconsider the
> >> above statement. The above statement is so bad that I have kept a
> >> copy of it to present to my Oracle students as an example of why
> >> projects fail.
> >
> > As you might guess, this is not a failed product, but a market leading
> > and highly successful product that thrives very well in its niche.
>
> Then best wishes. But please don't make a corporate marketing decision
> with respect to Oracle and expect Oracle to comply with your
> corporation's bad design decision.
>
We surely do not market our product that way. Basically, we run the
product in the environment present at the customer. If the customer
wants Oracle, we'll do our best to give it to him.
> >> You've earned the dubious honor of being a "bad example"
> >> at the University of Washington (though I did remove your name from
> >> the slide).
> >
> > Honor the fool ;-)
> > So please be explicit when critizicing our product.
>
> Gladly. What you are doing precludes optimization and is unscalable.
>
> > So far as I guess,
> > our solution will not differ from e.g. large accounting system such as
> > SAP where customisation is a major part of the product.
>
> Performing manual customization in a product like Oracle EBS,
> PeopleSoft, JD Edwards, Siebel, etc. is done in my experience prior
> to the product going into production.

What happens with our system is that there is a testbed isolated from
the production system. The system is tested here before being installed
in the production system - well, what do you expect? We're not
lunatics.

> There are no transactions taking
> place on the system. You seem to be saying that your product's design
> is one in which at any point in time, middle of the business day,
> someone that buys your product can modify schemas.

Of course not. The "upgrade" takes place perhaps three times a year. I
have not had any mention of the frequency. Still, it is most annoying
should the upgrade fail. Particularly on the testbed, where these
changes are made perhaps several times a day. Also, when playing with
the system and making an error that causes the system to not upgrade
(that is the database or other parts of the system aborts), you have to
stop the system and manually remove the tables that should not have
been installed. This is a pain in you-know-what for the developers (but
of course not a show-stopper).
> It is to this I am
> reacting. There are far better ways to implement flexibility unless I
> am misunderstanding the posts you have made in this forum.
>
> >> Seriously ... your statement equates with ... I don't care about
> >> security, stability, scalability, performance, or maintainability.
> >
> > Please motivate that statement. I have a really har time detecting
> > what e.g. security has to do with all this.
>
> You can not implement capabilities such as Fine Grained Auditing and
> Fine Grained Access Control on tables you don't know exist. Neither can
> you implement many other forms of auditing and security.

Reading this post makes it evident that you (and perhaps others) simply
haven't understood our product. To repeat, we do not in a production
system create new tables on a daily basis, but rather perhaps three
times a year (and an upgrade would not necesarrily require new
DDL-statements in our transaction).
I've reread the entire thread in search for a post that could be
understod as if we would ever do so on a "daily" basis. Of course not -
and how could you ever assume so?

The inability by Oracle to abort DDL-transaction is still a major
nuisance for the users of our testbed. Our software relies on the fact
that ABORT means ABORT and not just abort after the last DDL statement.
As a consequence whenever we play with the system and something fails,
we need to do a manual repair job (or backup the database first). This
does not make our system unusable, but it certainly is a major
inconvenience. Compare it to having to reboot your system every time
you made an error and you will have an idea what I'm talking about.

> --
> Daniel A. Morgan

/Peter