WIXLIB

Enabling Agile DatabaseDevelopment with Toad –Part 2Implement the automation components of Toad Development Suite for Oracle:Team Coding, Code Analysis, SQL Optimizer and Code TesterWritten by John Pocknell, Senior Product Manager, Quest What does it take to make your database development as agileas your application development?As we pointed out in Enabling Agile Database Developmentwith Toad – Part 1, the road to agile database developmentleads away from traditional, manual processes and toward fullyautomated pipelines that streamline work and minimize therisk of data loss or downtime. Together, Toad DevelopmentSuite for Oracle and Toad Intelligence Central offer automationfor synchronizing the development cycles of your applicationsoftware teams and database teams.Now, in part 2 of this technical brief, the focus is on TeamCoding, Code Analysis, SQL Optimizer and Code Tester,components of Toad Development Suite for Oracle.The components touch four areas of interest in automatingdatabase development:1. Access to source control2. PL/SQL unit testing3. Code reviews4. SQL and PL/SQL optimizationSETTING UP THE ENVIRONMENTAs depicted in Figure 1, the implementation of ToadDevelopment Suite for Oracle includes Toad for Oracle runningon development staff desktops. It also calls for various Toadrepositories and integration with version control systems.

DevelopmentStaff DesktopsContinuousIntegrationServer Jenkins Hudson Bamboo Team CityToadToad Development Suitefor OracleDevelopmentProjectsFor collaboration,Team Coding is autility that integrateswith your versioncontrol system.SourceCodeManagementReporting Formatter Templates Code Analysis Rule Sets Automation Database Connections Team Coding ObjectsVCS ProvidersFigure 1: Relationships among databases, version control and Toad productsToad Intelligence Central runs on aWindows server that all developers usingToad can access (to be covered in Part 3of this technical brief).INSTALLING THE DEVELOPERDESKTOPInstall Toad Development Suite forOracle on development systems. Itincludes the following products: Toad for Oracle Xpert Edition (includesSQL Optimizer for Oracle) Code Tester for Oracle Benchmark Factory for OracleImplementing Team CodingFor collaboration, Team Coding is a utilitythat integrates with your version controlsystem. Developers access PL/SQLcode and other objects from the Oracledatabase as usual, but Team Codingcontrols and monitors the check-out/check-in of the corresponding files insource control. Team Coding supportsthe following VCS providers: Serena PVCS Version Manager Microsoft Visual SourceSafe Microsoft Team Foundation Server Toad Data Modeler Microsoft Visual Studio Team Services(from Toad 12.9) Toad for MySQL Freeware Mercurial (from Toad 12.10)The implementation also requires anOracle client for Windows. The clientshould match the Oracle server inversion number and bit-width (32 vs. 64)as nearly as possible. Toad DevelopmentSuite for Oracle is flexible enough towork with Oracle Instant Client.Toad for Oracle itself can be installeddirectly on the Windows desktop usingthe web installer or a network packagewith silent installation.2Toad IntelligenceCentral IBM Rational ClearCase Perforce CVS Git Subversion (CollabNet recommended)Normally, Team Coding requires a setof repository tables to be installed ina schema in the Oracle database, butstarting with Toad for Oracle v12.10, you

can have the Team Coding objectsinstalled into the Toad IntelligenceCentral server. The result is a trulycentralized repository for all yourprojects without the need to install TeamCoding on each database instance.For more information on setting up TeamCoding with VCS integration, watchthis video.For information on the usage of TeamCoding with VCS, watch this video.Implementing Code AnalysisCode Analysis is a rules-based feature forcode review. It comes with approximately200 pre-defined coding rules acrossa number of standard category RuleSets such as Program Structure,Maintainability and Efficiency. As shownin Figure 2, you can use your own codingstandards and create your own RuleSets, then share the rules across themembers of each team. Code Analysisalso includes a repository for storing theresults of your code reviews.For more information on setting up andusing Code Analysis, watch this video.You can select an option in Team Codingthat will require a code review wheneverdevelopers try to check their code intosource control. Toad for Oracle willprevent any code that does not meetthe required quality levels from beingchecked in until the problems are fixed.For more information on setting TeamCoding policies, watch this video.Another option is to publish the resultsof code reviews to the Toad IntelligenceCentral, which presents historicaltrending and event-based informationthrough its web server.Figure 2: Creating a new Rule Set in Code Analysis and selecting rules from the library3You can select anoption in Team Codingthat will require acode review wheneverdevelopers try tocheck their code intosource control.

