An interesting tidbit of information to know about SQLPlus Worksheet is that it actually invokes the DOS-based SQL*Plus executable to do the real work. When you type a command into the SQL*Plus Worksheet window, and click on the lightning-bolt button, SQLPlus Worksheet invokes SQL*Plus to execute that command. SQLPlus Worksheet is a Java application, and I suspect Oracle designed it this way in order to avoid having to duplicate the existing SQL*Plus behavior in Java. A nice side-benefit is that you can be assured of 100% compatibility between SQL*Plus and SQLPlus Worksheet.
That is comforting if you run a lot of scripts.
Youcan also execute PL/SQL blocks via SQL*Plus, an interactive tool provided with all versions of Oracle.
PL/SQL program units can be precompiled.
Who Can Use SQL*Plus
The SQL*Plus, SQL, and PL/SQL command languages are powerful enough to serve the needs of users with some database experience, yet straightforward enough for new users who are just learning to work with the Oracle Database. The SQL*Plus language is easy to use. For example, to rename a column labelled LAST_NAME with the heading "Family Name", enter the command:
COLUMN LAST_NAME HEADING 'Family Name'
Similarly, to list column definitions for the EMPLOYEES table, enter the command:
DESCRIBE EMPLOYEES
Accessing Oracle Database with SQL Developer
To run the SQL and PL/SQL commands to access Oracle Database, you can use SQL Developer.
All SQL and PL/SQL commands are supported as they are passed directly from the SQL Worksheet to the Oracle Database.
Set Up the JDK Path For SQL Developer
Set the following environmental variables to ensure that the correct jdk is picked up:
- $ORACLE_HOME
- $JAVA_HOME=$ORACLE_HOME/jdk
- $PATH=$JAVA_HOME/bin/:$PATH
To start SQL Developer on which the Java SDK release is installed, use the following commands:
- Change to $ORACLE_HOME/sqldeveloper.
- Run $ ./sqldeveloper.sh.
- Right-Click Connections. In the dialog box, enter a Connection name, username, password, and for the host string,
the name of the database to which you want to connect and click Connect.
Once connected, you can view, create, modify, and delete the database objects using the Connection Navigator or issue any
SQL or PL/SQL command using a SQL Worksheet (From the Tools menu, select SQL Worksheet).
SQL*Plus commands must be interpreted by the SQL Worksheet before being passed to the database.
The SQL Worksheet currently supports a number of SQL*Plus commands.
SQL*Plus commands which are not supported by the SQL Worksheet are ignored and are not sent to the Oracle Database.
SQLPlus Worksheet
SQLPlus Worksheet is a bit different from the other DBA Management Pack modules. It's essentially a command-line application dressed up in a GUI interface. SQLPlus Worksheet, like SQL*Plus and Server Manager, allows you to execute SQL statements and view the results. Its display is divided into two panes. The upper pane is where you enter and edit your SQL statements. The lower pane is where you view the results from executing those statements.
Instead of entering a semicolon or a forward-slash to have a statement executed, you click a lightning-bolt icon instead.
The following simulation shows you how to start SQLPlus Worksheet, execute a simple query, and then exit the application.
Exploring SQLPlus Worksheet
- You need to log into a database. Enter
system in the Username field and press Tab to advance to the password field.
Type dogtoad in the Password field and press Tab to advance to the service field.
Next, tell Enterprise Manager the database to which you want to connect. Type coin in the Service field and click the OK button.
- This is the opening screen that you will see after connecting to the database using SQLPlus Worksheet. The CONNECT command, which SQLPlus Worksheet executed for you, is shown in the top half of the window. From here, you can enter and execute any SQL statement you like. Click on the top half of the window to simulate typing a SELECT query.
- Click on the lightning-bolt to the left to execute the query.
- The query was executed and the results displayed in the bottom half of the screen. The SELECT statement remains in the top half of the screen in case you want to edit it and execute it again. this is the end of the simulation.
SQL*Plus Overview
SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database installation. It has a command-line user interface. There is also the SQL*Plus Instant Client which is a stand-alone command-line interface available on platforms that support the OCI Instant Client. SQL*Plus Instant Client connects to any available Oracle database, but does not require its own Oracle database installation. See the Oracle Call Interface Programmer's Guide for more information on the OCI Instant Client. SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:
- Format, perform calculations on, store, and print from query results
- Examine table and object definitions
- Develop and run batch scripts
- Perform database administration
You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility
of SQL*Plus.
About SQL*Plus
SQL*Plus is the primary command-line interface to your Oracle database. You use
SQL*Plus to start up and shut down the database, set database initialization parameters, create and manage users, create and alter database objects (such as tables and indexes), insert and update data, run SQL queries, and more. Before you can submit SQL statements and commands, you must connect to the database. With SQL*Plus, you can connect locally or remotely. Connecting locally means connecting to an Oracle database running on the same computer on which you
are running SQL*Plus. Connecting remotely means connecting over a network to an Oracle database that is running on a remote computer. Such a database is referred to as a remote database. The SQL*Plus executable on the local computer is provided by a full Oracle Database installation, an Oracle Client installation, or an Instant Client installation.