iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 06

"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?