book

Oracle SQL Developer

by Ajith Narayanan, Susan Harper

January 2016

Intermediate to advanced

344 pages

English

Packt Publishing

Read now

Unlock full access

Support files, eBooks, discount offers, and moreWhy subscribe?Free access for Packt account holdersInstant updates on new Packt books
What this book covers

Downloading the example codeDownloading the color images of this bookErrataPiracyQuestions
Preparing your environmentFinding and downloading the softwareDownloading and installing the Java Development KitInstalling and starting SQL DeveloperWorking with different platformsMicrosoft WindowsAlternative platformsMac OS XLinuxMigrating settings from a previous releaseMaintaining your environmentVerifying the current releaseUsing Check for UpdatesManaging the system folder and other filesAlternative installations of SQL DeveloperOracle JDeveloperOracle Database 12cTroubleshootingRemoving extensionsResetting shortcut keysReset the environment, do not reinstall the product
Sample schemasCreating your first connectionUsing basic commands in the SQL WorksheetBrowsing and updating dataRunning reports
Managing SQL Developer windowsTiling windowsSplitting documentsMaximizing detailResetting the window layout
Working with the Connections navigatorOpening connections
General display editorsWorking with the display editorsUsing the SQL display editor
Working with PL/SQL objectsAccessing objects you don't ownOther usersSynonyms and filtersRecycle Bin
Editing objects
Execution planAutotrace paneDBMS Output paneOWA output paneQuery Builder
Introducing SQL Developer reportsWho can run reports?When do you use reports?Tree layout for ease of navigationRunning a reportPrivileges required for running reportsRunning data dictionary reportsGetting to know the data dictionaryAbout the databaseReviewing privileges and security reportsAssisting with quality assuranceUsing PL/SQL reportsRunning ASH and AWR reportsMigration reportsApplication Express reportsData Modeler reportsMore report categoriesRunning reports from other menusThe Monitor Sessions reportManaging the databaseReal-time SQL monitoring
Getting startedCreating foldersStoring reportsCreating general reportsBuilding general tabular reportsAdding bind variablesDrilling down through reportsCreating a drill-down reportMaster-detail reportsCreating master-detail reportsAdding chartsImporting and exportingSharing reports through user-defined extensions
Creating, compiling, and debugging PL/SQLWriting PL/SQL in the SQL WorksheetUsing code insightUsing code snippets and code templatesWorking with triggersUsing the Create Trigger dialogCreating INSTEAD OF triggersControlling triggersAdding functions or proceduresWorking with errorsCreating packagesCreating the bodyRefactoring codeSearching for codeFinding a DB ObjectDebugging PL/SQL codeUsing the debugging mechanism in SQL DeveloperUsing the Oracle debugging packagesDebuggingWorking with Explain PlanControlling the Explain Plan outputExecution plan detailsUsing SQL reportsRunning the Top SQL reports
Database instance viewer
Backup jobsBackup setsImage copiesRMAN settingsScheduled RMAN actions
Screen reader readability
Exporting dataExporting instance dataExporting table dataSetting up the export fileExporting SQL DMLExporting to HTMLSupporting export for SQL*LoaderExporting to Microsoft ExcelExporting to XMLExporting DDL (Metadata)Exporting table DDLSelecting multiple tables for DDL exportUsing the Database Export wizard to export DDL and dataStarting the export wizardSelecting objects for generationSpecifying objectsRunning the script
Importing data from SQL script filesImporting data from XLS and CSV filesCreating a table on XLS import
Working with Oracle connectionsUsing alternative Oracle connection typesReviewing the Basic connectionAccessing the tnsnames.ora fileAccessing LDAP server detailsCreating advanced connections with JDBC URLsConnecting to Oracle TimesTenReviewing JDBC driversOracle JDBC thin driver (Type IV driver)Oracle JDBC thick driver (Type II driver)SQL Developer's shipped driversFixing connection errorsUsing different authentication methodsOS AuthenticationSetting the database environment variablesCreating a connection using OS AuthenticationUsing proxy authenticationUsing the Kerberos authenticationImplementing Kerberos authentication in SQL DeveloperKerberos authentication using the thin JDBC driver
Setting up JDBC driversUsing Check for UpdatesManually adding JDBC drivers
Oracle SQL Developer Data ModelerFeature overviewIntegrated architecture
Oracle clients and JDBC driversCreating your first modelsImporting from the Data DictionaryCreating a database connectionUsing the Import WizardReviewing the results
Formatting the elementsControlling the layoutAdjusting the level of detail displayedAdjusting the width and height across the model
The flow of workStarting with analysis (top down)Importing existing models (bottom up)Building the relational modelLogical modelsCreating an ERDCreating entitiesAdding attributesWorking with relationshipsCreating constraints, domains, and setting default valuesWorking with domainsCreating domainsUsing domains to implement check constraintsIntroducing forward and reverse engineeringForward engineeringReverse engineering modelsCreating relational modelsBuilding the physical modelImporting a schema from the Data DictionaryGenerating the DDLCreating a new model
Introducing extensibilityExtensibility in SQL DeveloperWho builds extensions?Why extend?SQL Developer XML extension typesAdding an XML extensionSharing user-defined reportsAdding display editorsExamples of display editorsInclude subpartitionsUse restricted queriesAdd display editors for new navigator nodesBuilding the XML file for a display editorWorking with context menusAdding a context menu to the connections menusPassing parametersCreating a utility using context menusIncluding the user-defined extension for context menusAdding new nodes to the Connections navigatorIncluding user-defined extensions for a navigator nodeAdding a new tree to the navigator
Controlling existing extensionsAdding in new Java extensionsRemoving extensionsSharing extensions
Setting the sceneSetting up Application ExpressCreating a workspace and database schemaCreating an Application Express userBrowsing applications in SQL DeveloperCreating a connection in SQL DeveloperBrowsing and working with schema objectsBrowsing the applicationsDrilling down into lists of valuesFocusing on pagesMapping objects from SQL Developer to Application ExpressTuning SQL and PL/SQL code using SQL DeveloperWorking with Region Source SQLTuning with Explain PlanWorking with PL/SQL codeReplacing the anonymous block in Application ExpressManaging applications in SQL DeveloperImporting applicationsModifying applicationsDeploying applicationsControlling servicesReporting on applications using SQL Developer
Introducing SQL Developer MigrationsAn overview of the migration processOffline or online migration choicesSupported third-party databases
Setting up JDBC driversCreating third-party connectionsAccessing non-Oracle databasesBrowsing database objectsManaging the repositorySetting up the repository owner:Creating the repositoryAssociating a repository with a userPlanning database connectionsSetting up the source database connectionSetting up the target database connection
Using Quick MigrateThe migrationVerifying the resultsReviewing the logsGenerating scriptsMigration reports
Data source nodeCreating a new projectCreating a new workflowAddition of nodes to the workflowLink nodesRun nodesView reports
JSON Query node
About Oracle REST Data Services
Connecting to Oracle REST Data ServicesREST Data Services navigator – global and database settings
RESTful services terminologyRESTful services requirements and setup

Content preview from Oracle SQL Developer

Finding database objects

We can find all types of database objects (tables, columns, declarations within functions, or procedures) associated with an Oracle database connection; after finding these objects, we can edit them quickly with editing panes. To open the Find Database Object pane, right-click a connection name in the Connections navigator and select Find DB Object. You can also click on VIEW and then on FIND DB Object.