Transcription
SAGE Computing ServicesOracleSQL DeveloperWorkshop0419 904 458www.sagecomputing.com.au
Oracle SQL Developer WorkshopEdition AUSOUG Conference 2006 SAGE Computing Services 2005 - 2006SAGE Computing Services believes the information in this documentation is accurateas of its publication date. The information is subject to change without notice.SAGE Computing Services assumes no responsibility for any errors that may appearin this document.JDeveloper, Oracle SQL Developer, 10G Internet Developer Suite, ApplicationExpress, Oracle, Oracle Internet Application Server, Oracle Discoverer, SQL*Plus,Oracle Forms Developer, Oracle Reports Developer, Oracle Graphics, OracleDeveloper, Oracle Designer, Oracle Internet Developer Suite are registeredtrademarks of Oracle Corporation.Oracle8i, Oracle9i, Oracle 10G and PL/SQL, are trademarks of Oracle Corporation.Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.Windows is a trademark of Microsoft Corporation.All trade names referenced are the services mark, trademark, or registeredtrademark of the respective manufacturer.Microsoft Word was used in the production of this document.SAGE Computing Services would like to thank Sue Harper, Oracle CorporationDatabase Tools group for the SQL Developer materials on which thispresentation was based.
SAGE Computing ServicesA Company ProfileTelephone: 0419 904 458Facsimile: 08 9243 4335www.sagecomputing.com.auServicesServices provided by the company include: Customised Training ProgramsPost-training MentoringDatabase Administration ServicesComplete Systems Development and SupportConsultancy AdviceQuality AssuranceTuning ReviewsBusiness AnalysisTelephone Advice and AssistanceDatabase Health ChecksSAGE Computing Services Pty. Ltd. aims to provide the best Oracletechnology experts to assist our clients in building, implementing andmanaging business solutions using Oracle software. Sage offers customtraining workshops in Oracle products and a complete analysis, design,development and database administration consulting service.SAGE is a small company which has succeeded by adopting a partnershipapproach with our clients. We have a long term relationship with many of ourclients which is dependent on our providing services which achieve successfuloutcomes.enquiries@sagecomputing.com.au
Course Descriptions(not a full list of available courses, please see www.sagecomputing.com.au for a full coursecatalogue)All courses can be customised to suit client requirementsenquiries@sagecomputing.com.auOracle JDeveloper 10.1.35 daysThis course is designed to provide students with the skills and knowledge required to developa web application using ADF Business Components and ADF Faces (JavaServer Faces) webpages within Oracle JDeveloper 10.1.3. JDeveloper Introduction ADF Entity Objects JDeveloper IDE Database Connections ADF Associations ADF Domains Online Database BrowserStored Procedure EditorWorkspaces and ProjectsOffline Database ModellingTools Java Language Application DevelopmentFramework ADF Business ComponentsDiagrammer ADF Business ComponentsADF Application ModulesADF View ObjectsADF View LinksADF Business ComponentJava Classes ADF Binding Layer and theADF Model Layer Classes ADF Faces ADF Form ADF Read-Only Table ADF Input, Output andCommand Components ADF Forms, Tables, Trees JSF Configuration, Navigation,Managed Beans and EventHandling Expression Language ADF Page Layout and Menus ADF Faces Skins Deploying an ADF Application Web Security ADF Selection, Validator,Converter and VisualComponents Code Management andSource Control Advanced JDeveloperOracle 10G - New Features for Developers Workshop1 dayAimed at developers, this course is designed to provide the student with anunderstanding of the new features of Oracle 10G. SQL*Plus Enhancements Flashback Enhanced SQL commands New SQL Features Data Pump PL/SQL EnhancementsApplication Express Workshop (previously HTMLDB) PL/SQL Packages Performance Enhancements3 daysThe course is designed to provide the student with the skills and knowledge required todevelop a complete application using Oracle’s Application Express product. The student willdevelop web interfaces (including forms, reports and charts; addition of validation andcustomised formatting) to create a small application. Product Overview SQL Workshop Regions and Items Page Processing Utilities Shared Components Application Builder Creating Pages Themes and Templates Other Page and RegionTypes Utilities and Reporting Advanced DevelopmentTechniques Administration and Deployingan Application
Oracle SQL and SQL*Plus Workshop - Oracle 10G Rel24 daysThis course is designed to provide the student with a basis for developing systems using theOracle database. The SQL language is covered from simple to complex constructs.Guidelines are provided on writing SQL for optimum performance and ease of maintenance. The Relational Model The SQL Language SQL*Plus and iSQL*Plus Oracle SQL Developer More about SELECT Substitution Variables Using SQL*Plus for FormattingOutput Functions Joins Group and AnalyticalFunctions Set Operators Subqueries Data Manipulation Language Database Objects Constraints Views and Sequences Indexes Clusters Security Locking andRead Consistency More Advanced SQLPL/SQL Workshop3 daysThis course is for developers who will be designing or building applications using the Oracleserver. It is relevant for developers who are using the Oracle Developer toolset, and forthose using alternative front-end products accessing the Oracle database. The coursecovers basic PL/SQL syntax and the use of server level procedures, functions and triggers. PL/SQL OverviewBasic PL/SQL SyntaxSQL Statements in PL/SQLProcedural Statements Assignment and ConditionalProcessing Procedural Statements LOOPS Exceptions Nested Blocks and CursorsTables, Arrays and RecordsArchitecture OverviewProcedures / Functions Execution and ErrorHandling Security and Dependency PackagesMore About PackagesSupplied PackagesTriggers More About Triggers Large ObjectsOracle Forms Developer Workshop5 daysThis course is designed for developers who will be designing or building applications usingOracle Form Builder. This is a practical course in which the student builds an applicationduring a series of workshop sessions. Other Item TypesVisual AttributesMouse EventsRelations Form and Data Block Properties Form Layout Alerts and Editors Lists of Values Items Introduction to Triggers Record Groups Windows and Canvases Program Units Transaction Processing andTriggers Advanced Data BlockProperties Running a FormForms Modules and StorageWorking in the BuilderCreating a Form Check Boxes, Radio Groupsand List Items More Trigger EventsDetermining Form PropertiesTimersIntegrating Multiple FormsModules in an ApplicationForms Architecture and JavaIntegrating Forms withReportsPL/SQL Library ModulesManaging ApplicationDevelopmentMenu Modules
Oracle Reports Workshop - 10G4 daysThis course is designed for developers who will be designing or building applications usingOracle Reports. This is a practical course in which the student builds a series of reportsranging from simple to complex. Product Overview Columns The User Interface The Designer Interface Multiple Queries and Links The Paper Layout - BasicObjects Standard Layouts General Paper LayoutProperties Advanced Paper LayoutProperties Web Reports Storage Building a Paper Report The Data Model Editor Other Query Types Displaying Files, Images andCharts Matrices Parameters PL/SQL in Reports Report Templates Publishing Reports on theWebOracle 10G – Database Administration Workshop5 daysThis course is designed for Database Administrators. It covers the architecture of the Oracle10g server, and the procedures required to effectively administrate the database. The courseprovides a series of practical workshops in which the students can practice the databaseadministration techniques they have learnt. Oracle 10g Overview Oracle 10g Architecture Database Creation Startup and Shutdown andOracle Database Oracle Enterprise Manager Database Structure Managing Tablespaces Managing Redo Log Groupsand Members Database Storage Managing Undo Database Tuning The Multi Threaded Server Backup and Recovery Data Pump Security OptimisationApplication Tuning Workshop 10g3 daysThis course is designed for Designers, Developers, and Database Administrators, andexamines all aspects of tuning SQL statements and applications. Defining a Tuning Methodology Diagnostic Tools Processing an SQL Statement Indexes Cost Based Optimisation Gathering Statistics Stored Outlines Storage Parameters Hash Clusters and IndexClusters Optimising PL/SQL Optimising Applicationsthrough Stored Proceduresand Packages Data Design forPerformance Tuning Tips Partitions Optimise using Parallelisation Tuning ToolsOracle Discoverer Workshop2 daysThis course is designed for End Users and examines all aspects of using the latest versionsof Oracle Discoverer. Both the web and client server interfaces are covered. Oracle Discoverer Overview Performing Analysis Discoverer Workbooks Customising Workbooks andWorksheets Printing and Exporting QueryResults Worksheets and Conditions Scheduling andAdministration
Oracle Portal Workshop3 daysThis course is designed to provide the student with the knowledge and skills required to buildcorporate portals. The course covers the use of Oracle Portal for content management andincludes recommendations and guidelines on the classification and searching of content. Thestandardisation and customisation of the Portal interface and styles are described. Theworkshop includes the use of Portal to create simple application components such as forms,reports and graphs. Finally the security management of a corporate portal is considered. Product Overview Page Groups and Pages Styles, Navigation Bars andTemplates Item Regions and Classification Custom Types, Parametersand Events Application Components Forms Application Components Reports Shared Component Other Components SecurityAdvanced Oracle Portal Workshop1 dayThe course is designed to provide the student with the knowledge and skills required to buildcustom portlets. The course describes the provider and portlet structure and the integrationand management of custom portlets within the product. The attendee builds a simpleprovider and its custom portlets based on an example. The course focuses on PL/SQLportlets to demonstrate the techniques required, but also covers web portlets. Detailedcoverage of the Portal API and its use, not only in custom portlets, but to enhance otherPortal components is included. Programmable Portlets Concepts PL/SQL Providers PL/SQL Portlets API Services – Other Utilities API Services – SessionStorage Web ProvidersOracle End User Workshop2 daysThis course is designed for End Users who require a knowledge of SQL to query the Oracledatabase. It commences with a description of relational concepts and continues withcoverage of the SQL statements required to access information from one or more OracleTables. Some basic formatting is also covered. The Relational Model Structured Query Language SQL*Plus Oracle SQL Developer More About SELECT Substitution Variables Using SQL*Plus forFormatting Output Functions Joins Group FunctionsAll our training is conducted at the client site and using the client’s Oracle licences. SageComputing Services provides all course materials which the attendees retain after the courseas a reference.All Sage trainers are consultants who are using the products in real world situations and canbring a wealth of experience to the classroom.
SAGE Computing ServicesSECTION 1 - OVERVIEWThe ProductInterfaceDatabase ConnectionsSet up1Hands On - Getting Started1.11.2Invoke SQL DeveloperCreate a Database ConnectionMore on ConnectionsModifying Database ConnectionsExporting and Importing ConnectionsInvoking SQL*Plus from SQL DeveloperSECTION 2 – DATABASE OBJECTS1111121212141415161617Browsing of Database ObjectsCreating Database ObjectsModifying Database ObjectsDeleting Database Objects17192426SECTION 3 – SQL QUERIESThe SQL LanguageStructureSelecting All RowsExpressionsColumn AliasRestricting the RowsSELECTfirst name, last name, salaryFROMemployeesWHEREsalary*12 100000Ordering the DataGroup FunctionsSUMCOUNTGrouping the Output into Sets of RowsJoins27272727282829303030303131313132Foreign keysSelect Statements with Join ConditionsUsing a Table AliasHands On – SQL and the SQL Worksheet3.13.23.3The SQL Worksheet ToolbarUsing the SQL Worksheet to perform SQL statementsSQL History List32323233333338Hands On - PL/SQL Development39Creating and Editing PL/SQLExecution and Debugging39414.14.25101011Hands On - Database Objects2.12.22.32.44910Help CentreOnline Help ManualContext Level Help399Help2Oracle SQL Developer WorkshopHands On – Reports5.15.25.35.4Page: 8Data Dictionary ReportsUser Defined ReportsStandard ActionsBind Variable Reports4646474949
SAGE Computing ServicesOracle SQL Developer WorkshopSection 1 - OverviewThe ProductFunctionalityOracle SQL Developer is a free GUI tool aimed at simplifying and enhancingdatabase development tasks. It facilitates the execution of SQL statements andscripts, the manipulation of Database Objects, editing and debugging of PL/SQL andthe running of Database Reports.SQL Developer has been developed in the JDeveloper IDE and works with anyOracle database 9.2.0.1 or later. It is installed simply by unzipping the downloadedfile.The functionality of the product includes: Creating, storing, importing and exporting database connections Creating and browsing users Browsing database objects using a tree based browser Creating database objects Querying and updating data using a grid interface Running SQL statements and scripts Entering, running and debugging PL/SQL In-built and custom ReportingInterfaceYou will operate SQL Developer using the following features: Use the Navigator pane on the left hand side of the screen to locate andselect objects. Use the right mouse menu to display context sensitiveactions. Use the tabbed panes on the right side of the screen to display informationabout objects and to enter SQL and PL/SQL statements.Click on a tab to display its information.To close a tab, place your mouse cursor over the tab and click on the X that isdisplayed.Double click on a tab to maximise. (Double clicking again returns to itsprevious state.)Drag the boundaries between panes to change their sizes. Optionally display the message area at the bottom of the screen (using themenu option View, Log) Optionally display the Help Centre on the right of the screen (by selectingoptions from the Help menu).Page: 9
SAGE Computing ServicesOracle SQL Developer WorkshopNote that this describes the default interface. The SQL Developer interface can becustomised using a series of preferences (using the menu option Tools,Preferences). You can also “dock” the panes in different positions by dragging thetabs to a new location.SQL Developer is expected to be included in JDeveloper post release 10.1.3.HelpHelp CentreThe Help Centre Area with in SQL Developer allows you to access all helpinformation, as well as perform a full text search. To display the Help panel, selectHelp from the menu and then select the type of help you require, either: Table ofContents, Full Text Search or Index. You can which between these by using the tabsat the bottom of the Help Centre.Online Help ManualThe Oracle SQL Developer Online Help document, available from the OracleTechnology Network web site, is a PDF form of the Help Centre within SQLDeveloper. You can view or download it from the SQL Developer Home Page on theOracle Website. The following is a direct URL to the Online Help /database/sql developer/pdf/online help.pdfPage: 10
SAGE Computing ServicesOracle SQL Developer WorkshopContext Level HelpSQL Developer includes a context level help feature which can be accessed usingthe F1 key throughout the application. In addition dialogs such as the Create Objectdialog contain context specific help that is accessed by selecting the displayed Helpbutton.Database ConnectionsBefore you can use SQL Developer to access information from an Oracle databaseyou must create a connection. A connection specifies which database you areconnecting to and the username you will use to connect.Set upThe hands on sessions which follow assume that you have followed the set upinstructions provided, and have access to the product and the HR schema. If youhave any problems with the hands on exercises please ask the presenter forassistance.Page: 11
SAGE Computing ServicesOracle SQL Developer Workshop1 Hands On - Getting Started1.1 Invoke SQL DeveloperDisplay Windows Explorer and invoke SQL Developer by double clicking onSQLDeveloper.exe in the directory in which you unzipped the product.1.2 Create a Database Connection1. Right click on Connections within the Connections tab and Select NewDatabase Connection from the menu.2. In the New/Select Database Connection Window enter the followinginformation. Enter sage hr as the connection name, hr for the username andhr for the password. hr is the default password for the hr user but may bedifferent for your instance of the hr schema. Enter your machine name for theHostname and the Port and SID in the basic tab as in the example below.Click the Test button.Page: 12
SAGE Computing ServicesOracle SQL Developer WorkshopThe Status above the Help button should show Success. Once you have asuccessful test click the Connect button to save the new connection.3. You should now see the sage hr connection in the Connections tab on theleft of the SQL Developer application. Expand the sage hr connection, byclicking on the plus sign to its left, to display the list of database object typeswithin your hr schema.When a connection is opened a SQL Worksheet is automatically displayed forthe connection. A SQL Worksheet allows you to execute SQL against thedatabase connection you have just opened. You can also open a new SQLWorksheet by clicking Tools and then SQL Worksheet.4. SQL Developer allows you to perform a series of operations on a databaseconnection including modifying and renaming a connection and invoking aSQL*Plus session. These options can be accessed by right clicking on theconnection and selecting the required operation.Page: 13
SAGE Computing ServicesOracle SQL Developer WorkshopMore on ConnectionsModifying Database ConnectionsThe properties of a Database Connection can easily be updated by right clicking onthe appropriate connection and selecting Properties from the context menu.The properties of all connections can be managed through this dialog box and detailssuch as Username, Password, Hostname and SID can be modified.Connection DetailsList of connectionsdefined in SQL DeveloperThe new connection can be tested via the Test button, then saved by clicking theConnect button.Page: 14
SAGE Computing ServicesOracle SQL Developer WorkshopExporting and Importing ConnectionsThe Export and Import features within SQL Developer make it easy to store a centraldefinition of Database connections which can then be imported when new SQLDeveloper applications are installed, and manage changes centrally. SQL Developerallows you to Export and Import Connections to and from an XML file by right clickingon the Connections node in the Connections Navigator.The Export Connection Descriptors dialog allows you to specify which connectionsyou wish to export, as well as the location and the name of the XML file.Click OK to complete theexport and receive aconfirmation message.In the same way, first invoke the Import Connection Descriptors from the contextmenu, then specify the im
Oracle Reports Workshop - 10G 4 days This course is designed for developers who will be designing or building applications using Oracle Reports. This is a practical course in whic
