1.3 SQL Statements

«« Previous
Next »»

The primary means of communicating with Oracle Database is by submitting SQL statements.

1. Submitting Commands and SQL to the Database


There are several ways to submit SQL statements and commands to Oracle Database.

  • Directly, using the command-line interface of SQL*Plus
  • Indirectly, using a graphical user interface, such as Oracle Enterprise Manager Database Express (EM Express) or Oracle Enterprise Manager Cloud Control (Cloud Control)

With these tools, you use an intuitive graphical interface to administer the database, and the tool submits SQL statements and commands behind the scenes.

  • Directly, using SQL Developer

Developers use SQL Developer to create and test database schemas and applications, although you can also use it for database administration tasks.

Oracle Database also supports a superset of SQL, which includes commands for starting up and shutting down the database, modifying database configuration, and so on.

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


3. Connecting to the Database with SQL*Plus


When you connect with SQL*Plus, you are connecting to the Oracle instance.

  • About Connecting to the Database with SQL*Plus

Oracle Database includes the following components: the Oracle Database instance, which is a collection of processes and memory, and a set of disk files that contain user data and system data.

Each instance has an instance ID, also known as a system ID (SID). Because there can be multiple Oracle instances on a host computer, each with its own set of data files, you must identify the instance to which you want to connect. For a local connection, you identify the instance by setting operating system environment variables. For a remote connection, you identify the instance by specifying a network address and a database service name. For both local and remote connections, you must set environment variables to help the operating system find the SQL*Plus executable and to provide the executable with a path to its support files and scripts.

  • Step 1: Open a Command Window

Take the necessary action on your platform to open a window into which you can enter operating system commands.
1. Open a command window.

  • Step 2: Set Operating System Environment Variables
Depending on your platform, you may have to set environment variables before starting SQL*Plus, or at least verify that they are set properly.

For example, on most platforms, ORACLE_SID and ORACLE_HOME must be set. In addition, it is advisable to set the PATH environment variable to include the ORACLE_HOME/bin directory. Some platforms may require additional environment variables:
  • On the UNIX and Linux platforms, set environment variables by entering operating system commands.
  • On the Windows platform, Oracle Universal Installer (OUI) automatically assigns values to ORACLE_HOME and ORACLE_SID in the Windows registry.
If you did not create a database upon installation, OUI does not set ORACLE_SID in the registry; after you create your database at a later time, you must set the ORACLE_SID environment variable from a command window.

UNIX and Linux installations come with two scripts, oraenv and coraenv, that you can use to easily set environment variables. For more information, see Administrator's Reference for UNIX Systems.

For all platforms, when switching between instances with different Oracle homes, you must change the ORACLE_HOME environment variable. If multiple instances share the same Oracle home, you must change only ORACLE_SID when switching instances.

Example 1-1 Setting Environment Variables in UNIX (C Shell)

setenv ORACLE_SID orcl
setenv ORACLE_HOME /u01/app/oracle/product/12.1.0/db_1
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/lib

Example 1-2 Setting Environment Variables in Windows

SET ORACLE_SID=orawin2

Example 1-2 assumes that ORACLE_HOME and ORACLE_SID are set in the registry but that you want to override the registry value of ORACLE_SID to connect to a different instance.

On Windows, environment variable values that you set in a command prompt window override the values in the registry.
  • Step 3: Start SQL*Plus
Start SQL*Plus.
  1. 1Do one of the following:
    • Ensure that the PATH environment variable contains ORACLE_HOME/bin.
    • Change directory to ORACLE_HOME/bin.
  2. Enter the following command (case-sensitive on UNIX and Linux):
    • sqlplus /nolog
  • Step 4: Submit the SQL*Plus CONNECT Comman
You submit the SQL*Plus CONNECT command to initially connect to the Oracle instance or at any time to reconnect as a different user.

1. In SQL*Plus, submit the CONNECT command.

Example 1-3 Connecting to a Local Database User

This simple example connects to a local database as user SYSTEM. SQL*Plus prompts for the SYSTEM user password.

connect system

Example 1-4 Connecting to a Local Database User with SYSDBA Privilege

This example connects to a local database as user SYS with the SYSDBA privilege. SQL*Plus prompts for the SYS user password.

connect sys as sysdba

When connecting as user SYS, you must connect AS SYSDBA.

Example 1-5 Connecting to a Local Database User with SYSBACKUP Privilege

This example connects to a local database as user SYSBACKUP with the SYSBACKUP privilege. SQL*Plus prompts for the SYSBACKUP user password.

connect sysbackup as sysbackup

When connecting as user SYSBACKUP, you must connect AS SYSBACKUP.

Example 1-6 Connecting Locally with SYSDBA Privilege with Operating System Authentication

This example connects locally with the SYSDBA privilege with operating system authentication.

connect / as sysdba

Example 1-7 Connecting with Easy Connect Syntax

This example uses easy connect syntax to connect as user salesadmin to a remote database running on the host dbhost.example.com. The Oracle Net listener (the listener) is listening on the default port (1521). The database service is sales.example.com. SQL*Plus prompts for the salesadmin user password.

connect salesadmin@"dbhost.example.com/sales.example.com"

Example 1-8 Connecting with Easy Connect Syntax with the Service Handler Type Indicated

This example is identical to Example 1-7, except that the service handler type is indicated.

connect salesadmin@"dbhost.example.com/sales.example.com:dedicated"

Example 1-9 Connecting with Easy Connect Syntax with a Nondefault Listener Port

This example is identical to Example 1-7, except that the listener is listening on the nondefault port number 1522.

connect salesadmin@"dbhost.example.com:1522/sales.example.com"

Example 1-10 Connecting with Easy Connect Syntax with the Host IP Address

This example is identical to Example 1-7, except that the host IP address is substituted for the host name.

connect salesadmin@"192.0.2.5/sales.example.com"

Example 1-11 Connecting with an IPv6 Address

This example connects using an IPv6 address. Note the enclosing square brackets.

connect salesadmin@"[2001:0DB8:0:0::200C:417A]/sales.example.com"

Example 1-12 Connecting by Specifying and Instance

This example specifies the instance to which to connect and omits the database service name. A default database service must have been specified, otherwise an error is generated. Note that when you specify the instance only, you cannot specify the service handler type.

connect salesadmin@"dbhost.example.com//orcl"

Example 1-13 Connecting with a Net Service Name

This example connects remotely as user salesadmin to the database service designated by the net service name sales1. SQL*Plus prompts for the salesadmin user password.

connect salesadmin@sales1

Example 1-14 Connecting with External Authentication

This example connects remotely with external authentication to the database service designated by the net service name sales1.

connect /@sales1

Example 1-15 Connecting with SYSDBA Privilege and External Authentication

This example connects remotely with the SYSDBA privilege and with external authentication to the database service designated by the net service name sales1.

connect /@sales1 as sysdba

Example 1-16 Connecting as a User with a Service Name

This example connects remotely as user salesadmin to the database service designated by the net service name sales1. The database session starts in the rev21 edition. SQL*Plus prompts for the salesadmin user password.

connect salesadmin@sales1 edition=rev21

2. Syntax of the SQL*Plus CONNECT Command

Use the SQL*Plus CONNECT command to initially connect to the Oracle instance or to reconnect to the Oracle instance.

Syntax of the SQL*Plus CONNECT Command
Use the SQL*Plus CONNECT command to initially connect to the Oracle instance or to reconnect to the Oracle instance.

Syntax

CONN[ECT] [logon] [AS {SYSOPER | SYSDBA | SYSBACKUP | SYSDG | SYSKM}]

The syntax of logon is as follows:

{username | /}[@connect_identifier] [edition={edition_name | DATABASE_DEFAULT}]

When you provide username, SQL*Plus prompts for a password. The password is not echoed as you type it.

The following table describes the syntax components of the CONNECT command.

Syntax Component Description
/ Calls for external authentication of the connection request. A database password is not used in this type of authentication. The most common form of external authentication is operating system authentication, where the database user is authenticated by having logged in to the host operating system with a certain host user account. External authentication can also be performed with an Oracle wallet or by a network service.
AS {SYSOPER | SYSDBA | SYSBACKUP | SYSDG | SYSKM} Indicates that the database user is connecting with an administrative privilege. Only certain predefined administrative users or users who have been added to the password file may connect with these privileges.
username A valid database user name. The database authenticates the connection request by matching username against the data dictionary and prompting for a user password.
connect_identifier (1) An Oracle Net connect identifier, for a remote connection. The exact syntax depends on the Oracle Net configuration. If omitted, SQL*Plus attempts connection to a local instance. A common connect identifier is a net service name. This is an alias for an Oracle Net connect descriptor (network address and database service name). The alias is typically resolved in the tnsnames.ora file on the local computer, but can be resolved in other ways.
connect_identifier (2) As an alternative, a connect identifier can use easy connect syntax. Easy connect provides out-of-the-box TCP/IP connectivity for remote databases without having to configure Oracle Net Services on the client (local) computer.
Easy connect syntax for the connect identifier is as follows (the enclosing double-quotes must be included): 

"host[:port][/service_name][:server][/instance_name]" 

where:
1. host is the host name or IP address of the computer hosting the remote database. Both IP version 4 (IPv4) and IP version 6 (IPv6) addresses are supported. IPv6 addresses must be enclosed in square brackets. See Oracle Database Net Services Administrator's Guide for information about IPv6 addressing.

2. port is the TCP port on which the Oracle Net listener on host listens for database connections. If omitted, 1521 is assumed.
service_name is the database service name to which to connect. Can be omitted if the Net Services listener configuration on the remote host designates a default service. If no default service is configured,

3. service_name must be supplied. Each database typically offers a standard service with a name equal to the global database name, which is made up of the DB_NAME and DB_DOMAIN initialization parameters as follows:

DB_NAME.DB_DOMAIN 

If DB_DOMAIN is null, then the standard service name is just the DB_NAME. For example, if DB_NAME is orcl and DB_DOMAIN is us.example.com, then the standard service name is orcl.us.example.com.

4. server is the type of service handler. Acceptable values are dedicated, shared, and pooled. If omitted, the default type of server is chosen by the listener: shared server if configured, otherwise dedicated server. instance_name is the instance to which to connect. You can specify both
service name and instance name, which you would typically do only for Oracle Real Application Clusters (Oracle RAC) environments. For Oracle RAC or single instance environments, if you specify only

5. instance name, you connect to the default database service. If there is no default service configured in the listener.ora file, an error is generated.You can obtain the instance name from the instance_name initialization parameter.
edition={edition_name | DATABASE_DEFAULT} Specifies the edition in which the new database session starts. If you specify an edition, it must exist and you must have the USE privilege on it. If this clause is not specified, the database default edition is used for the session.

«« Previous
Next »»