Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

ss

Oracle Tips by Burleson 

SQL and SQL*Plus

So, we started our database and loaded the PUBS schema.  Now, let’s dive into SQL (pronounced sequel).   Relational databases and SQL were developed in the early 1970s at IBM.  SQL stands for Structured Query Language .  The idea was to define a common method of storing data that would allow you to retrieve specific information without detailed knowledge of the underlying database engine.  In 1979, Oracle released the first commercial relational database that used SQL.  In 1986, the American National Standards Institute  (ANSI) began publishing SQL standards.  SQL is currently the standard query method of all major database management systems.  In Oracle, SQL is divided into two basic groups: data definition language  (DDL) or data manipulation language  (DML).   DDL is used to define objects within the database just as creating tables or indexes.  DML is used to insert, update and delete data in the database.  Finally, there is data retrieval, which is the SELECT statement. 

The examples in this book are created using SQL*Plus.  SQL*Plus is the command line interface to the Oracle Database.  The first step is to start SQL*Plus and connect to the database.  In Windows, open a terminal window.  In Linux/Unix go to the command line and ensure that the database environment is set.  To start SQL*Plus just enter “sqlplus”.  If the program is not found, make sure the ORACLE_HOME is set in the path.

[oracle@appsvr oracle]$ sqlplus

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jan 20 20:23:44 2005

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

Enter user-name:

My database is called DEVDB.  I can start SQL*Plus and log on in one command like below.

[oracle@appsvr oracle]$ sqlplus pubs/pubs@devdb

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jan 20 20:28:11 2005

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL>

The log on format is:

username/password@database_service_name. 

The database service name is the name of the entry in the TNSNAMES.ora file located in the ORACLE_HOME/ network/admin/tnsnames.ora.  You may need to get with your DBA to setup the TNSNAME.ORA file.  If you are running SQL*Plus on the computer that you installed Oracle on, the installation program created a TNSNAMES entry that matches the database name, in my case DEVDB.

If there was someone watching me log on and I didn’t want them to see my password, I could not include it and SQL*Plus will ask for it and not echo the password to the screen.

[oracle@appsvr oracle]$ sqlplus pubs@devdb

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jan 20 20:29:54 2005

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

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL>

Now that we are connected, lets get to the SQL.  I would recommend that you print a copy of the pubs.ppt slide in the Code Depot for easy reference.  Look at the AUTHOR table.  You can also see what makes up the AUTHOR table by describing it. 

SQL> desc author

 Name                                                      Null?    Type
 ----------------------------------------- -------- ------------ AUTHOR_KEY                                                  VARCHAR2(11)
 AUTHOR_LAST_NAME                                   VARCHAR2(40)
 AUTHOR_FIRST_NAME                                  VARCHAR2(20)
 AUTHOR_PHONE                                             VARCHAR2(12)
 AUTHOR_STREET                                            VARCHAR2(40)
 AUTHOR_CITY                                                 VARCHAR2(20)
 AUTHOR_STATE                                              VARCHAR2(2)
 AUTHOR_ZIP                                                    VARCHAR2(5)
 AUTHOR_CONTRACT_NBR                           NUMBER(5)

This command lists the columns and their definitions. 

SQL*Plus  places each command into a buffer.  You can edit the SQL*Plus buffer, but it is more efficient to create and run scripts (unless you are a vi cowboy) (if you don’t know what that is don’t worry, you are not one).  When you enter a command, SQL*Plus will continue to place it into the buffer until it encounters a semicolon.  This tells SQL*Plus to execute the command.  You can re-execute the previous command by entering a forward slash (/) or by entering RUN.  To list the current buffer enter “L”.  When you enter a carriage return without a semicolon, SQL*Plus assumes you are still entering a command and will provide another line.  The Windows version of SQL*Plus also has a command history that you can cycle through using the Up/Down arrows.

As your queries become more complicated, you will want to be able to edit your queries.  The easiest way to do this is to use the host command.  Enter host notepad <filename> at the SQL prompt to open a text file in notepad.  Write/edit the query, save and close the file, then execute it with the @<filename> command.  To re-edit the file, hit the up arrow to bring the host command back, enter and edit the query.  In this way, you can quickly go from editing to execution and back to editing.


The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks