6 Replies Latest reply: Dec 5, 2012 4:45 PM by User437258 RSS

    How can i test my Procedure /Function/Package

    948515
      Hi,
      How can i test my Procedure /Function/Package, i mean how to unit & integration test my Procedure /Function/Package. Is there any tool available in the market, or we have to test in manually .


      If manual test means can you please give me the brief steps to done.


      Thanks & Regards
      Arun
        • 1. Re: How can i test my Procedure /Function/Package
          mk_dba
          you can test using sql developer debug for testing. you can also use dbms_profiler for plsql tuning and testing.

          You can also use dbms_output.
          • 2. Re: How can i test my Procedure /Function/Package
            APC
            mk_dba wrote:
            you can test using sql developer debug for testing. you can also use dbms_profiler for plsql tuning and testing.

            You can also use dbms_output.
            No no no. Debugging is not testing. It is the opposite of testing. Debugging is what we must do if we haven't tested our code properly.

            Cheers, APC
            • 3. Re: How can i test my Procedure /Function/Package
              APC
              The granddaddy of PL/SQL test harnesses is utplsql. This is a pure PL/SQL solution (well, it includes some tables but it just requires a database). This is the place to start with unit testing. utplsql was written by Steven Feuerstein, Whom God Preserve. It's rather old now and no longer supported, but as it's open source so you can fix bugs if you find any. [url http://utplsql.sourceforge.net/]Find it here.

              Most IDEs will generate test harnesses for you. I prefer utplsql because it's pure database and also because it supports [url http://www.extremeprogramming.org/rules/testfirst.html]Test First development. But if you like GUIs check out [url http://docs.oracle.com/cd/E15846_01/doc.21/e15222/unit_testing.htm]SQL Developer as a good free option.

              After utplsql Steven Feuerstein developed Code Test for Quest. This is probably more productive as a unit test tool but it is not free and it only runs on Windows (which isn't a problem for most people). If you already license TOAD and have some spare money you should consider it. [url http://www.quest.com/toad-development-suite-for-oracle/code-tester-for-oracle.aspx]Find out more.

              When it comes to integration testing there isn't much on offer. Basically it's using the unit test tools to run higher level tests. If you are into CI you should check out [url http://176.34.122.30/blog/2009/05/18/continuous-integration-with-oracle-plsql-utplsql-and-hudson/]the utplsql plugin for Hudson .
              • 4. Re: How can i test my Procedure /Function/Package
                791525
                Thanks Mr.APC it will useful to kick start my learning about the testings


                Regards,
                Arun
                • 5. Re: How can i test my Procedure /Function/Package
                  Sven W.
                  Oracle SQL Developer has a component test suite very usable for unit tests. It needs a little to setup and get used to it, but then it is extremly helpful.

                  See also: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/sqldev_unit_test/sqldev_unit_test_otn.htm
                  APC: Debugging is not testing. It is the opposite of testing. Debugging is what we must do if we haven't tested our code properly.
                  Well said. I have to remember that phrase!

                  Edited by: Sven W. on Sep 18, 2012 8:14 PM
                  • 6. Re: How can i test my Procedure /Function/Package
                    User437258
                    I have to put in a plug for ruby-plsql-spec. The dynamic Ruby programming language (think OO like Smalltalk not quasi OO like Java) coupled with RSpec makes a fantastic PL/SQL testing framework. Adding methods to PL/SQL package functions is really cool and great for testing!

                    Check it out at http://blog.rayapps.com/2010/10/05/ruby-plsql-spec-gem-and-code-coverage-reporting.