Database Development Lifecycle with Visual Studio: SQL, PLSQL, 'NET Stored Procedures, Source Contro

1
(No Transcript)
2
Database Development Lifecycle with Visual
Studio SQL, PL/SQL, .NET Stored Procedures,
Source Control, and Deployment
Christian Shay Principal Product Manager,
Oracle
3
Agenda

• SQL and PL/SQL Development Lifecycle in Visual
  Studio
• Overview
• Creating
• Source Control
• Editing
• Debugging
• Deployment
• .NET Stored Procedures
• Overview
• Demo

4
ltInsert Picture Heregt
SQL and PL/SQL Development Lifecycle with Visual
Studio
5
SQL and PL/SQL Development Lifecycle

• Create Schema Objects, PL/SQL procedures,
  functions, packages
• Oracle Wizards eg Oracle Stored Procedure
  Wizard, Table Designer
• Query Window Ad Hoc SQL
• Run SQLPlus Script for existing scripts
• Create Roles and grant privileges to them
• Grant and Revoke Privileges Wizard
• Create SQL and PL/SQL scripts
• Generate Create Script from existing schema
  objects
• Store scripts in source control
• Oracle Database Project

6
SQL and PL/SQL Development Lifecycle

• Edit SQL and PL/SQL Scripts
• Oracle SQL Editor file based
• Oracle PL/SQL Editor database based
• Tune SQL
• Explain plan feature in Oracle Query Window
• Create client side .NET code
• (C, VB.NET, ASP.NET)
• Use Oracle Data Provider for .NET to call PL/SQL
• Debug .NET and PL/SQL together
• PL/SQL Debugger in Visual Studio
• Deploy

7
SQL and PL/SQL Development Lifecycle

• 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

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 SQLPlus Script

9
D E M O N S T R A T I O N
Creating Schema Objects
10
Create Roles and Grant Priveleges

• Oracle Query Window
• Grant and Revoke Privileges Dialog

11
D E M O N S T R A T I O N
Privileges Wizard
12
Visual Studio Source Control Integration

• Configuring Source Control in Visual Studio
• Generate Create Script to Source Control
• Oracle Database Project Features

13
D E M O N S T R A T I O N
Source Control Integration
14
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

15
D E M O N S T R A T I O N
SQL and PL/SQL Editors
16
SQL Tuning

• Explain Plan feature in Oracle Query Window
• Options page to choose what metrics to see

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 GRANT DEBUG CONNECT SESSION TO username
• Set port range in Debugging Options page
• Tools -gt Options-gtOracle 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 -gt 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
ltInsert Picture Heregt
.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
Oracle DB Extensions for .NET
Assembly foo.dll (proc sp)
Stored Proc
26
Architecture
27
Supported Platforms

• 32 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 ltOracleHomeNamegtClrAgnt

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
D E M O N S T R A T I O N
.NET Stored Procedures
33
ltInsert Picture Heregt
Next Steps
34
.NET Hands On Lab!

• Monday 230pm630 pm - Marriott Golden Gate A2
• Eight Lessons, work at your own pace from
  beginner level to advanced
• Visual Studio 2008 and Oracle 11g preinstalled
  and configured
• Lab Modules to choose from
• 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

35
.NET Demogrounds Booth

• All week
• Oracle .NET Engineering staff are there to give
  you a demo and answer questions
• Moscone West, first floor, far left back corner
  area
• Booth L41 .NET Development for Oracle
  Database
• Windows Database booth is nearby at booth L19

36
Todays Oracle Develop .NET Sessions

• 230pm 630pm Marriott Golden Gate A2
• Hands-on Lab Building .NET Applications with
  Oracle

37
Tuesdays .NET and Windows DB Sessions

• 100 pm Marriott Salon 12/13
• Optimizing .NET Data Access Performance with
  Oracle Database
• 100pm Moscone South Rm 305
• What's New for Windows and .NET in Oracle
  Database 11g
• 230pm Marriott Salon 01
• New Oracle Features for .NET Developers
• 400pm Marriott Salon 12/13
• ASP.NET Web Development with Oracle
• 500pm Moscone South Rm 305
• Active Directory and Windows Security Integration
  with Oracle Database

38
Thursdays Windows DB Sessions

• 900am Moscone South Rm 304
• Best Practices for Oracle Database Performance on
  Windows
• 1200pm Moscone South Rm 304
• Best Practices for Oracle Database and Client
  Deployment on Windows

39
More Information

• .NET Technology Center
• http//otn.oracle.com/dotnet
• .NET Blog
• http//cshay.blogspot.com/
• For more questions
• christian.shay_at_oracle.com

or
search.oracle.com
40
(No Transcript)
41
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
Oracles products remains at the sole discretion
of Oracle.