Pular para o conteúdo

SQL Developer Start Training for Developers – Part I

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 Developer Start Training

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.

4CbIPWM3a5bVq fmmr030V9

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.

seMpp6r00mLuDZPDF

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 !

zzsCuyUEJ 4ZRYUz071 ZfsM1MmVz5woIV28PpYzo2qyw

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  !

KhTIa7iVigZQDZh5AB8xGDRl2wBfyptruBa8Vw15E yK7eytz6bdalLlWHGrSEfY9un5lbrf6wOpgkNpGTgXT DPGvBGcir6RgyN6ArMnMwBUjJMUYMLcQREoaaXWhuS3elauB r

Startup Script

Do you need to run a script before open a database connection ? Easy !

lVZ mnksWgPKd fgIgCvEDhDZrowLgrr8E6V3G0yetsfFThs qT b5QRnaIBtw0tH8J1m3tLFDthLhtf0X4XKP4UGycZLqltX Twm5owFMZ6GcYDd5MXzTouvVQzxZjWAPqjwq 6

Database Worksheet

Do you prefer to show query results in a new tab ?

QGKfgZKBkfr5EDlpTjqLdfi5Lvpmt823bZzeDgkbikiUjnXQqc1kJBIC1XA3t 2TXo4YO09TK1l6LJCm8ccf00Zp04ZTBLKiHO4q0Z8IrbM0hnrrv8plVzFlhPD9 Q8BfVej5zQW

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.

WoFJL9gSH9tlKo3Pcpo ZXqgddppBz6owqfeqcPMDKFBWSvDtwAsRLYYtx6Sugvty9ou7koeZ7LLZuHda81z4YgLXa2JuDOt3JMczOX5EkdtnF6nRIrR0o 82TdGAcjDKgTcS my
4C7FjMXgIR5ZA3DKl9aNaT4OJPrDzECkWIpcOfy4UtUdK3TAoBYMw8arLbqiUTHjXhAMHJ70kaaVeaGxLCibv9hWrzwFcUUMUJI04WvDlsadSC8M0ok0L5QZAq9dXxn6lujBs QW

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.

elHRR QFIttxCFbL0v328XoqocBygaKJF6thohutIQsQIOAUVVrMnVQ5ztn2kE74EXiXzfkPU450DUn0j3ZY3o6Wwj qG0McvxN2K 1fRw37GHGl5PB7A73kIpgbrWzGcOIJadqO

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.

Jkk8BcyCPpi1A2unQl4p1nQzhTmwul6BbQD5lQhW 5PHs26Nr1bbavmBE7KNMlDyNV7rMcYVXyDHHBaMudgml cG5ogMQTVfSm2uN9fbYtx 7tTohK8 XlCo50BxQm d1rfrjazB

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.

rrUFdhM94h1xOKjfE0A3nxJ9iBx6SwG9Ecde9E2DiUjnE 50nD29J1Ik3oYfJ1kXNwGDQZAo2q C4Jb sbHbsEYs2 RPhSpyPtWgnQ8sPkpiK3cvdVhgtAPmRhgVM35ku0hYJQKl

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.

OUvXXH0CvEfcpsqjcn4 NA5VE2624sH6qfzjDzzfEzMoyQVSIELn5VkB63lfeO8vVWJSADZ7QBpSm6xS7aiHWaxNgDTgs4EgcwqI363EOVb zizXol8pK6 elw3jbLyKG1jGaq8h

Let´s run something…

rFwzr9CGvBHpxaw58QZSbroIb0UhSqLf84c2fV0vYaAGcWLm8Ca5PwSn ivp8SO2dSzJWuBhmoVMX9iuWJir1DAMPvKGlUiMWNCpQx5KzGfUYcza2 6NAzNBwA6S1kGRbF2XI6j2

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:

QDYAm1LEPH4fZL3ZtJEVB61QT90haoWKamt73rut5XAZ KwkiWlQ9a3xOjoyBqneHHN0mN6PpKHg7p8

VVw2 49w1gBKKqEJM3Wcos8vcwXOotj8afHwipp qZTy 3dnxZYLmo SyojEjN3zK2kiLLL0vit6Mpe vsJOR 3X6xMiF65IVZbwwiNf9lMBNMJyiwCNKZbOIXubnY cZIAcA8gnRun Statement (CTRL+ENTER) – Execute the SQL statement;

42oAo 2JEOyZjSxLOh3caaLpt4OcYkkAUcvtJev6quaaVtw9ATQ1QLwW112zu86PYHpDRzYkkFw5tLZClG0OGXdOpPnENFKKF4T SQgHvuyZd0AbtdITTQRMQmxWf a8VOL ukOqRun Script (F5) – Execute a SQL or PL/SQL script. A Script Output Tab will be opened;

UDauTX7lox STzHtI7wV izQIO69rAfYrJTYE29GE9wYY9AjL XFTE8ny7xVsObUlHZckuvalhhopniE7K1 ZR70IUUNHS8qVVBumrI0LTIW0vFLeRRjWdFHG06H 9oyN0LCzqXs

dD6tfeLVNEpuHu3gMh2a4Ns0xlf4u7EdUT6DVVPV MG2glCyeExplain Plan – (F10) – Execute a Explain Plan. An Explain Plan Tab will be opened

0ieXh2cILPkAcB 6fpIzS OkIulg9CdYwMYnbxOBi 7EI5o GkwZjieoOYIGCFGNB 3DP0hVYE Rw OScNOmeLA6MLGJXFCYdHCjEDjFJ2 YcBdDbE XMhAN5fqeCO5PKleaH6KD

8X MzDmNG3rXUFTvU BhqdSBVJTyg3177 k sZ3bQy4iT6MOGk3ghRES Rr1pT nWS gxEshCrNXATTDlZgW6XXr3OsiXiHAmfSpLMqF4IakIn6MeRDXfyAutotrace (F6) – Execute an Autotrace; An Autotrace Tab will be opened;

ko0dhIDIrQN80gGhz WV lDTNkWARv AgPo bGph1213xSAQfksiH35W86QWid79W YaKQI oVmpZZDIxxqHA aLOi0zFuLkX45m9Fn11HArV 5Hh03T000cms26y965A2mHy0Hd

ok1eMxJ4fVchKxlJjylAZLuxFMUdAyREbeuvJMUYmHm44E9YtjfC r7RBLY9YcYyejjOaKRKuTJRoTh99S TKsXJx81oCTE2Z4Z4mxMKBcQa 1mc yAiTmzwUiqBlCMPUn5TT7BKSQL Tuning Advisor (CTRL+F12);

MVhsM vpiunlqIcJ4FCK78ZmA2G1AOxdxCTnEN6ex xm3L4cUWm5Z5oY7L9AY86g9SxWPNL4aFHGBTJTqJlFtjMZtxcI ACommit (F11) and Rollback (F12);

fxBwiUuIihJPeiWZlNkCzJyAQJOVheqQzvVOfOp ILLDACWtstvAmDSUR3vhWRCbHEzQG3UkUnshared SQL Worksheet (CTRL+SHIFT+N) – Create a SQL Worksheet in a new database sessions;

RrgvIuNDoWmjVzrY3vnjbbT4HyVts3kRtZ3PCKjSTqTnUBjJ 9Fhu4W7 s0sBcBdHMPqueugbsVLDz3vl2nllMhxFJAugGV06Z1ldIuUt1VEQZQzvLcIKTwl51xmSmuRGB ockGaClear (CTRL+D) – Clean the SQL Worksheet;

oMzkm3IxdjzlzuZGcgrwPM936Uq8VnZ0WAKO zdDSQ2lyb 1z0 PwXy URt4olbiDkCg8OiDfADqHlzDVBSQL History (F8) – Show the SQL History;

bBO1ZszGI7MagvfKCHMK9nxvl6 ufGxidLzU PchUf9 bROV4 knB IF6KmGdSXwHneOMPDbYit5tiHXJYb2CherP4b9eP1QlkuN3SVeIUzc6wcQf1F9o6jmO5v0gnz2c2kAiXsu

uo1vU BixDBQ3jNktlUkBuX06iGlbFARjyihST8u aaAyn p68nYj3 ambuAz43AkAgbW0P2qzd2so2qb7mIcpQU0njqNSBB0JAw9w2zpVwl1O8cO306u0GAOWdqqh jSEuHffroUpper/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:

rFwzr9CGvBHpxaw58QZSbroIb0UhSqLf84c2fV0vYaAGcWLm8Ca5PwSn ivp8SO2dSzJWuBhmoVMX9iuWJir1DAMPvKGlUiMWNCpQx5KzGfUYcza2 6NAzNBwA6S1kGRbF2XI6j2

Look the Query Builder Panel and click !

fYSpjr1jnwgvE6 6roq30UvGBfmDebc0tx7G4mM 9WqzMyZiO1dtIEN15i9nsvLz1XNcRbVMF1Y7 zKvdQ8xFOBGBkKQZWbSrsUHg3AJSv JuvgffWpEzL bfh5UeFPYWYadOOnj

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:

woxTSmq3PVyqm7AY7YZuZg1f2t1bsoYWKlyv6lhdKdPGSO9UgYip3oNub7BXfLGKleYZZhZp6tWuWKCIx1ZGeWxEWzO41E9hhKOv LFkau dbZdtxJziJIJWVWloWCiG6LGdDAy

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.

p9pDkSnm13HRYLIoaXfo2 WwZKmD3fCJvGEfhykOvx

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(*).

YayxWtA8lxnPyE17byzXPsCPqbt HSAj8iIzxNMT3cVvIqF9exF4O6QW5T6AAU6dzQ7kloqd69LAaGGR5YRQjI FqKfzFxoPVOQT0vu5JkGuY9kWy3gqNs9d lVzqY6IToyWH 8

Find/Highlight

Search or Highlight a String in Query Result

c28yImDIsq0hsQ63EhLb9Cb8BLJFxTlsaO6zWVYGDuvj0CVxhqO1L0

Export

Export the result of SQL into a many types of output formats.

oSfgjsqAJAlmXAmlDTuBgS4jqXA4IE LFBCIyHhK7Oh fgucFllIwUftdEMXOihkuyVsTy28

Let´s execute an anonymous PL/SQL block. The output would be a DBMS_OUTPUT…

Woops….no DBMS_OUTPUT…what´s wrong ?

3NlfAvni9HbecDZd6F1QBqzj c17zp0hQelBWizikgpXD15ZjC5QRyLHFNP2aE5oc7hmlbpBa5vZSi1oY0pFzlWL6TA1EgDmgpkrOGS18zDFG20JZyVcsP0zCOnOE6mTW7f oeQW

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 ^^).

M7fiDWI

A new window will be opened…

Ui8BbWlr4FcqsQN29BcyfZUZIJZPV2LGjYQhj6ZUSDGmcexUWZidqppaf2ufDDINnkwjB eyo6x2tIeFoFCp9l Zs BbGqr0KSSOdhU3kKhr5 k28BRN8IE3BvB34FMEjEXqXqrI

Choose the correct connections and a new tab will appear below the DBMS_OUTPUT Panel.

ed7kNumXvekyNPVsxbjiSmQtmHoamye7HGMg5XHWnJC6

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. 🙂

https://www.profissionaloracle.com.br/2017/04/09/sql-developer-start-training-for-developers-part-ii/

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 5 / 5. Contagem de votos: 20

Sem votos ! Seja o primeiro a classificar !

Marcações:

Deixe um comentário

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

plugins premium WordPress