SQL Developer Start Training for Developers – Part II
Using SQL Developer with Subversion
Preparing the Subversion
SQL Developer is fully integrated with Subversion.
The first step is create a new subversion connection. Just find in the main menu the option Team >> Create Connection.
Choose Manually Create a Subversion Connection:
Fullfill all the fields:
Click oin Test Read Access Button to test your connection:
If you don’t have a repository created, it´ll be necessary create one. Just go to Team Menu and choose Create Local Repository:
That´s it ! Now, it´ll be necessary to do a checkout to copy the files from the subversion to your local repository. Go to Team Menu and Choose Checkout:
It´s done ! Your repository is updated and ready to be used !
Using the Subversion Integration
Now, it´s time to use the Subversion Integration. The first action is to open the Files Window. Go to View Menu and choose Files. A new window will be opened:
Choose the object and right click to open the menu option:
Click in Open and the database object will be available to change.
Now, go to the Team menu. The Subversion options are available !
That´s it ! I won´t talk about Subversion, because is not the focus. Let´s continue to learn about Subversion !! ☺
Working with database objects
SQL Developer give a fully support to work with database objects. Let´s create one and learn a little more about this tool.
Let´s use the code below to test:
--
-- valueType Package SPEC
--
CREATE OR REPLACE PACKAGE valueType AS
PROCEDURE question
(
p_text IN VARCHAR2
);
PROCEDURE question
(
p_text IN NUMBER
);
PROCEDURE question
(
p_text IN DATE
);
FUNCTION answer
(
p_type IN VARCHAR2
)
RETURN VARCHAR2;
FUNCTION answer
(
p_type IN NUMBER
)
RETURN VARCHAR2;
FUNCTION answer
(
p_type IN DATE
)
RETURN VARCHAR2;
END valueType;
/
--
-- valueType Package
--
CREATE OR REPLACE PACKAGE BODY valueType AS
v_answer VARCHAR2(100) := NULL;
PROCEDURE question
(
p_text VARCHAR2
)
IS
BEGIN
DBMS_OUTPUT.put_line('VALUE ' || p_text || ' is ' || answer(p_text));
END question;
PROCEDURE question
(
p_text NUMBER
)
IS
BEGIN
DBMS_OUTPUT.put_line('VALUE ' || p_text || ' is ' || answer(p_text));
END question;
PROCEDURE question
(
p_text DATE
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('VALUE ' || p_text || ' is ' || answer(p_text));
END question;
FUNCTION answer
(
p_type VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
BEGIN
v_answer := 'VARCHAR TYPE';
EXCEPTION
WHEN OTHERS THEN
v_answer := 'ERR';
END;
RETURN(v_answer);
END answer;
FUNCTION answer
(
p_type NUMBER
)
RETURN VARCHAR2
IS
v_answer VARCHAR2(100);
BEGIN
BEGIN
v_answer := 'NUMERIC TYPE';
EXCEPTION
WHEN OTHERS THEN
v_answer := 'ERR';
END;
RETURN(v_answer);
END answer;
FUNCTION answer
(
p_type DATE
)
RETURN VARCHAR2
IS
v_answer VARCHAR2(100);
BEGIN
BEGIN
v_answer := 'DATA TYPE';
EXCEPTION
WHEN OTHERS THEN
v_answer := 'ERR';
END;
RETURN(v_answer);
END answer;
BEGIN
NULL;
END valueType;
/
Just copy the code and press F5 (Run Script).
Now, we must create an anonymous block to call our test package. Open a new SQL Worksheet (ALT+F10).
BEGIN
DBMS_OUTPUT.PUT_LINE('Begin...');
valueType.question('TEST');
valueType.question(1);
valueType.question(TO_DATE('01/01/2017','DD/MM/YYYY'));
DBMS_OUTPUT.PUT_LINE('End...');
END;
Let´s run ! (F5)
Where´s the DBMS_OUTPUT ?
Remember, it´s necessary to open the DBMS_OUTPUT Panel (CTRL+N). Let´s run again !
It´s works !!!
Now, it´s time to learn how to debug.
Debugging a database object
First, open our object clicking ion the name of the package and pressing SHIFT+F4. The Package SPEC will be opened. To open the BODY, click oin 
at the toolbar above. A new Worksheet will be opened with the Package BODY.
Now, click oin line number to create a BREAK POINT. you can create so many breakpoints that you want !
When you run the debug mode, the execution will stop at any break points created.
To execute the debug mode, just click on the red bug icon 
(CTRL+SHIFT+F10). A new window will be opened, and it is divided in three parts:
Target
Show all public functions and procedures available in the Package. Choose one to execute;
Parameters
Show all parameters that belongs to the function/procedure highlighted;
PL/SQL Block
Show the code to execute the function/procedure highlighted;
Now, put a value in Input Value field located in Parameters. Click oin ok after that, and a new worksheet will be opened:
The debug mode ran, but didn´t stop at the break point. What happened ?!?
There´s a simple explanation…To run any database object in debug mode, it´s necessary compile for debug.
Let´s try again ! (CTRL+SHIFT+F10)
The execution stopped at the breakpoint !
Look ! Something changed in toolbar and a new Panel appears.
Debug Toolbar
Terminate Test;
Find Execution Point in Test (ALT+F3);
Step Over in Test (F8);
Step Into in Test (F7);
Step Out In Test (Shift+F7);
Step to End of Method in Test;
Resume in Test (F9);
Pause in Test;
Suspend All Breakpoints in TEST;
Any option in debug toolbar is self-explained ! ☺
Debug Worksheet
Now, we´ll take a look in at all new panels opened in debug mode:
Breakpoints Panel
Show manual and exception breakpoints.
Smart Data Panel
Auto-inspects the last X accessed variables.
Data Panel
All variables are displayed.
Watches
Show all watched created to monitoring.
It´s very easy to create a watch and monitoring the variables. After you start the debug mode, right click over the variable name and choose “Watch”.
You can create any watches that are you want.
Debugging IDE Connection
Show debug executing information.
User Defined Reports
Do you have a library of SQL scripts that you run in a regular way ? Off course you do ! SQL Developer offers an interesting way to storage and run those.
Just click in on “View” option on in main menu and choose “Reports”.
A Reports Panel will be opened.
Creating an user defined Reports
To create a new report, just right click on User Defined Report paste and choose New Report…
A Create Report Window will be opened. Fill the following fields in Master Report option:
Name
Report Name;
Style
Report Style (Table, Chart, Gauge, Code, Script, PL/SQL DBMS_OUTPUT) ;
Description
Report Description ;
Tool Tip
Some tip to run the Report ;
SQL
Your SQL code.
Just click on Properties to change the Report Table Type
Table Type
Horizontal or Vertical.
After that, click oin Apply button and your Report will be ready to run !
Running a Report
Just double click oin your Report Name. A new panel will be opened:
Click oin the green arrow to execute. A window with the parameters will be opened:
You made it ! Congratulations ! ☺
Let´s talk about some tools offered by SQL Developer !
When I was talking about the option Save the Grid, I said that I would´ll speak about it later. Ok, now it´s time now !
SQL Developer allows to transform your SQL code in a Report. Just click oin Save Grid as Report and follow all the steps described in this topic.
SQL Developer Tools
SQL Developer 4.2 brings an interesting pool of tools to support developer work. Let´s talk about some of them. Go to the Tools option in main menu:
Database Copy
It´s highly probably that you don´t have access or permission to do something with that, but, the database copy tool is available !
Database Diff
Database difference is available in SQL Developer, but it´s not a good idea run this tool in PRODUCTION environment.
Database Export
A tool to export database objects ? Off course it has ! Just use the common sense !
Import Data
I almost forgot to talk about Data Import. This functionality is not described in Tools Menu, but exists !
Go to the Connections Panel, find your connection and double click it.
Open the Tables Paste and find the table that you want to import data and right click. Choose Import Data in Menu.
Choose the file to Import and follow the steps:
Easy Peasy !
Monitor Sessions
Do you need to take a look in your sessions running ? Monitor Sessions will help you with that !
Active SQL, Explain Plan, Waits, Server, Client, Application, Contention, Long Opsq…everything available !
Real Time SQL Monitor
That´s my favourite Tool in SQL Developer. The Real Time SQL Monitor tab displays details and performance metrics for all the SQL queries that are running in real time. You can browse through the pages, if there are multiple queries in the Real Time SQL Monitor.
You can see additional details for the selected entry underneath the list. It displays additional performance monitoring information for the selected entry under two tabs namely, Plan Statistics and Metrics. Plan Statistics provides you clear information like drilled down operation by line id, estimated rows, cost, executions, timeline, memory consumption, temporary memory consumption , I/O requests etc.
SQL Developer have bunch of DBA Tools and integration with REST, Hadoop and other technologies. But, it´s a subject to another training ! ☺
I hope you enjoyed and learned a little about SQL Developer and its features !
See you in next doc/article/training !
