Advanced Database Development with Oracle SQL Developer

Advanced Database Development with Oracle SQL Developer David Peake Principal Product Manager The following is intended to outline our general pr...
Author: Norma Page
5 downloads 1 Views 899KB Size

Advanced Database Development with Oracle SQL Developer David Peake 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 Oracle’s products remains at the sole discretion of Oracle.

Agenda • Overview of SQL Developer Features • Advanced Features • PL/SQL editing and debugging • User defined reports • Creating SQL Developer extensions

• SQL Developer Going Forward • Summary • More Information

Lewis Cunningham Database Architect, Shepherd Systems "Oracle SQL Developer is a tool that should be in every Oracle developer's toolkit. In addition to allowing you to do SQL and PL/SQL development, Oracle SQL Developer provides a laundry list of useful reports and a facility for creating your own user-defined reports.”

Overview of SQL Developer Features • • • • • • •

Create Database Connections Create and browse database objects Query and update Data Import Data Export Data and DDL for objects Use SQL Worksheet for queries and scripts Use Graphical Query Builder • Supports complex where clauses

• Manage Snippets • Add categories and new snippets

• Create, edit, compile, debug PL/SQL • Create, run, edit database reports • Manage SQL Developer Environment using Preferences

Creating and Running PL/SQL • Code editor • • • • •

Syntax highlighting Code formatter Code insight (auto complete) Code folding Query Builder

• Code snippet viewer • Drag and drop code snippets • Add and customize snippets

• Compile, Run and Debug • Inline error reporting • Full debug capabilities

• Code bookmarking • Preferences • Customizable quick keys

Running PL/SQL • Run Procedures, Functions, and Packages • 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

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

Remote Debugging Problem: Test a procedure being executed in a separate application • SQL Developer • Select Remote Debug • Set up remote debug detail; machine, port • Browse to procedure • Set a breakpoint

• Remote session • execute DBMS_DEBUG_JDWP.CONNECT_TCP (‘’, 4000) • Execute procedure

• SQL Developer • Debug


SQL Developer Remote Debugging and Oracle APEX

Creating and Running Reports • Suite of System Reports • • • • • • • • • •

All Objects Charts DBA Data Dictionary Jobs PL/SQL Security Streams Table XML

Additional Context Menu Options • Generic • Single Record View • Count Rows • Export data • HTML • XLS • XML • Loader • Insert…

• Session Reports • Trace Session • Kill Session

Creating User Defined Reports • Types of Reports • System • Instance Data

• Styles • Master – Detail Reports • Charts • Alternative Reports • Running Scripts • Producing HTML Output • Drill Down Reports

Master-Detail Reports • Create Master • Test report • Add binds

• Create Detail • Include Join Clause • Add Binds • Test full report

Creating Graphical Reports • Generic query select group, series, data from table

• Example select emp.deptno, emp.deptno, max(sal) from emp, dept where emp.deptno = dept.deptno group by emp.deptno

Alternative Reports • PL/SQL for HTML output • Scripts • Code

Drill Down reports • Create reports to navigate from one report to the next • Provide each report with a report ID • Walk through the flow selecting report ids as the driver


SQL Developer Creating Reports

Extensibility • SQL Developer leverages the JDeveloper IDE, thus giving developers the option to extend the functionality • Custom plug ins • Available now • Oracle Data Miner PL/SQL Code Generator • In the Future • Oracle Migration Workbench

User Developed Extensions • Users writing their own extensions • • • • •

CWD4ALL for ORACLE SQL Developer Insider for SQL Developer Error lookup for SQL Developer GIS extension for Oracle SQL Developer OrindaBuild

Adding XML Extensions • Create XML • Associate Extension type with file location

Creating Java Based Extensions • Install • A Java IDE • JDeveloper • Eclipse • SQL Developer

• In JDeveloper • Using Check for Updates, set up the SDK • Install SDK Samples

• Create or open a workspace • Create or edit a project

• • • •

Change the deployment profile Deploy Open SQL Developer Run


SQL Developer Creating Extensions

SQL Developer 1.1 • Reporting • Master Detail & Linked Reports • Charting

• Native Excel Import & Export • SQL Worksheet enhancements • Duplicate Record • View CLOB data • Single record view

• • • • •

SQL and PL/SQL Parsing Updates Enhanced SQL Script File Handling Rewritten Object Navigator Search Engines: AskTom, Metalink, OTN Forum… External Tools Support

SQL Developer Going Forward • Tuning and Diagnostics • Database Activity Reporting on AWR & ASH • PL/SQL Profiler, SQL Tune & Access Advisor • Trace File Parsing and Reporting

• Other Features • Additional database objects • AQ, DBMS Job, Intermedia, Text • 11g New Feature Support • Database Editions • PL/SQL Enhancement e.g SQL Injection Tool • Workload capture & Replay… • Version Control • CVS, SVN • Migration Workbench (SQL Server, MySQL and MS Access)

Summary • SQL Developer offers basic database features • • • • • •

Database Connections Create and browse capabilities Query and update features Import and Export features A Worksheet for queries and scripts A Graphical Query Builder

• Advanced capabilities include • PL/SQL editing and debugging • User defined reports • SQL Developer extensions

For More Information SQL Developer