11 Replies Latest reply: Feb 13, 2013 5:41 AM by Marwim RSS

    TDD with PL/SQL

    Marwim
      Hello,

      Scope: my problem is not how TDD works or which tool to use. I have no problem to test code that does not depend on db content (e.g a custom to_char function) and private functions are public by conditional compilation in development db. It's about testing code that needs certain combination/line-up uf data to be tested.

      My application is managing insurance policies. To test e.g. a function that reads certain data from a policy-history I need the complete policy and also a complete policy for each test case. I have a repository of test policies in a master schema (used for regression tests) that I can load into development. The problem is, that loading the data takes so much time, that a quick succession of test/code/test/change/test... is not possible.

      How do you set up your data for TDD?

      Regards
      Marcus
        • 1. Re: TDD with PL/SQL
          John Stegeman
          Have you looked at Flashback (flashback table/flashback transaction/flashback database)?

          You could set up the data, run your test, then flashback to before the test.
          • 2. Re: TDD with PL/SQL
            Karthick_Arp
            Marwim wrote:
            Hello,

            Scope: my problem is not how TDD works or which tool to use. I have no problem to test code that does not depend on db content (e.g a custom to_char function) and private functions are public by conditional compilation in development db. It's about testing code that needs certain combination/line-up uf data to be tested.

            My application is managing insurance policies. To test e.g. a function that reads certain data from a policy-history I need the complete policy and also a complete policy for each test case. I have a repository of test policies in a master schema (used for regression tests) that I can load into development. The problem is, that loading the data takes so much time, that a quick succession of test/code/test/change/test... is not possible.

            How do you set up your data for TDD?

            Regards
            Marcus
            I don have personal experiance but some friends of mine has done RMAN Point-in-time recovery for this purpose. That too table space specific. The had all there development object in one table space and did [url http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmtspit.htm#CIHGGAFH] Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)
            • 3. Re: TDD with PL/SQL
              Marwim
              I thought about flashback, but there is just one dev-db (one for each application) and I'm not the only developer. Flashback would affect not only my policies but each others changes too.
              • 4. Re: TDD with PL/SQL
                John Stegeman
                Flashback would affect not only my policies but each others changes too.
                Not if you used flashback transaction
                • 5. Re: TDD with PL/SQL
                  Marwim
                  Besides the reasons against flashback RMAN is not available to our developers :-(
                  • 6. Re: TDD with PL/SQL
                    John Stegeman
                    You don't need RMAN for flashback transaction
                    • 7. Re: TDD with PL/SQL
                      Karthick_Arp
                      Marwim wrote:
                      Besides the reasons against flashback RMAN is not available to our developers :-(
                      After seeing John's post was going through the document and found 11g R1 has enhancements to DBMS_FLASHBACK package. They have introduced [url http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_flashb.htm#CHDEAIFF] TRANSACTION_BACKOUT Procedures which could be handy.

                      But not sure about your DB version :)
                      • 8. Re: TDD with PL/SQL
                        Marwim
                        Not if you used flashback transaction
                        But even then I would have to load my data before testing, because other developers might start a batch, e.g. write accounting records or reports for statutory health insurance and many more, that can change the data/history of my test cases. Or another developer might load a test case that I use for my TDD from the master to test a change herself.
                        • 9. Re: TDD with PL/SQL
                          Marwim
                          You don't need RMAN for flashback transaction
                          RMAN was because of Karthick's reply :-)
                          • 10. Re: TDD with PL/SQL
                            John Stegeman
                            Your tests use a shared resource (the one-and-only development database). The only way to break this dependency is to not use a shared resource (e.g. each developer has their own schema in the DB, or better yet, each developer has their own DB)
                            • 11. Re: TDD with PL/SQL
                              Marwim
                              Separate db/schema would make it easier but at the moment it's unlikely that we get them. I will try something else: A setup procedure that loads the necessary policies on demand (maybe once in the morning) and flashback transaction.

                              Thanks for your suggestions.

                              Marcus