SQL * Plus

The language that enables database management is SQL (Structured Query Language). All tools available on the market for graphical database management, and the ability to “query” the query are only overlaid.

SQL * Plus is the primary Oracle tool for database communication. Enables querying the database and displaying results. In addition to supporting SQL commands, SQL * Plus also has a set of custom commands for program configuration and database connectivity.

To run SQL * Plus, select its name from the start menu or type in the console:

sqlplus

or

sqlplus username / password @ sidbase

SQL * Plus is installed during Oracle Database installation. Although it is a console program and is subjectively perceived – less convenient, it is worth to get acquainted with it. One of the main reasons is that we can control the work of instances and manage data and database objects, even on servers that are devoid of a graphical environment. Production servers rarely make port 1521 (the default listener port), and often this port is blocked at the gateway level. We only have SSH and our only tool is SQL * Plus.

Basic SQL * PLUS commands

CONNECT

The command connects to the specified database. Necessary to start working with the database. We can not perform any operations on this database.

 

The syntax for connecting is as follows:

connect username / password @ binary_name_descriptor

The string followed by @ is the connection name by the tnsnames.ora file  We can also connect to the instance by specifying the IP address of the server and the SID of the database. Example:

connect hr/test@192.168.1.1/xe

In order to log in to the HR user, after unlocking it, we provide the following path:

connect hr / password_which we set @ localhost;

The shorter form of the  connect  command is  conn  :

conn hr / password_why @ localhost;

Using SQL * Plus, you can also specify authorization data when you run the program:

sqlplus  hr / hr @ localhost;

We can connect to SQL * Plus via the listener or directly, even to the disabled instance. We skip in this case @adres_ip_lub_sid eg:

sqlplus hr / hr

In this case, it is necessary to run SQL * Plus from the host console on which the database is installed or via SSH. The advantage is the ability to connect even to a disabled instance (only as SYS). By joining as SYS, we must remember to add the phrase  as sysdba :

sqlplus sys / password as sysdba

or

sqlplus sys/haslo@192.168.1.1/xe as sysdba

DISCONNECT

The command closes the session with connect, does not exit the program.

 

EXIT

The command ends the session, the program window closes.

DESC

This command allows you to get a description of the selected database object.

 

@

Runs the commands specified in the file specified as a command parameter. For example, if  we create a test.sql file on the c: \ partition of  our disk, where we will put the query:

select * from jobs;

We run the command

@c: \ test.sql

Referring to the script created earlier, the effect will be the following screen:

 

SPOOL

With this command, we are able to write to the text file all the executed commands and the results of their actions. All these data will remain in memory only and will not appear in the file until the spool off command ends. Below the console along with the effect.txt file to which the commands have been written together with the results. Note that the actions

in the console were only executed after the spool command was issued.

 

 

SPOOL OFF

Ends the write to file, stops the spool command.

 

Basic SQL * Plus options

SQL * Plus options are set using the SET command  :

SET option_name value

E.g:

autocommit {on | off}

The variable modifies the setting for automatic commit execution or after a series of statements (Default on).

Commit is a command used to validate data changes made within a session. This command can be issued explicitly or implicitly, ie we can disable autocommit  and commit itself by issuing a commit command;  or set auto-commit. If we do not approve changes to the data, the changes will only affect our session and will not be visible to other users.

echo {on | off}

Decides whether commands sent to the server are re-displayed as a result of the command. (Default on)

linesize n

The variable specifies how long the line will be in the SQL * Plus screen buffer. (Default 80)

pagesize n

Specifies the size of the page in rows. (Default 24)

serveroutput {on | off}

Specifies whether messages written during PL / SQL subroutines are to be displayed on the SQL * Plus console. The default is no display.

(Default off)

exercises

1. Connect as a hr user to the local database.

2. Start saving what is happening on the screen to the file.

3. Display the structure of the departments table.

4. Stop saving to file and check what file contains.

5. Create a file that contains a command to describe the table.

6. Execute the command from the file.

7. Disconnect from the database without closing the program window.

SQL DEVELOPER

 

A graphical alternative to SQL Plus is SQL Developer. We can use it provided that we can connect to the Oracle server over the network (we have a listening port open) or if we connect directly to the machine on which the server is installed. This is a free program distributed by Oracle. However, we have to install it separately, it is not supplied with the database.

CREATING, TESTING AND SAVING NEW CONNECTION

 

To create a new connection click on the green cross in the upper left corner of the “Connections” tab. In the following window, enter:

Connection Name  –

username  . Username – the name of the user whose permissions we want to connect to the database.

Password  – password in the User.

Save Password  – select if you want the password to be saved in the program. If you do not check it, you will have to enter it each time you connect to the database.

Hostname  – The host on which the Oracle server resides. If we connect to a database located on the same machine, type “localhost”

Port – the network port under which the database server is listening. The default is 1521

SID  – sid base, for our needs it will always be XE. If you are working on a different version of Oracle than Express Edition, ask your administrator about the database sid.

If we authorize ourselves as an SYS user, we must additionally change the Role setting from  Default to  SYSDBA.

You can check the correctness of the entered data by pressing the Test button. In the left-hand corner of the window, the status of the connection is listed as Success:   if the data allows authorization or  Failure with cause if the connection can not be made.

You can save the connection by clicking  SAVE  and connect by clicking on  CONNECT.

TREE OF OBJECTS

When you start a new connection, you can click on the plus symbol next to the connection name in the Connections tab. You will see an editing window and a list of database objects grouped into categories. By expanding the branches, you can view the list of objects, structures, and even the contents of the table (double-clicking the symbol of the object).

 

By right-clicking on the name of the connection, and then selecting  Open SQL Worksheet, we will run another code editor. By clicking  View -> DBMS Output we will launch a console on which we will display the messages triggered in the PL / SQL subroutines.

 

 

In order for the console to display messages from an active call, we must click on the green cross in its upper left corner and then select the call that we are interested in:

RUNNING THE EDITOR’S SOFTWARE

After writing a program in the editor, we can run it by pressing the “run” button, which looks like “Play” on the tape recorder. If there are several subprograms in the editor, all will be executed. If you want to run only one, select it and click “run” or key combination CTRL + ENTER