Auto Optimize SQLgenerates multiplerewrites of youroriginal SQL statementuntil it finds one thatexecutes more quickly.Figure 3: PL/SQL Profiler identifying a slow-running SQL statement inside aPL/SQL programOptimizing SQL and PL/SQLWhen a PL/SQL program runs slowly,you try to pinpoint the bottleneck. Ifthe problem is a poorly written SQLstatement, you then try to improveit. Toad and SQL Optimizer handle theprocess in two steps.Step 1 – Profiling PL/SQLThe PL/SQL Profiler feature in Toad worksin conjunction with the Editor to time the4execution of each line of PL/SQL code asthe program runs. PL/SQL Profiler storesthe data in a repository and representsit graphically, as shown in Figure 3.Any performance bottlenecks becomeimmediately apparent.For more information on setting up andusing PL/SQL Profiler, watch this video.

Figure 4: Auto Optimize SQL automatically finding a faster SQL alternativeStep 2 – Optimizing the SQLNext, use SQL Optimizer in Toad torelieve the bottlenecks. SQL Optimizerautomatically reforms the statementto remove any PL/SQL-specificelements, such as INTO statements, andconverts locally declared variables tobind variables.5The Auto Optimize SQL feature (seeFigure 4) will then start generatingmultiple rewrites of your original SQLstatement until it finds one that executesmore quickly. It presents execution plansand statistics to help you select the bestalternate statement.For more information on using AutoOptimize SQL, watch this video.With Code Tester youdescribe your testsbased on your usecases, and the toolgenerates test codeas a PL/SQL packagethat implements yourtest definition.

With Code Tester,you don’t write testcode. You describeyour tests and the toolgenerates test codethat implements yourtest definition.Figure 5: Creating a PL/SQL unit test in ToadIMPLEMENTING CODE TESTERFOR ORACLETesting code is a double-edgedsword. Developers know that testing isimportant, but it often involves writinga large volume of test code, keepingthat test code in synch with changes inapplication code, verifying test resultsand taking other steps that effectivelykeep developers from their highestvalue work.Code Tester for Oracle makes it easy todefine tests, generate test code and runtests, all within an easy-to-use graphicalinterface. Best of all, with Code Testeryou don’t write test code. You describeyour tests based on your use cases,and the tool generates test code as aPL/SQL package that implements yourtest definition.Code Tester stores execution data fromunit tests in the repository whether youexecute the tests directly from the ToadEditor or from Code Tester itself.6The Code Tester repository needs to beinstalled on the same database whereyour developers will be defining theirtests. You can enable public or privateaccess to the repository depending onyour requirements.Creating unit tests in Toad EditorAs shown in Figure 5, you can create unittests directly in Toad for Oracle and storethem in the Code Tester repository. Theprocess creates reusable tests that willbe stored for ongoing regression testingthroughout the lifetime of the code.In Toad Editor, when you execute thecode normally, Code Tester uses anyinput and output values to create theunit test definition, which it then stores inits repository.For more information on creating PL/SQLunit tests in Toad, watch this video.

Figure 6: Creating a PL/SQL unit test in Code Tester using Run-to-TestCreating unit tests in Code TesterCONCLUSIONIn Code Tester, the simplest way tocreate unit tests is to use the Run-to-Testfeature. It provides more-comprehensiveoptions for defining input values andexpected outcomes.It takes automation to make yourdatabase development as agile asyour application development. Thecombination of Toad DevelopmentSuite for Oracle and Toad IntelligenceCentral offers database developersstrong advantages in catching up withagile application development throughautomated features like Team Coding,Code Analysis, PL/SQL Profiler, SQLOptimizer and Code Tester. Databasedevelopers now have the opportunityto shorten development cycles whileminimizing risk.As noted above, all unit tests, whethercreated in Toad or Code Tester, will bestored in the Code Tester repository.There you can view all your tests, testsuites and PL/SQL code (see Figure 7).For more information on creatingcomprehensive PL/SQL unit tests inCode Tester, watch this video.Figure 7: Code Tester main window showing Test Builder (inset)7The Run-to-Testfeature providesmore-comprehensiveoptions for defininginput values andexpected outcomes.

ABOUT THE AUTHORJohn Pocknell is a senior manager ofproduct management at Quest. Based atthe European headquarters in the U.K.,John is responsible for the Toad productportfolio. He has been with Quest since2000, working in the database design,development and deployment product8areas, and he has run many Toadtraining courses for customers. Johnhas spent the last 15 years successfullyevangelizing Toad to customers atvarious events throughout Europe, theU.S. and the Asia-Pacific region. Johnwrites many blogs and papers that arepublished on the Toad user community,Toad World.John has worked in IT for almost 30years, most of that time in Oracleapplication design and development.He is a qualified aeronautical engineerwith more than 10 years of experience inprovisioning IT consultancy services andimplementing quality assurance systemsto ISO 9001.