Creating an Oracle Database Connection

Database connections can be created and saved for reuse. Figure below shows the window where connections can be defined. To reach this window, click the “+” symbol visible on the Connections tab. The username and password must both be supplied, but only the username will be saved unless the Save Password check box is selected. Saving a password means that future connections can be made without any password prompt. This is convenient but highly dangerous if there is any possibility that the computer you are working on is not secure. In effect, you are delegating the authentication to your local operating system: if you can log on to that, you can log on to the database.

The Connection Type radio buttons let you choose between three options:

• Basic This prompts for the machine name of the database server, the port on which the database listener will accept connection requests, and the instance (the SID) or the service to which the connection will be made.
• TNS If a name resolution method has been configured, then an alias for the database can be entered, rather than the full details needed by the Basic option.
• Advanced This allows entry of a full JDBC (Java Database Connectivity) connect string. This is completely Oracle independent and could be used to connect to any database that conforms to the JDBC standard.

Selecting Basic requires the user to know how to connect to the database; selecting TNS requires some configuration to have been done on the client machine by the database administrator, in order that the alias can be resolved into the full connection details.

After you enter the details, the Test button will force SQL Developer to attempt a logon. If this returns an error, then either the connection details are wrong, or there is a problem on the server side. Typical server-side problems are that the database listener is not running, or that the database has not been started. Whatever the error is, it will be prefixed with an error number.

SQL Developer

SQL Developer

SQL Developer is a tool for connecting to an Oracle database (or, in fact, some non-Oracle databases too) and issuing ad hoc SQL commands. It can also manage PL/SQL objects. Unlike SQL*Plus, it is a graphical tool with wizards for commonly needed actions. SQL Developer is written in Java and requires a Java Runtime Environment (JRE) to run. Being written in Java, SQL Developer is available on all platforms that support the appropriate version of the JRE. There are no significant differences between platforms.

The general layout of the SQL Developer window is a left pane for navigation around objects, and a right pane to display and enter information. In the figure, the left-hand pane shows that a connection has been made to a database. The connection is just a label chosen when the connection was defined, but most developers will use some sort of naming convention. The branches beneath list all the possible object types that can be managed. Expanding the branches would list the objects themselves. The right-hand pane has an upper part prompting the user to enter a SQL statement and a lower part that will display the result of the statement. The layout of the panes and the tabs visible on them are highly customizable.
The menu buttons across the top menu bar give access to standard facilities:

• File A normal Windows-like file menu, from which one can save work and exit from the tool.
• Edit A normal Windows-like edit menu, from which one can undo, redo, copy, paste, find, and so on.
• View The options for customizing the SQL Developer user interface.
• Navigate Facilities for moving between panes and for moving around code that is being edited.
• Run Forces execution of the SQL statements, SQL script, or PL/SQL block that is being worked on.
• Debug Rather than running a whole block of code, steps through it line by line with breakpoints.
• Source Options for use when writing SQL and PL/SQL code, such as keyword completion and automatic indenting.
• Tools Links to external programs, including SQL*Plus.
• Migrate Tools for converting applications designed for third-party databases (Microsoft Access, SQL Server, and MySQL) to the Oracle environment.
• Help It’s pretty good.

SQL Developer can be a very useful tool, and it is very customizable. Experiment with it, read the Help, and set up the user interface the way that works best for you.

Reference:

• OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051)

SQL*Plus

SQL*Plus is a client-server tool for connecting to a database and issuing ad hoc SQL commands. It can also be used for creating PL/SQL code and has facilities for formatting results. It is available on all platforms to which the database has been ported—the sections that follow give some detail on using SQL*Plus on Linux and Windows. There are no significant differences with using SQL*Plus on any other platform. In terms of architecture, SQL*Plus is a user process written in C. It establishes a session against an instance and a database over the Oracle Net protocol. The platforms for the client and the server can be different. For example, there is no reason not to use SQL*Plus on a Windows PC to connect to a database running on a mainframe (or the other way round) provided that Oracle Net has been configured to make the connection.

SQL*Plus on Linux

The SQL*Plus executable file on a Linux installation is sqlplus. The location of this file will be installation specific but will typically be something like:

/u01/app/oracle/product/db_1/bin/sqlplus

Your Linux account should be set up appropriately to run SQL*Plus. There are some environment variables that will need to be set. These are:

• ORACLE_HOME
• PATH
• LD_LIBRARY_PATH

The ORACLE_HOME variable points to the Oracle Home. An Oracle Home is the Oracle software installation: the set of files and directories containing the executable code and some of the configuration files.

The PATH must include the bin directory in the Oracle Home.

The LD_LIBRARY_PATH should include the lib directory in the Oracle Home, but in practice you may get away without setting this. Figure below shows a Linux terminal window and some tests to see if the environment is correct.

In The Figure , first the echo command checks whether the three variables have been set up correctly: there is an ORACLE_HOME, and the bin and lib directories in it have been set as the first element of the PATH and LD_LIBRARY_PATH variables. Then which confirms that the SQL*Plus executable file really is available, in the PATH. Finally, SQL*Plus is launched with a username, a password, and a connect identifier passed to it on the command line. If the tests do not return acceptable results and SQL*Plus fails to launch, this should be discussed with your system administrator and your database administrator.

The format of the login string is the database username followed by a forward slash character as a delimiter, then a password followed by an @ symbol as a delimiter, and finally an Oracle Net connect identifier. Following the logon, the next lines of text display the version of SQL*Plus being used, which is 11.1.0.6.0, the version of the database to which the connection has been made (which happens to be the same as the version of the SQL*Plus tool), and which options have been installed within the database. The last line is the  prompt to the user, SQL, at which point the user can enter any SQL*Plus or SQL command. If the login does not succeed with whatever username (probably not system) you have been allocated, this should be discussed with your database administrator.

SQL*Plus on Windows

Historically, there were always two versions of SQL*Plus for Microsoft Windows: the character version and the graphical version. The character version is the executable file sqlplus.exe, and the graphical version was sqlplusw.exe. With the current release the graphical version no longer exists, but many developers will prefer to use it and the versions shipped with earlier releases are perfectly good tools for working with an 11g database. There are no problems with mixing versions: an 11g SQL*Plus client can connect to a 10g database, and a 10g SQL*Plus client can connect to an 11g database. Following a default installation of either the Oracle database or just the Oracle client on Windows, SQL*Plus will be available as a shortcut on the Windows Start menu. The navigation path will be as follows:

1. Start
2. Programs
3. Oracle—OraDB11g_home1
4. Application Development
5. SQL Plus

Note that the third part of the navigation path may vary depending on the installation. The location of the executable file launched by the shortcut will, typically, be something like the following:
D:\oracle\app\product\11.1.0\db_2\BIN\sqlplus.exe

However, the exact path will be installation specific. Figure below shows a logon to a database with SQL*Plus, launched from the shortcut. The first line of text shows the version of SQL*Plus, which is the 11.1.0.4.0 beta release, and the time the program was launched. The third line of text is a logon prompt:

Enter user-name:

followed by the logon string entered manually, which was

userName/password@serviceName

A change some people like to make to the shortcut that launches SQL*Plus is to prevent it from immediately presenting a login prompt. To do this, add the NOLOG switch to the end of the command:

sqlplus /nolog

There is no reason not to launch SQL*Plus from an operating system prompt rather than from the Start menu shortcut: simply open a command window and run it. The program will immediately prompt for a logon, unless you invoke it with the NOLOG switch described above.

Reference:

• OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051)