GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

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 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 !

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Sair da versão mobile