1 / 41

Visual Studio Development Lifecycle: SQL, PL/SQL, Source Control, Deployment

Visual Studio Development Lifecycle: SQL, PL/SQL, Source Control, Deployment. Christian Shay Principal Product Manager - .NET Technologies, Oracle. Program Agenda. SQL and PL/SQL Development Lifecycle Overview Create Users, Roles and Grant Privileges Creating Schema Objects

maxwell
Download Presentation

Visual Studio Development Lifecycle: SQL, PL/SQL, Source Control, Deployment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Visual Studio Development Lifecycle: SQL, PL/SQL, Source Control, Deployment Christian Shay Principal Product Manager - .NET Technologies, Oracle

  2. Program Agenda SQL and PL/SQL Development Lifecycle Overview Create Users, Roles and Grant Privileges Creating Schema Objects Visual Studio Source Control Integration PL/SQL and SQL Editing SQL Tuning PL/SQL Debugging in Visual Studio Deployment of SQL and PL/SQL Scripts .NET Stored Procedures Overview Demo <Insert Picture Here>

  3. <Insert Picture Here> SQL and PL/SQL Development Lifecycle with Visual Studio

  4. Oracle Application Development Lifecycle Create Users/Roles Create Schema Objects Test, Tune, Deploy Source Control Grant Privileges .NET Coding PL/SQL and SQL Editing/Debug

  5. SQL and PL/SQL Development Lifecycle Overview • Oracle Developer Tools for Visual Studio • Tightly integrated “Add-in” for Visual Studio 2008, VS 2005 and VS .NET 2003 • ODP.NET • ADO.NET 1.x and ADO.NET 2.0 compliant data provider • Native access to Oracle database • Utilize advanced Oracle Database features • RAC, performance, security, data types, XML, etc. • Both available for free download today • http://otn.oracle.com/dotnet

  6. Create Users, Roles and Grant Privileges • User Designer • Role Designer • Grant and Revoke Privileges Dialog

  7. D E M O N S T R A T I O N User/Role Designer Privileges Wizard

  8. Create Schema Objects • Oracle Wizards • Table Designer • PL/SQL Package Wizard • Table Import Wizard to import data • ..many others (one Wizard/Designer for every schema type) • Query Window • Run SQL*Plus Script

  9. D E M O N S T R A T I O N Creating Schema Objects

  10. Visual Studio Source Control Integration • Configure Source Control in Visual Studio • Oracle Database Project • Generate Create Script to Source Control

  11. D E M O N S T R A T I O N Source Control Integration

  12. PL/SQL and SQL Editing • Oracle PL/SQL Editor – database based • Collapsible Regions • Syntax Coloring • Integrated Online Help • Supports Debugging • Oracle SQL Editor – file based

  13. D E M O N S T R A T I O N SQL and PL/SQL Editors

  14. SQL Tuning • Oracle Performance Analyzer • SQL Tuning Advisor • Explain Plan feature in Oracle Query Window

  15. Performance Tuning in Visual Studio –SQL Tuning Advisor Requirements ADVISOR Privelege Oracle Database license for Oracle Diagnostic Pack Oracle Database license for the Oracle Tuning Pack How to run: Oracle Query Window “Tune SQL” button Oracle Performance Monitor – Tune SQL button Implement Findings Button Automatically fix the problem for certain finding types View Report Button View more details about how to fix a problem

  16. Performance Tuning in Visual Studio –Oracle Performance Analyzer Detects performance issues in an application’s use of the database under load Use it when you have a running app with a load Requirements SYSDBA Oracle Database license for Oracle Diagnostic Pack

  17. D E M O N S T R A T I O N SQL Tuning

  18. PL/SQL Debugging in Visual Studio • Direct Database Debugging • “Step into” a SP directly from Server Explorer • Application Debugging • Step from application code (eg C# or ASP.NET code) directly into PL/SQL and then return back • External Application Debugging • Set breakpoints and debug SPs called by external applications running on other machines or platforms

  19. PL/SQL Debugging Configuration • GRANT debug privileges as SYSDBA • 9.2, 10g: GRANT DEBUG ANY PROCEDURE TO username • 10g or later: • GRANT DEBUG CONNECT SESSION TO username • Set port range in Debugging Options page • Tools -> Options->Oracle Developer Tools • Compile PL/SQL units for Debug • Via menu in PL/SQL editor or in Oracle Explorer

  20. Direct Database Debugging • “Step Into” from Server Explorer • “Run Debug” from Server Explorer • Enter parameters manually

  21. Application Debugging Mode • Step from .NET code into PL/SQL and back • Check off “Tools -> Oracle Application Debugging” • ODT automatically starts listener using port in range given in Options page • Uncheck "Enable the Visual Studio hosting process" in the .NET Project Properties Debug tab

  22. D E M O N S T R A T I O N PL/SQL Debugging

  23. Deployment of SQL and PL/SQL Scripts • Source Control • Export/Import

  24. <Insert Picture Here> .NET Stored Procedures

  25. Write .NET Stored Procedures for Oracle Oracle Developer Tools for VS.NET Client Application Visual Studio Environment Develop Invoke C#,VB.NET,C++ Server Project (Use ODP.NET if required) Stored Procedure, SQL .. Invoke Deploy Build Assembly foo.dll (proc sp) Oracle DB Extensions for .NET Stored Proc

  26. Architecture

  27. Supported Platforms • 32 bit and 64 bit Windows • .NET Framework 1.1 or 2.0 • ADO.NET 1.x, 2.0 • Oracle 10.2 or later

  28. When to use • Ease of development • Computationally intensive code • Leverage .NET programming expertise • Leverage existing .NET code libraries • Call out to OS or Applications • When Windows platform only is OK • When no commits or rollbacks required in SP • When no distributed transactions are required

  29. Install • Optional Database option ships with Oracle Database 10.2 and 11g • Latest and greatest release is part of ODAC • DBCA performs configuration • CLR Service created and started • Service is named <OracleHomeName>ClrAgnt

  30. Stored Procedure Requirements • Be declared a public static method. • Not be a constructor or a destructor. • Use parameter types that are compatible with the Oracle native database types.

  31. ODP.NET Server Side Differences • Failover/Notifications, Transaction and Connection classes have differences • No transactions can be started, committed, or rolled back from within a .NET SP • No distributed transactions

  32. <Insert Picture Here> Next Steps

  33. .NET Hands On Lab! • Tuesday – 2:30pm–6:30 pm - Continental Parlor 7/8/9 • Ten Lessons, work at your own pace – from beginner level to advanced • Visual Studio 2008 and Oracle 11g preinstalled and configured

  34. .NET Hands On Lab! • Developing and Deploying a .NET Stored Function • Using Database Change Notification With ODP.NET and Oracle 11g • Building ASP.NET Web Applications with Oracle Developer Tools for Visual Studio • Getting Started with Oracle Data Provider for .NET • Building .NET Applications Using Oracle Developer Tools for Visual Studio • Debugging Oracle PL/SQL from Visual Studio • Optimizing Data Access Performance with ODP.NET • Using Oracle User-Defined Types with .NET and Visual Studio • Tuning .NET Applications in Visual Studio with SQL Tuning Advisor and Oracle Performance Analyzer • Using Oracle Providers for ASP.NET

  35. .NET Demogrounds Booth • All day Monday – Wednesday • Oracle .NET Engineering staff are there to give you a demo and answer questions • Moscone West, first floor, far left back corner area • Booth W-011 – “.NET Development for Oracle Database” • Windows Database booth is nearby at booth W-010

  36. Upcoming Oracle Develop .NET Sessions • Tuesday 11:30 am - Golden Gate 1 • Messaging and Event-Driven .NET Applications with Oracle Database • Tuesday 1:00 pm - Golden Gate 1 • Optimize Oracle Data Access Performance with Microsoft Visual Studio and .NET

  37. Upcoming OpenWorld Sessions • Wednesday 10:15 pm – Moscone South 236: • Microsoft Active Directory and Windows Security Integration with Oracle Database • Wednesday 11:45 am - Moscone South 236: • Best Practices for Performance of Oracle Database on Windows

  38. More Information .NET Technology Center http://otn.oracle.com/dotnet .NET Blog http://cshay.blogspot.com/ For more questions christian.shay@oracle.com or search.oracle.com

  39. The preceding 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.

More Related