SQL Developer Start Training for Developers
The purpose of this training is describe the major functionalities of SQL Developer used by Developers and how to configure and use it.
What is SQL Developer ?
Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, edit and debug PL/SQL statements, manipulate and export data, and view and create reports. You can connect to Oracle databases, and you can connect to selected third-party (non-Oracle) databases, view metadata and data, and migrate these databases to Oracle.
Download and Installation
The version used in this training is the 4.2. Despite being a beta version, a lot of bug were fixed comparing with the 4.1 and 4.0.
The first step is download the SQL Developer:
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Choose the Windows 64-bit with JDK 8 included. This version can be used without a real JDK installation, because it has its own JDK (not interfere in other JDK installation in SO) and can be unzipped in any folder and run immediately.
10 Steps SQL Developer Environment Configuration
SQL Developer has a lot of possibilities to configure the environment. But, what options should I be aware of to make my work easier ?
Logs
Choose the folder of your preference to save the SQL Developer logs.
Fonts
Just be sure to choose a font of your preference. You will typeing a lot, so , set up an one that´s pleasing your eyes.
SQL History limits
The number of statements saved is defaulted to 7 days (It´s too low for me). If you want to save some more, change to 500 if you want a few more days.
External Editors
Just spend a few minutes to configure the external editors. A special attention to PDF, because it will allow to open this type of docs directly from BLOB fields.
Shortcut keys
Sort by category and find ‘Other’. You´ll will find some of the best shortcuts undocumented.
Autotrace/Explain Plan
Do you need more information in Autotrace/Explain Plan ? Choose what is important to you !
NLS
Put the correct NLS info and don´t worry..be happy !
File Types
SQL Developer doesn´t open your .PKB or .PKS file ? Just include it !
Startup Script
Do you need to run a script before open a database connection ? Easy !
Database Worksheet
Do you prefer to show query results in a new tab ?
Creating a Database Connection
The environment configuration is done ! Now, it´s time to create the database connection.
Click oin assigned button and choose New Connection.
Fill all fields in according the connection type and press the Test Button. If all information filled in were correct, the Success message will appear in at Status.
After that, press Connect and your first database connection is already done !
Connection Folders
SQL Developer allows the creation of Folders to organize the database connections. Can you imagine how difficult is to find one connection when you have a hundred of them mixed in one single list ?
Just click oin Connection Tab, choose the connection that you want to put in a folder and right click. The following options will appear.
You can create a new folder or user another one that already exists. You can easily drag the connection and drop into an existing folder too. ☺
Connection Colours
It´s possible to change the colour of the connections to alert or simply to organize.
A right click in a existent database connection will open the menu. Choose Properties and the connection configuration window will open.
Just choose the colour of your database connection.
Tip: I´ve used green to DEVELOPER, yellow to ACCEPTANCE and red to PRODUCTION connections.
Connection created. Let´s go to the next step…Running SQL and PL/SQL !
Running a SQL or PL/SQL code
To run our first SQL or PL/SQL code, just right click in your connection. A SQL Worksheet will open immediately.
Let´s run something…
Look ! The Query Result Panel was opened below the SQL Worksheet.
Yeah, I know. I didn´t explained how to execute the SQL code. The magic combination is CTRL+ENTER or the first option on toolbar inside the Worksheet.
Toolbar
Now, we´ll take a look in all SQL Worksheet toolbar options:
Run Statement (CTRL+ENTER) – Execute the SQL statement;
Run Script (F5) – Execute a SQL or PL/SQL script. A Script Output Tab will be opened;
Explain Plan – (F10) – Execute a Explain Plan. An Explain Plan Tab will be opened
Autotrace (F6) – Execute an Autotrace; An Autotrace Tab will be opened;
SQL Tuning Advisor (CTRL+F12);
Commit (F11) and Rollback (F12);
Unshared SQL Worksheet (CTRL+SHIFT+N) – Create a SQL Worksheet in a new database sessions;
Clear (CTRL+D) – Clean the SQL Worksheet;
SQL History (F8) – Show the SQL History;
Upper/Lower/InitCap (CTRL+Quote)– Upper/Lower/Initcap the selected text.
Now you know everything about the SQL Worksheet options.
Let´s go back to the SQL statement:
Look the Query Builder Panel and click !
It´s possible to create a new search in a more interactive way.
Going back to Query Result Tab, a right click will open a new menu:
Save Grid as Report
I´ll explain about it a few chapters ahead. Be patient ! ☺
Single Record View
Show a detailed record view of all columns in our SELECT.
Just look to the pencil in the right side of the column. Probably you´ll understand that is possible to edit the column right ? So, unfortunately it´s not true….
The doc in help menu confirm that is possible to edit a column, since the database user logged have the permissions to do it. But it´s doesn´t work…probably a kind of bug.
Count Rows
Show Count(*).
Find/Highlight
Search or Highlight a String in Query Result
Export
Export the result of SQL into a many types of output formats.
Let´s execute an anonymous PL/SQL block. The output would be a DBMS_OUTPUT…
Woops….no DBMS_OUTPUT…what´s wrong ?
It´s necessary to choose which connections do you want to see the DBMS_OUTPUT in SQL Developer (in case you have many connections open ^^).
A new window will be opened…
Choose the correct connections and a new tab will appear below the DBMS_OUTPUT Panel.
Let´s execute the anonymous PL/SQL block again !
It works !!!
You can open multiples tabs according connections opened in SQL Developer.
Before start to work with the database objects (Packages, Procedures, Functions, etc..), I´ll teach you how to integrate SQL Developer and Subversion. But, only in the second part od this training. 🙂
