|
"A lie gets
halfway around the world before the truth has a chance to get
its pants on." - Sir Winston Churchill (1874-1965) |
Read
first then play the video:
SQL-VIDEO -SQL
and PL/SQL Command editors
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
"iself".
DOS> mkdir iself
Change the directory to the
iself directory.
DOS> cd iself
List directory. Notice that
there is nothing in it.
DOS> dir
Login to "sqlplus" as
"iself" password "schooling".
SQL> sqlplus iself/schooling
SQLPlus default directory
From now on the "iself"
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 "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 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 "iself" 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
iself directory.
DOS> dir
TYPE command
Use the "type"
command to list the query.
DOS> type dept.sql
Login to "sqlplus"
as "iself/schooling"
DOS> sqlplus iself/schooling
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
Use the
"start" command to run the file.
SQL> start dept
These are different ways
you can run the SQL script file.
|
"I do not feel
obliged to believe that the same God who has endowed us with
sense, reason, and intellect has intended us to forgo their
use." - Galileo Galilei |
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?
|