Getting Productive with SQL Developer

Oracle SQL Developer is a very popular free Graphical user interface. This course will teach you about this tool and see how it makes database development and management a breeze, for database developers, DBAs, and analysts.
Course info
Rating
(16)
Level
Beginner
Updated
Apr 20, 2017
Duration
3h 53m
Table of contents
Course Overview
Overview
Installing Oracle SQL Developer and Connecting to Databases
Browsing the Database, Creating, and Modifying Objects
Easy SQL Querying and Reuse with Snippets and Code Templates
Discovering Database Using Data Modeler
Exporting & Importing Data
Debugging Program Units Using SQL Developer
Executing DBA Tasks
SQL Developer Reports & Command-line Interface
Description
Course info
Rating
(16)
Level
Beginner
Updated
Apr 20, 2017
Duration
3h 53m
Description

This IDE is packed with tons of cool features to significantly boost productivity and making executing the day to day tasks for both the database developers and DBAs a breeze. This tool allows you to write complex queries, explore the database and provides a complete development platform for Oracle PL/SQL applications. In this course, Getting Productive with SQL Developer, you will get an in-depth look at this tool. First, you'll discover how to download, install, and get started with Oracle SQL Developer. Next, you'll explore schemas and creating database objects. Then, you'll discover how to run and debug programs. Finally, you'll learn how to write powerful and visually appealing reports and automating tasks using the SQL Developer Command Line interface. By the end of this course, you'll have a solid foundation to get started and be productive with Oracle SQL Developer.

About the author
About the author

Experienced technologist, with expertise in various aspects of software development lifecycle, architecting software solutions and software development.

More from the author
Oracle REST Data Services
Beginner
5h 31m
Feb 13, 2018
More courses by Pankaj Jain
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hello everyone, my name is Pankaj Jain and I'm very excited to welcome you to my course, Getting Productive With Oracle SQL Developer. Oracle SQL Developer is a very popular, free graphical user interface, widely used by developers and DBAs around the world. This IDE is packed with tons of cool features to significantly boost productivity. And making executing the day to day tasks for both the developers and DBAs a breeze. This tool allows you to write complex queries, explore the database, and provide a complete development platform for Oracle PLC applications. In this course, we will take an in-depth look at this tool. You may be a beginner with Oracle SQL Developer or an experienced user. I'm very confident you will find some useful tips and tricks that you can take away from this course. No prior experience with Oracle SQL Developer is required. Some of the major topics that we will cover include downloading, installing, and getting started with Oracle SQL Developer, exploring schemas and creating database objects, writing complex queries with ease using the visual query builder and intellisense, enhancing productivity using the core templates and snippets, running and debugging program units, reverse engineering the database using data modeler, database copy and div, and other DBF functions, writing powerful and visually appealing reports, and automating tasks using the SQL Developer command line interface. By the end of this course you will have a solid foundation to get started. And be productive with Oracle SQL Developer. I hope you will join me on this journey to learn about this wonderful tool with The Getting Productive with Oracle SQL Developer Course at Pluralsight.

Installing Oracle SQL Developer and Connecting to Databases
Oracle SQL Developer is not only easy to work with it is also very easy to install and get started. Hello, my name is Pankaj Jain and welcome to this module on "Installing Oracle SQL Developer and Connecting to Databases". In this module, we get will get up and started with Oracle SQL Developer. We will broadly cover the following. First, we will see how to download and install Oracle SQL Developer. Secondly, we will see how to connect to Oracle databases. We will see how to connect using the basic connection info as well as by using the tnsnames. ora file. We will see how to export/import connections from SQL Developer which for instance, helps in migrating to a new install. Oracle SQL Developer can also connect to non-Oracle databases, like MySQL, Microsoft SQL Server, Microsoft Access, Sybase, Teradata, and IBM DB2 for object and data browsing with limited worksheet capabilities. However, we will discuss only connecting to Oracle databases. Connecting to other databases is also pretty simple. Alright, so let's get started.

Browsing the Database, Creating, and Modifying Objects
Oracle SQL Developer has a simple and intuitive interface, which allows for easy exploration and creation of database objects. Hello, my name is Pankaj Jain, and welcome to this module on browsing the database, creating and modifying objects. Let us talk about what we are going to cover in this module. In this module, we will look at how easy it is to explore database schemas and objects with SQL Developer. We will take a high-level overview of the tool and its options. We will see how we can browse object types like tables, indexes, triggers, procedures, functions, et cetera, and generate their definitions. Next, we will see how SQL Developer makes it easy for us to modify and create various objects. We will create a bunch of database objects using SQL Developer. We will demonstrate how Oracle SQL Developer makes it easy for us to generate code to run the program units like procedures and function. Oracle SQL Developer increases productivity by letting us focus on writing the code and helping us out with code formatting. We will see how a set our preferences for code formatting. So let's get started.

Easy SQL Querying and Reuse with Snippets and Code Templates
Oracle SQL developer makes writing queries extremely easy, with its IntelliSense and the visual query builder tool. It makes it equally easy to reuse our code, with SQL snippets and code templates. Hello, my name is Pankaj Jain, and welcome to this module on easy SQL querying and reuse with code snippets and code templates. In this module, we will start off with seeing how we can run queries, and how IntelliSense helps make it easy. We will see how we can run our SQL scripts using this tool. We will see how we can configure SQL Developer to create a new result tab for the execution of every query for us to be able to track the results. Oracle SQL Developer preserves our SQL activity and SQL history, and this makes recalling previously-run queries and code and running them again a breeze. Next, we will explore the visual query builder tool which makes relational joins automatically for the tables selected, and allows visually building the query with conditions, sorting, grouping et cetera. Next, we'll talk about SQL snippets, which provides a means for us to store our favorite queries and reuse them. SQL templates allow us to create our code templates and it injects it in the code for us, enabling us to rapidly generate code. So there are a lot of exciting features to talk about. Let's get started.

