David G. Johnston <david.g.johnston@xxxxxxxxx> writes: > On Tue, Feb 16, 2021 at 4:28 PM Tim Cross <theophilusx@xxxxxxxxx> wrote: > >> >> David G. Johnston <david.g.johnston@xxxxxxxxx> writes: >> >> > On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr@xxxxxxxxx> 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
