Oracle9 i JDeveloper for Database Developers and DBAs

Oracle9i JDeveloper for Database Developers and DBAs Brian Fry Principal Product Manager Oracle JDeveloper Oracle Corporation

Agenda • Database and SQL Development • Schema Modeling and Offline Editing • PL/SQL Development and Debugging • Questions and Answers

Any JDBC database PL/SQL web services SQL*Plus, iSQL*Plus JPublisher interMedia SQLJ XML and XDB OLAP (BI Beans) Business Components for Java Connection Manager Database Browser PL/SQL IDE Java Stored Procedures Database Modeling Best IDE for the Database • Award-winning database support • Best Database Tool for Java • Best Java Database Application Tool • Best Database Access Tool

Connection Manager • Create and test connections with a wizard • Store often-used connections • Import and export connections • Reuse connections throughout lifecycle • Database, PL/SQL Development • Application Development • Deployment • Additional connection types • Application Server, WebDAV Server, SOAP Server, Oracle9i SCM Server, UDDI Registry

Browsing the Database • View schema objects from the Navigator • Tables (Indexes, Columns, Constraints, Data) • Views • Synonyms • PL/SQL subprograms • Oracle Objects • Materialized Views (Snapshots) • Java Resources • User-defined filters at all levels

Creating Database Objects • Create schema objects • PL/SQL subprograms • Tables • Views • Triggers

SQL Worksheet • Execute any valid SQL statement • Browse query results • Access previous statements from SQL History • Fetch rows incrementally

Tuning SQL Statements • Get the Explain Plan for any SQL statement

D E M O N S T R A T I O N Database Browser

Schema Modeling • “Design-capture” existing schema objects • Visually design tables and relationships • Generate DDL from diagram • SQL File • Directly to database • Reconcile with database

Offline Tables • Import objects from an existing database • Create and edit table definitions while disconnected • Generate DDL to create or alter tables • Reconcile changes with database

D E M O N S T R A T I O N Schema Modeling

Editing and Compiling PL/SQL • Comprehensive editor features • Bookmarks • Macros • Code Templates • Search and Replace • Syntax Highlighting • Etc • PL/SQL Code Insight • Synchronized Structure window • Errors reported in Log window

Running PL/SQL • Run Procedures, Functions, and Packages • DBMS_OUTPUT • Function return values • OUT parameters • Run PL/SQL dialog • Specify run target • Shows parameter information • Generates editable PL/SQL block for populating parameters

Debugging PL/SQL • Supported with Oracle8i and later • Uses JDWP implementation starting with Oracle9i Release 2 • Control program execution • Step Into, Step Over, Run to Cursor, etc • Inspect and modify variables • Tooltips in Code Editor • Configure breakpoint conditions

PL/SQL Debugging Requirements • Compiled with debug information • JDeveloper “Database Connections” preferences panel • ALTER SESSION SET PLSQL_DEBUG = TRUE • ALTER <PROG_UNIT> COMPILE DEBUG • Oracle8i and Oracle9i specifics • CREATE ANY PROCEDURE (debugging other schemas) • Oracle9i Release 2 specifics • DEBUG ANY PROCEDUREDEBUG CONNECT SESSION • Compiled in Interpreted (not Native) mode

Additional PL/SQL Debugger Features with Oracle 9.2 • Debug Java stored procedures • Seamless debugging between server-side Java and PL/SQL • Inspect elements of PL/SQL Collections (Tables, Records, etc) • Remotely debug PL/SQL calls from any client

1: Listen 2: Attach 3: Execute Remotely Debugging PL/SQL Database Session JDeveloper Client Application

Remotely Debugging PL/SQL:Explicit Calls • Start the debugger listener in JDeveloper (Listen for JPDA) • Modify the client code to attach to the debugger listener from the database session: DBMS_DEBUG_JDWP.CONNECT_TCP (‘host’, port); • Initiate procedure call from the same database session, for example: EXEC my_procedure; • Modify the client code to terminate the connection with the debug listener: DBMS_DEBUG_JDWP.DISCONNECT_TCP();

Remotely Debugging PL/SQL:Implicit Calls • Two-tier OCI applications • Set environment variableset ora_debug_jdwp = host12.acme.com;port=4040 • OCI layer transparently calls the server-side PL/SQL to connect with the debugger listener • Web mod_plsql applications • Create a UI form to set a cookie in the browser • Pass the hostname and port to owa_debug package • owa_debug transparently calls the server-side PL/SQL to connect with the debugger listener for each subsequent page requested by that browser

D E M O N S T R A T I O N PL/SQL Development

Summary • JDeveloper is a complete development environment for SQL and PL/SQL in addition to Java and XML

Q & Q U E S T I O N S A N S W E R S A

Oracle9 i JDeveloper for Database Developers and DBAs