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:
Show all public functions and procedures available in the Package. Choose one to execute;
Show all parameters that belongs to the function/procedure highlighted;
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.
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 ! ☺
Now, we´ll take a look in at all new panels opened in debug mode:
Show manual and exception breakpoints.
Smart Data Panel
Auto-inspects the last X accessed variables.
All variables are displayed.
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:
Report Style (Table, Chart, Gauge, Code, Script, PL/SQL DBMS_OUTPUT) ;
Report Description ;
Some tip to run the Report ;
Your SQL code.
Just click on Properties to change the Report 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:
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 difference is available in SQL Developer, but it´s not a good idea run this tool in PRODUCTION environment.
A tool to export database objects ? Off course it has ! Just use the common sense !
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 !
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 has a lot of other 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 !
Formado em Gestão em Tecnologia da Informação, com sólidos conhecimentos em SQL, PL/SQL, Oracle Forms, Reports e E-Business Suite (AP,AR e GL).
Foi durante 3 anos gerente de tecnologia de grande empresa do setor de saúde, e atualmente atua como Analista de Sistema Sênior na Scania Latin America e também como Diretor-fundador do GPO (Grupo de Profissionais Oracle).