Home arrow Site Administration arrow Oracle Database XE: SQL and SQL Plus

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

TABLE OF CONTENTS:
  1. Oracle Database XE: SQL and SQL Plus
  2. Running SQL Commands Using the XE Home Page
By: Apress Publishing
Rating: starstarstarstarstar / 1
October 14, 2010

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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

 

 

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



 
 
>>> More Site Administration Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

SITE ADMINISTRATION ARTICLES

- Coding: Not Just for Developers
- To Support or Not Support IE?
- Administration: Networking OSX and Win 7
- DotNetNuke Gets Social
- Integrating MailChimp with Joomla: Creating ...
- Integrating MailChimp with Joomla: List Mana...
- Integrating MailChimp with Joomla: Building ...
- Integrating MailChimp with Joomla
- More Top WordPress Plugins for Social Media
- Optimizing Security: SSH Public Key Authenti...
- Patches and Rejects in Software Configuratio...
- Configuring a CVS Server
- Managing Code and Teams for Cross-Platform S...
- Software Configuration Management
- Back Up a Joomla Site with Akeeba Backup

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: