How to use sql developer
Now there is a standalone graphic user interface that you can use to develop your SQL with Oracle databases. The many things you do in Oracle 10G XE can also be carried out with the Oracle SQL Developer. It is expected to enhance productivity in your Oracle applications.
You can use Oracle SQL Developer to connect, run, and debug PL/SQL. It can run on at least three different operating systems. This tutorial exposes you to this useful interface and shows you some basic tasks that gets you started with this tool. With Oracle 10 XE and this tool, you will have expanded your skill set.
Downloading and installing the Oracle SQL Developer
This version 1.1 December release can be downloaded from theOracle site after accepting the license agreement. This 63MB down load will install a zip folder which you may place in any directory. From the unzipped contents, double click on theSQLDeveloper.exe.
The User Interface
On a Windows machine you may get a security warning which you may safely override and click on Run. This opens up the splash window shown in the next picture followed by the Oracle SQL Developer interface shown in the picture that follows.
In this highly reduced image you may not be able to discern the various details. It has a main menu at the very top where you can access File, Edit, View, Navigate, Run, Debug, Source, Tools and Help menus. Immediately below the main menu on the left you have a tabbed window with two tabs, Connections and Reports. This will be the item you have to contend with since most things start only after establishing a connection. The connection brings with it the various related objects in the databases. The next picture shows the drop-down of the Viewmain menu, where you can see other details such as links to the debugger, reports, connections and snippets.
Snippets are often-used SQL statements or clauses that you may want to insert. You may also save your snippets by clicking on the bright green plus sign in the window shown, which opens up the superposed Save Snippet window.
In the Run menu item you can run files as well as look at the Execution Profile. The debug menu item has all the necessary hooks to toggling break points: step into, step over, step out and step to End of methods, etc., including garbage collection and clean up as shown in the next picture. Tools give access to External Tools that can be launched, Exports both DDL and data, schema diff, etc. Help gives you both full text search and indexed search.
{mospagebreak title=Connecting to databases}
This tutorial shows how to connect to Oracle 10G XE. SQL Developer supports other databases for which you can find the correct syntax for connection Information.
Connecting to the Oracle 10G XE database
It will be helpful if you refresh your Oracle 10G XE knowledge or read the several Oracle 10G XE articles on the DevShed site. This is a good place for you to look at new developments, scripts, UI description, etc. The connection will be made to the Oracle 10G XE with the credentials hr, hr. Click on the "Connections" icon with the bright green plus sign as shown.
This opens up the next window, New/Select Database Connection. This is where you must supply all the information. As you can see it has identified a resident Oracle 10G XE on the machine. Of course you need to configure it further. In addition to Oracle it can connect to MySQL, MS Access, and SQL Server as well.
On the left hand side of this window you will generate the Connection Name and Connection Details once you fill in the appropriate information on the right. Connection name is what you supply; to get connected you need to have a username and password as well. If you want, you can save the password to avoid providing it again and again. At the bottom of the screen you can save the connection, test it and connect to it. There is also access to online help.
Insert the following information:
Connection Name: Test10G (or whatever)
Username: hr
Password: hr (turns to **)
Save Password is selected.
Oracle is default
Connection type: Basic
With this click on the Test button at the bottom. A testing window pops up with a progress bar. Now click on the Connect button, which establishes the connection as shown. When you click on the + node of the Connections for Test10G you can expand the node to see its content as shown. You can further expand the various nodes to see more details.
{mospagebreak title=Verifying objects in the database}
Expand the Tables node to view all the tables that exist. Expand the NorthEmployees table and you can click any column and modify some of the properties as shown.
You may click on any table to see the data it contains as shown in the next picture.
This way you can see all related Information for the table like the columns, Data, Constraints, etc.
{mospagebreak title=Creating a SQL Query}
Selecting from the EMPLOYEES table
Type in Select * from EMPLOYEES in the area with the titleEnter SQL Statement. Since there is only one database you may not need choose a database. The active database should be in a drop-down just above the SQL statement window on its right. Then click on the bright green arrow button (pointing to the right). This executes the statement and you can see the results in the bottom window as shown.
You can make changes to the statement and look at the results as shown.
After verifying the results you may look at SQL Output, or use Explain to provide the execute plan for the SQL statement. The Autotrace tab produces the trace information for the statement. DBMS Output produces the DBMS_Output package information. OWA Oracle Web Agent [MOD_PLSQL] is an Apache (Web Server) extension module that enables you to create dynamic web pages from PL/SQL packages and stored procedures.
Summary
Oracle SQL Developer is a convenient standalone user tool for working with Oracle and other databases. This tutorial has only described the bare essentials for using this tool. For connection to other databases the connection information has to be correct. While connecting to the Access 2003 Northwind database an exception was thrown (No Read Access to System tables) although they were verified to have access to the Admin. They appear to be MS Access ODBC driver related for the version used.
No comments:
Post a Comment