1
<Insert Picture Here>
PL/SQL Development and Unit Testing with Oracle SQL Developer 3.0
Sue Harper Senior Principal Product Manager
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracles products remains at the sole discretion of Oracle.
Agenda
SQL Developer 2.1 New Features SQL Developer 3.0 New Features Working with PL/SQL Remote Debugging Unit Testing Tuning Finding More Information
SQL Developer Features
Database connections Browse, query, update objects SQL Worksheet Query Builder Data import and export Object export PL/SQL Database and User defined reports User defined extensions Third-party databases Migrations Oracle APEX integration TimesTen integration Files and source code control
5
SQL Developer 2.1 New Features
PL/SQL Unit Testing Data Modeler Viewer Migration support for IBM DB2 UDB and Teradata Updated Data Grids
Manage columns, filter on data
New SQL Worksheet
Dockable dbms_output, multiple worksheets
Increased Connections navigator support for
Jobs, Editions (for Database 11g Release 2), XML DB Repository
Updated display editors
PL/SQL edit mode, subpartitions
Version Control support for Serena Dimensions, Perforce Updated filtering mechanism
Schema level, generated objects
Oracle SQL Developer 3.0 New Features
Production March 2011 DBMS Scheduler Query Builder Database migration Integrated spatial support PDF file support DBA Navigator Schema browser Extended Tuning Support Extended Unit Testing PL/SQL updates Import and Export updates Integrated Data Miner support
Working with PL/SQL
Creating and Editing PL/SQL
Code editor
Syntax highlighting Code formatter Code insight (auto complete) Code folding Query Builder
Code snippets
Drag and drop code snippets Add and customize snippets
Code templates
Preparing Code Skeletons with Dialogs
Use dialog to create
Procedures & Functions Triggers Package spec & body
Create Trigger
Table View Schema Database
10
Compiling and Running PL/SQL
Compiling
Inline error reporting
Run procedures, functions, and packages
Use DBMS_OUTPUT Function return values OUT parameters
Run PL/SQL dialog
Specifies run targets Shows parameter detail Generates editable PL/SQL block For parameter values For output parameters Works with records
11
Debugging PL/SQL
Set breakpoints
Configure conditions
Compile for Debug Control program execution (Step into, over) Run to Cursor Inspect and modify variables Review
Smart data Data
Watches expressions View debug log
12
Remote Debugging
Problem: Test a procedure being executed in a separate application In SQL Developer
Select Remote Debug Set up remote debug detail; machine, port Browse to procedure Set a breakpoint
In remote session
execute DBMS_DEBUG_JDWP.CONNECT_TCP (127.0.0.1, 4000) Execute procedure
In SQL Developer
Debug as normal
Use the environment variable SET ORA_DEBUG_JDWP=host=127.0.0.1;port=4000
13
Unit Testing
14
Unit Testing Overview
Tests Suites Reports Library Static and dynamic lookups Multi user repository based Code coverage Command line use Target any database
15
Unit Testing - Tests
Input/Return
Static or Dynamic Values
Startups/TearDown
Table Copy/Restore Row Copy/Restore Custom
Code coverage Success or failure testing Validation
Custom
16
Unit Testing - Suites
Startups/TearDown
Table Copy/Restore Row Copy/Restore Custom
Code Coverage Tests are run sequentially Startup and teardowns for tests can be turned off
17
Unit Testing - Library
Stores reusable items
Dynamic Values Startups Teardowns Validations
Referenced or copied to local tests
18
Unit Testing - Reports
Includes Standard reports
Suites Tests Code Coverage
Reports against the repository Users can query the repository directly
19
Unit Testing Multi User
Use database users Control
admin vs. user
Managed with roles
20
Unit Testing Running Suites/Tests
Inside SQL Developer simply change the Combo List Command line by passing arguments Results are stored in the repository
21
Tuning
22
Refactor, Review, Search, Tune and Monitor
SQL Developer provides a PL/SQL related utilities PL/SQL Hierarchical Profiler Extended Search using PL/SQL SQL Monitor SQL Developer PL/SQL Reports General refactoring APEX refactoring
23
Hierarchical Profiler
24
Searching
Find (and replace) in all editors Find DB Objects
Across schemas Navigate to object
Extended search Across schemas
For object types For usages PLScope support
(Oracle Database 11g)
25
Real-Time SQL Monitoring
Real time view of SQL Use /*+MONITOR*/ Drill to view details Visual indicators for current step Queries over 5 seconds monitored DBMS_SQLTUNE.REPORT_SQL_MONITOR
26
SQL Monitor and PL/SQL Reports
Shipped PL/SQL Reports
SQL Monitor Search Source Code Program Unit Arguments
User Defined Reports
Using plsql-dbms_output Formatting code
27
Refactoring
Extract a procedure Surround blocks with
For While Begin block
Variable renames Extract anonymous PL/SQL blocks from APEX apps
28
Integrating with Oracle APEX
Providing integration points to Oracle APEX Remote debugging Tuning SQL Refactoring PL/SQL code
29
Finding More Detail
30
Finding More Detail
www.oracle.com/technology/products/database/sql_developer
SQL Developer on OTN
White papers,Oracle by Example (OBE) and online demos Team Blogs: Blogs, Magazine Articles & Podcasts www.oracle.com/technology/products/database/sql_developer
SQL Developer Exchange
Share reports, snippets, code, and add feature requests http://sqldeveloper.oracle.com
Forums
SQL Developer forums.oracle.com/forums/forum.jspa?forumID=260
31
Summary
PL/SQL
Creating, editing, compiling and debugging
Unit Testing
Creating, Running, Reporting Batch processing
Creating SQL Developer extensions Real-Time SQL Monitoring
Watch SQL as it runs
Refactoring
Convert APEX anonymous blocks into a Package
32
33
34