Discovering Database Using Data Modeler
Oftentimes, when I start working on a new Oracle database, I like to find ways to come up to speed quickly, see visually how the tables are related to each other, generate database documentation, find objects; and, lucky for us, Oracle SQL Developer makes this so easy for us. Hello, everyone; my name is Pankaj Jain, and welcome to this module on Discovering Database Using Data Modeler. In this module, we will start off by looking at Oracle SQL Developer Data Modeler which, just in a few clicks, can help generate a nice relational diagram for the objects in our schema. It can equally easily generate a logical entity relationship diagram for us. These visual diagrams makes discovering existing schemas in our database so much more easier. SQL Developer can also generate the DDL sequence for us. We can quickly export these diagrams and share them with others. Next, we will see how we can equally easily generate database documentation, which is a nice HTML report which allows us to click and see the various objects in our database. Finally, we will see how to find database objects, how to search for specific keywords or references to objects in our source code. This should be a pretty exciting module. Let's get started!

Exporting & Importing Data
Often times, when working with data, you might want to export data out of tables, or bring data in from other sources using popular formats like Excel, or json, or insert scripts. You might also want to export and import the definitions of tables, along with data. And Oracle SQL Developer makes all these tasks a breeze. Hello, my name is Pankaj Jain, and welcome to this module on exporting and importing data using Oracle SQL Developer. In this module, we will start off by seeing how we can export and import data using Excel, one of the most popular formats. We will see how to map the Excel columns to the table columns. Then, we will see how to export and import table definitions, along with data. We will see how we can generate data as insert scripts, as well as generate data in json and pdf formats. Finally, we will take a look at the SQL hints, which Oracle SQL Developer provides to quickly export data in various popular formats, right from the worksheet. If we want to have a more in-depth look at data transfer options for Oracle, check out my course Importing and Exporting Oracle Data for Developers on Pluralsight, where, along with more extensive data transfer coverage options with SQL Developer, we also look at external tables and SQL loader. My course on importing and exporting Oracle data for DBAs on Pluralsight can help you understand the data and metadata transfer options from a DBA perspective using the Oracle Data Pump Tool. All right, let us now explore the capabilities of Oracle SQL Developer for transferring data.

Debugging Program Units Using SQL Developer
When problem happens, being able to find quickly what is wrong is critical for supporting production applications. In this module, you will see how SQL Developer Debugger allows us to quickly get to the root of the problem. Hello, my name is Pankaj Jain. And welcome to this module on debugging program units using SQL Developer. In this module, we will first quickly talk about the various commonly used debugging options for debugging PL/SQL. Then, we will examine our setup with packages and procedures we will use to demonstrate debugging concepts. We will see the prerequisites for us to be able to debug the code. We will examine the SQL developer debugger interface and then see how we can quickly get to the bottom of a problem using the debugger. Next, we will see how to follow the execution flow with the debugger in order to get a better understanding of what is happening, how the code is flowing, leading to the exception. In this module, you would get a good overview of the SQL developer debugger and the other debugging options. However, for a more detailed coverage, please go to my course on Oracle PL/SQL: Transactions, Dynamic SQL, and Debugging on Pluralsight.

Executing DBA Tasks
SQL Developer has a lot of cool features packed in for the database admins right from glancing at the state of the database in a single dashboard view, to getting detailed reporting about various database activities. Hello, my name is Pankaj Jain, and welcome to this module on executing DBA tasks. In this module, we will start off looking at the DBA View, which provides a lot of useful reports and information related to database administrators in one place. We will look at the Instance Viewer, which is a a very cool and dynamic dashboard to see the state of the database in one glance. We will look at the database configuration information, profiles, rules, user information, and storage data, among other things. Next, we will perform some common DBA tasks like viewing existing sessions, killing sessions, gathering table stats, schema stats, running explain plans, etc. Finally, we will see how to create a new user and view and modify permissions of existing users. We will use SQL Developer to do a database copy and find differences between two schemas. Alright, so let's get started.

SQL Developer Reports & Command-line Interface
SQL Developer provides you a lot of useful out-of-the-box reports, as well as lets you create powerful custom reports like master detail, graphical, drill down reports, etc. It also has a command line interface which allows you to script and automate certain tasks. Hello, my name is Pankaj Jain, and welcome to this module on SQL Developer Reports and Command-Line Interface. In this module, we will start off by first looking at some of the out-of-the-box reports provided by SQL Developer. These built-in reports include useful data dictionary reports, which gives you information about your database and its objects, _____ reports, streams, data modeler reports, and other useful categories. Next, we will see how to create some simple user-defined reports. Then, we will go on to build master detail reports, drill down reports, show how to modify the PDF settings and generate PDF reports. We will create some nice chart reports, and SQL Developer provides us some 50+ chart types. We will also see how to manage and share reports with other members on the team. Then we will explore the SQL Developer command line interface which allows you to execute certain tasks like running reports, providing SQL files, loading data, etc. This enables us to automate these tasks. We will also see the Oracle SQL Developer Cart function, which allows us to save the objects we want to deploy in a cart, and then generate scripts from it, which we can easily deploy to multiple environments. Alright, let's get started.