Phone
800-766-1884

ss

Oracle Tips by Burleson

PL/SQL and SQL*Plus

At this point you should have your Oracle database started and loaded with the PUBS schema and it is now time to move to using PL/SQL. In Oracle, SQL is divided into three basic groups of commands including queries, Data Definition Language (DDL) and Data Manipulation Language (DML).

Queries – We use the SELECT operator to perform data extractions

DDL – Data Definition Language is used to define objects within the database such as creating tables or indexes.

DML – Data Manipulation Language is used to insert, update and delete data in the database.

Remember, PL/SQL is a programming language that is tightly integrated with SQL. It has the ability to retrieve and manipulate data, but also to execute DDL and other PL/SQL code.

PL/SQL runs on the database server, not on the local client machine. Unlike external programs, PL/SQL takes advantage of the power and safety of the Oracle database, its security and protection. By executing on the database server, PL/SQL also avoids the most time consuming part of interacting with the database, the passing of result-sets back-and-forth across the network.

The examples in this book are created using SQL*Plus, the command line interface to the Oracle Database. You may chose to use one of the PL/SQL integrated development environments such as TOAD, PL/SQL Developer or Oracle’s new Raptor . For a new developer we recommend using SQL*Plus because it is simple and will keep you from confusing PL/SQL errors and the tool errors. You will find that the first thing a developer needs to do is understand the Oracle and PL/SQL error messages . Remember, a development tool can make error messages harder to understand.

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 the “sqlplus” command. If the program is not found, make sure the ORACLE_HOME is set in the path.

The example database is called DEVDB. You 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 tnsnames.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 SID, in my case DEVDB.

If there was someone watching you log on and you didn’t want them to see the password, do not included 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>

We recommend that you print a copy of the pubs.ppt slide in the code depot for easy reference. Look at the author table on the slide. You can also see what makes up the author table by describing it from the SQL*Plus prompt.

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 statement including an anonymous PL/SQL block 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, the wildly popular UNIX editor) When you enter a SQL statement, SQL*Plus will continue to place it into the buffer until it encounters a semicolon. This tells SQL*Plus to execute the command. In PL/SQL, each line is terminated in a semicolon.

SQL*Plus waits until you enter a forward slash to send the buffer to the database for execution. You can re-execute the previous statement or block 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.

PL/SQL deals with blocks of code or scripts. You must use a text editor to create the script and then use SQL*Plus to execute it. The easiest way to do this is to use the host command. On Windows, enter “host notepad <filename>” at the SQL*Plus prompt to open a text file in notepad. Write/edit the query or PL/SQL block, save and close the file, then execute it with the “@<filename>” command. On Windows 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. Sadly, Linux/Unix will not scroll through previous commands with the arrow keys without using a separate utility.

To get a list of SQL*Plus command type “help index”.

SQL> help index

Enter Help [topic] for help.

@             COPY         PAUSE                    SHUTDOWN
@@            DEFINE       PRINT                    SPOOL
/             DEL          PROMPT                   SQLPLUS
ACCEPT        DESCRIBE     QUIT                     START
APPEND        DISCONNECT   RECOVER                  STARTUP
ARCHIVE LOG   EDIT         REMARK                   STORE
ATTRIBUTE     EXECUTE      REPFOOTER               TIMING
BREAK         EXIT         REPHEADER                TTITLE
BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
CHANGE        HELP         RESERVED WORDS (PL/SQL) VARIABLE
CLEAR         HOST         RUN                      WHENEVER

For more detail use “help <command>”

SQL> help column

COLUMN
------

Specifies display attributes for a given column, such as:
     - text for the column heading
     - alignment for the column heading
     - format for NUMBER data
     - wrapping of column data

Also lists the current display attributes for a single column or all columns.

COL[UMN] [{column | expr} [option ...] ]

where option represents one of the following clauses:
     ALI[AS] alias
     CLE[AR]
     ENTMAP {ON|OFF}
     FOLD_A[FTER]
     FOLD_B[EFORE]
     FOR[MAT] format
     HEA[DING] text
     JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
     LIKE {expr | alias}
     NEWL[INE]
     NEW_V[ALUE] variable
     NOPRI[NT] | PRI[NT]
     NUL[L] text
     OLD_V[ALUE] variable
     ON|OFF
     WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

It bears repeating that PL/SQL is tightly coupled to SQL. If you are not comfortable with SQL you will find PL/SQL very difficult. This book uses Oracle’s SQL functions throughout the examples. These SQL functions are covered in detail in the companion book Easy Oracle SQL.

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3

John Garmany

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