Oracle Database Administration for Microsoft SQL Server DBAs part 29 takes the administration topics with which the SQL Server DBA is familiar, translates them into Oracle terms, and then expands on Oracle functionality. Definitions and comparative terms run throughout the book so the SQL Server DBA can easily leverage existing knowledge. This Oracle Press guide also expands on some of the features in Oracle that do not match up directly with SQL Server, and looks at other processes often performed on an Oracle database that would not typically be a standard practice in SQL Server environments | 262 Oracle Database Administration for Microsoft SQL Server DBAs Debugging Procedures and Unit Testing Procedures are made up of the functions transactions and cursors we have been discussing in this chapter. They are also part of the packages. Understanding how to write stored procedures is important for a DBA. Understanding how to review procedures and find the good and the bad in them may be even more important. When you re experiencing problems with procedures check their permissions on objects. Also check for the dreaded missing semicolon somewhere. These are quick areas to check and in a small stored procedure they might be easy to spot. Privileges needed for running in debug mode for PL SQL are Debug any procedure and Debug connect session. Figure 9-2 shows an example of compiling a procedure in debug mode in SQL Developer. Clicking an error message shown here will take you to the line in the code that is causing the issue. FIGURE 9-2. Debugging procedures in SQL Developer Chapter 9 PL SQL 263 Breakpoints can also be set to walk through the procedures to validate code and variables. Another way to get output throughout the procedure to see what is happening is to use to output a statement value or step in the procedure. SQL Developer also has unit testing functionality. Test plans can be set up as an unit test repository that is created in the database. You can seed some data and pass in parameters from tables to do the testing. To set up a repository for unit testing or to connect to an existing repository select the Unit Test option from the Tools menu as shown in Figure 9-3. Create a new repository if one is not yet available. SYSDBA permissions are required to create a new repository but users can be added with lesser permissions to run unit tests and create the test plans. Figure 9-4 shows the first step in creating a unit test using the wizard. All of the packages procedures and functions are listed and available for testing. Other
