Oracle Database XE: SQL and SQL Plus

Using SQL Plus on Linux

To run SQL*Plus, or any Oracle-supplied command, you must have several environment variables set. You can set these environment variables automatically at login by editing your login script, or you can use an environment variable script built when you installed Oracle Database XE. If you want to use your login script to set these variables, you need to define the variables listed in Table 28-1. For Oracle Database XE, most of these variables have required values.

When you install Oracle Database XE, the installer creates two scripts that define the environment variables for you. You can reference these scripts with thesourcecommand in your login script, or run it directly from the command line. This script in Listing 28-1 is for the Bourne, Korn, or Bash shells. The script for the C shell is similar but is namedoracle_env.cshinstead.

Table 28-1.  Required Linux Environment Variables

Listing 28-1. Environment Variable Script /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/ oracle_env.sh

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/ product/10.2.0/server
export ORACLE_HOME
ORACLE_SID=XE
export ORACLE_SID NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh` export NLS_LANG
PATH=$ORACLE_HOME/bin:$PATH
export PATH
if [ $?LD_LIBRARY_PATH ]
then
   LD_LIBRARY_PATH=$ORACLE_HOME/ lib:$LD_LIBRARY_PATH
else
   LD_LIBRARY_PATH=$ORACLE_HOME/lib
fi
export LD_LIBRARY_PATH

Regardless of how you defined your environment variables, you’re finally ready to start a SQL*Plus session. At the Linux command-line prompt, type sqlplus, and you will see theSQL>prompt after you enter your username and password. In this example, you connect to the database with theSYSTEMuser account after starting SQL*Plus, and then query the table structure of the employees table in theHRschema:

[oracle@phpxe ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 13 20:42:22 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: system
Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> describe hr.employees
 Name              Null?     Type
 ----------------- --------- ---------------
 EMPLOYEE_ID       NOT NULL  NUMBER(6) 
 FIRST_NAME                  VARCHAR2(20) 
 LAST_NAME         NOT NULL  VARCHAR2(25)
 EMAIL             NOT NULL  VARCHAR2(25)
 PHONE_NUMBER                VARCHAR2(20)
 HIRE_DATE         NOT NULL  DATE
 JOB_ID            NOT NULL  VARCHAR2(10)
 SALARY                      NUMBER(8,2) 
 COMMISSION_PCT              NUMBER(2,2)
 MANAGER_ID                  NUMBER(6)
 DEPARTMENT_ID               NUMBER(4)

SQL>

Alternatively, you can authenticate your user account on the command line as follows:

[oracle@phpxe ~]$ sqlplus system/yourpassword

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 13 20:42:37 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

SQL*Plus is also available from the Linux KDE and Gnome desktop environments. For KDE, click the K menu (or the red hat on Red Hat Linux) ? Oracle Database 10g Express Edition ? Run SQL Command Line, and you’ll get a SQL prompt in a terminal window, as shown in Figure 28-4. Using this method, you must authenticate using theCONNECTcommand.

  
Figure 28-4.  A SQL*Plus window from the KDE start menu

For the Gnome desktop environment, you follow a similar procedure. Click Applications Menu ? Oracle Database 10g Express Edition ? Run SQL Command Line to get a SQL prompt in a terminal window.

Using SQL*Plus on Windows

The required environment variables are automatically set in the Windows registry when you install Oracle Database XE. Therefore, all Oracle applications in the Windows start menu or at a command-line prompt will run fine without defining these variables yourself.

The first way to access SQL*Plus in a Windows environment is to click Start ? All Programs ? Oracle Database 10g Express Edition ? Run SQL Command Line. You must use theCONNECTcommand to establish a connection, the same as for the Linux environment. It is certainly beneficial to your training efforts to have Oracle applications launch and otherwise behave in the same way regardless of the operating system platform.

Alternatively, you can launch SQL*Plus by clicking Start ? Run and entering sqlplus in the Run window. SQL*Plus prompts you for the username and password just as it does when you runSQLPLUS in a Linux terminal window. To bypass the prompts, you can type sqlplus system/yourpassword in the Run window.