Re: Syntax checking DO blocks and ALTER TABLE statements?

David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:

> On Tue, Feb 16, 2021 at 4:28 PM Tim Cross <theophilusx(at)gmail(dot)com> wrote:
>
>>
>> David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>>
>> > On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>> >
>> >>
>> >> How does one go about syntax checking this?
>> >>
>> >> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping
>> in
>> >> similar DO blocks, and want to make sure the statements are clean.)
>> >>
>> >>
>> > Begin a transaction, execute the DO, capture an error if there is one,
>> > rollback the transaction.
>> >
>>
>> As David points out, wrapping the whole thing in a transaction will at
>> least guarantee it all succeeds or it is all rollled back. This can be
>> frustrating if the statements are slow and there are a lot of them as it
>> can result in a very tedious do-run-fix cycle.
>>
>>
> I do presume that someone wanting to test their code in this manner would
> be doing so in a test environment and an empty database. Which makes the
> execution time very small.
>

True. However, it never ceases to amaze me how many places don't have
such environments. Far too often, my first task when commencing a new
engagement is to sort out environments and procedures to manage change.

> I personally would also solve the "lot of them" problem by using dynamic
> SQL, so one pretty much only has to test the code generator instead of all
> the actual executions - which can simply be confirmed fairly quickly once
> on a test database without the need for transactions.
>

Given the number, I think I would do the same. A good example of why
being 'lazy' can be a virtue. Faster and easier to write a procedure to
generate dynamic SQL than write out all those alter statements manually
or even write it using a scripting language and ODBC if there is
sufficient variation in the statements to make writing it in plsql
'messy'.

--
Tim Cross