everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

SQL

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19
<< Previous

Chapter # 06

Next >>


 

Command Line editing

 

Go to MS-DOS and make a new directory to be used for your new scripts and programs.

Login to SQLPlus from DOS

Change directory to the root directory.

DOS> cd ..

 

Make a directory called "oracle".

DOS> mkdir oracle

 

Change the directory to the oracle directory.

DOS> cd oracle

 

List directory. Notice that there is nothing in it.

DOS> dir

 

Login to sqlplus as "oracle" password "learning"

SQL> sqlplus oracle/learning

 

SQLPlus default directory

From now on the "oracle" directory is a default directory for "SQLPLUS"

 

Query the dept table.

SQL> SELECT deptno, dname, loc FROM dept;

 

Command line editing

You should know command line editing since it is a universal editor regardless of the operating system platform. You can use it at IBM, UNIX, LINUX, DOS, and any other operating system. Learn it well, since it may come handy.

Oracle Buffer

Remember that always your last SQL statement is in the Oracle buffer.

L command

Type the letter "L" to list the last entered SQL statement.

SQL> l

The asterisk next to the line indicates the current line position.

 

RUN commands

Run the SQL statement in the buffer, using the "run" command.

SQL> run

-or- the letter "r."

SQL> r

-or- "/"

SQL> /

 

Semi-column in Oracle

You enter a semi-column at the end of each SQL statement. It means to terminate a SQL statement and execute the statement in the Oracle buffer.

Write a format free SQL statement to query the dept table; and enter each word in a line.

SQL> SELECT

deptno,

dname,

loc

FROM

dept;

End the SQL statement with ; to terminate and execute the statement.

 

List the statement from the Oracle buffer.

SQL> L

 

A dot (.) in a SQL statement

You enter a dot at the end of your SQL statement to tell Oracle that it is the end of my SQL statement and do not execute the statement. You may do that if for some reason you want to change your SQL statement in the Oracle buffer.

Write a format free query and use a dot at the end of the sql statement to end the statement, but not run the statement.

The sql statement will be in the Oracle buffer as long as it was not replaced or the user session was not terminated.

SQL> SELECT

deptno

,

dname

,

loc

FROM

dept

.

 

Then run the statement.

SQL> r

 

c or CHANGE command

Use the command line editor to add location a column heading to the "loc" column. First, you should change the current line position to number 6 and then use the "C" or change command to add column heading.

SQL> L6

SQL> c/loc/loc "location"/

 

Listing lines within a range

List the sql statement lines from 2 to 7.

SQL> L 2 7

 

List the entire query.

SQL> L

Deleting lines within a range

Delete the sql statement lines from 4 to 5.

SQL> del 4 5

 

Run the query. Notice that the department name column was deleted.

SQL> /

 

Positioning a SQL line

Position line number 3 to the current line.

SQL> L3

 

I or INSERT command

Use the "i" or insert command to insert a line.

Then use the dot to terminate the insert mode.

SQL> i

4i dname,

 

List the query. The line was added.

SQL> L

 

Then run the statement.

SQL> /

 

SAVE command

Save the sql statement as "d-e-p-t" file in the "oracle" directory. Notice that the default extension is s-q-l.

SQL> save dept

 

GET command

Use the "get" command to replace the sql file into the buffer.

SQL> get dept

 

EXIT command

Exit sqlplus to see where the file was stored?

SQL> exit

 

List the file names in the oracle directory.

DOS> dir

 

TYPE command

Use the "type" command to list the query.

DOS> type dept.sql

 

Login to "sqlplus" as "oracle/learning"

DOS> sqlplus oracle/learning

List the buffer. Notice that there is no query in the buffer. Get the file and then run it.

 

SQL> l

 

SQL> get dept

/

@ command

or use the "@" sign command to run the file.

SQL> @c:

START command

Or use the "start" command to run the file.

SQL> start dept

 

These are different ways you can run the SQL script file.

 

Questions

Q: What does the LIST or L command line editor?

Q: What does the INSERT or I command line editor?

Q: What does the DEL or D command line editor?

Q: How do you change a string in the Oracle Buffer?

Q: How do you save the SQL script in the Oracle Buffer?

Q: How do you open the SQL Script into the Oracle Buffer?

     Reviews and Templates for FrontPage
     

Copyright everythingOracle.bizhat.com 2006 All Rights Reserved.