Oracle Database XE: SQL and SQL Plus

In this fourth part of a five-part series on Oracle Database XE Administration, you will learn about the SQL*Plus command on Linux and Windows; you’ll also learn how to run SQL commands using the XE home page. This article is excerpted from chapter 28 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

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 the source command 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 named oracle_env.csh instead.

Table 28-1.  Required Linux Environment Variables

 

 

Environment Variable

Description

Required Value

ORACLE_SID

Oracle instance ID

XE

ORACLE_HOME

Oracle software home directory

/usr/lib/oracle/xe/app/oracle/product/ 10.2.0/server

PATH

Search path for executables and shell scripts

$ORACLE_HOME/bin:${PATH}(C shell) or $ORACLE_HOME/bin:$PATH(Bourne, Korn, or Bash shells)

NLS_LANG

Language and territory

Defaults to AMERICAN_AMERICA.US7ASCII

LD_LIBRARY_PATH

Search path for shared libraries

$ORACLE_HOME/lib:$LD_LIBRARY_PATH

SQLPATH

Search path for *.sqlscripts, separated by colons

$ORACLE_HOME/sqlplus/admin

 

 

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 the SQL> prompt after you enter your username and password. In this example, you connect to the database with the SYSTEM user account after starting SQL*Plus, and then query the table structure of the employees table in the HR schema:

[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 the CONNECT command.

  
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 the CONNECT command 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 run SQLPLUS in a Linux terminal window. To bypass the prompts, you can type s qlplus system/yourpassword  in the Run window.

{mospagebreak title=Running SQL Commands Using the XE Home Page}

Regardless of whether you are using Linux or Windows, running SQL commands using the GUI follows the same procedure. Figure 28-5 shows the Oracle Database XE home page for the account RJB (you created this account in Figure 27-8 in Chapter 27). Note that for any account other than SYS , SYSTEM , and other privileged accounts created during installation, you see the Application Builder icon on the Oracle Database XE home page. We’ll tell you more about the icons on the home page later in this chapter in the section “Using Oracle-Supplied Utilities.”


Note  Accounts with system privileges can access the Application Builder menu as long as the account is not SYS or SYSTEM .


To execute SQL commands, use the SQL Commands function in the Oracle Database XE Web environment. Click the SQL icon shown in Figure 28-5, then click the SQL Commands icon on the next page to see the SQL Commands page shown in Figure 28-6. Type this command and click the Run button:

select * from hr.employees;

The SQL Commands window returns all rows from the table HR.EMPLOYEES and formats them using a best-fit method depending on the datatypes and the size of the columns. You can use the scroll bars at the bottom of the browser window to see the remaining columns in the query result.

Alternatively, you can use the down arrow on the right side of each icon to navigate directly to the desired destination page.


Figure 28-5. The Oracle Database XE home page for the user RJB


Figure 28-6.  The SQL Commands page

Please check back next week for the conclusion to this article.
[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan