• Two ways to run sql commands:

 
1. Interactive mode: type commands directly at the SQL prompt
2. Batch: create a set of commands in a file and run it in the SQL⁺

• At the SQL⁺ prompt, you can type in any SQL query. The query will be sent to the Oracle server for executing and the results will be given back to your terminal interactively.

For instance to create a table 'STUDENT', type at prompt:

SQL> CREATE  TABLE  Student
  2  ( Name   VARCHAR2(30),
  3    StudentNumber  NUMBER(4)  NOT  NULL,
  4    Class  NUMBER(4),
  5    Major  VARCHAR2(4),
  6    Primary key (StudentNumber)
  7  );

Table created.
SQL-Command

Note: It is suggested that we use VARCHAR2 instead of VARCHAR

• You can use EDIT to invoke a text editor on the contents of the buffer (the last SQL command), then make your changes, save and exit the editor. You then return to the SQL and reexecute your command by enter slash (/) at the prompt, SQL>.
• Or you can use the internal SQL command line editor to make changes and then reexecute. The internal SQL command line editor only works with your last SQL command. These commands are:

 

 

Command	Abbr.	Purpose
APPEND text	A text	adds text at the end of a line
CHANGE /old/new	C /old/new	changes old to new in a line
CHANGE /text	C /text	deletes text from a line
CLEAR BUFFER	CL BUFFER	deletes all lines
DEL	(none)	deletes the current line
DEL n	(none)	deletes line n
DEL *	(none)	deletes the current line
DEL LAST	none)	deletes the last line
DEL m n	(none)	deletes lines from m to n
INPUT	I	adds one or more lines
INPUT text	I text	adds a line consisting of text
LIST	L	lists all lines in the SQL buffer
LIST n	L n or n	lists line n
LIST *	L *	lists the current line
LIST LAST	L LAST	lists the last line
LIST m n	L m n	lists a range of lines (m to n)

After you change the buffer, you can reexecute it by enter slash (/).

• Use your favorite editor to create a file with extension '.sql' containing your SQL command(s).

 

 

For instance, use 'emacs' to create a file, 'exCreate.sql' containing the following:

SET TERMOUT ON
PROMPT Create Table Student
SET TERMOUT OFF
SET FEEDBACK ON

-- Drop the old table before create. -- In line comment
DROP TABLE Student;

REMARK Create a table Student
CREATE TABLE Student
( Name  VARCHAR2(30),
  StudentNumber NUMBER(4) NOT NULL,
  Class NUMBER(4),
  Major VARCHAR2(4),
  Primary key (StudentNumber)
);

/* Insert data into the Student table */
INSERT INTO Student VALUES ('Smith', 17, 1, 'COSC');
INSERT INTO Student VALUES ('Brown', 8, 2, 'COSC');
INSERT INTO Student VALUES ('Senior Answer1', 421, 5, 'COSC');
INSERT INTO Student VALUES ('Dick Davidson', 110, 1, 'COSC');
INSERT INTO Student VALUES ('Babara Benson', 28, 2, 'ECSE');
INSERT INTO Student VALUES ('Charlie Cooper', 21, 2, 'DCSC');
INSERT INTO Student VALUES ('Katherine Ashly', 138, 1,'COSC');
INSERT INTO Student VALUES ('Benjamin Bayer', 430, 5, 'EPW');
INSERT INTO Student VALUES ('Senior Crew', 492, 5, 'COSC');
INSERT INTO Student VALUES ('John', 362, 3, 'CIVI');
INSERT INTO Student VALUES ('Proc', 123, 1, NULL);

COMMIT;

• From the batch file, you can run it by typing at prompt:
• @[filename] or
• start [filename]
Note that the file extension '.sql' can be omitted.

SQL>   @exCreate
Create Table Student
SQL>
or
SQL>   START exCreate
Create Table Student
SQL>

Exit to : Oracle Hints and Tips