OK, ready to go? Start SQL*Plus and log on to the database using the SYSTEM/MANAGER ID. You should see the SQL> prompt. I will walk you through the script lines, pointing out various important processing steps. The script commands and system messages will be shown in bold . At the end, I'll give you the complete script and the output listing showing what the system generated, along with comments to help you follow the flow. For those of you who are wondering whether there's an easier way, the answer is yes. In practice, all of us have a medicine bag of various tools, tricks, and scripts that we create in a text editor and save as a .sql type. Here's an example of saving a script in Notepad. Notice that the file name is in quotes, and it is saved as type "All Files":
These files can then be run from the SQL> prompt: @A:\CREATE_TABLES.SQL . For your own learning, however, I advise you to type the commands manually, get the normal syntax errors, struggle with cleaning them up, and in general become very good at creating SQL*Plus commands. (This exercise will be frustrating at times, but believe me, it will be worth it.) Getting StartedThe first step is to tell Oracle to create an output listing of all commands and system messages. We do this by using two SQL commands SPOOL and SET ECHO :
So here are the first couple of lines to type in: SQL> SET ECHO ON; SQL>SPOOL C:\SPOOL_FILES\WAIVER.LST; Creating the TablespaceNow we create the tablespace, using the ALTER , DROP , CREATE commands we just talked about. Type in the following: ALTER TABLESPACE TABLESPACE_FOR_WAIVERS OFFLINE NORMAL ; DROP TABLESPACE TABLESPACE_FOR_WAIVERS INCLUDING CONTENTS ; CREATE TABLESPACE TABLE_SPACE_FOR_WAIVERS DATAFILE 'D:\TABLESPACES\DATA\TABLESPACE_FOR_WAIVERS' SIZE 1000M OFFLUNE PERMANENT ; "Whoa," you say? Just got a nasty error: SQL> ALTER TABLESPACE TABLESPACE_FOR_WAIVERS 2 OFFLUNE; OFFLUNE * ERROR at line 2: ORA-02142: missing or invalid ALTER TABLESPACE option You've discovered by now (unless you're a perfect typist) that you cannot backspace when working from the SQL command line. Do you have to retype the entire command? No. SQL*Plus comes with a default editor (you can change it, but we're not going to discuss that right now). If you are running straight SQL commands, you can type them in on the command line and then easily edit them by typing EDIT at the SQL> prompt. This command takes you right to the editor, and the editor (in this example Notepad) shows you your latest SQL command:
Now you can correct the error. In this example OFFLUNE should be OFFLINE . After you correct the error, select File Save , and at the SQL> prompt, just type "RUN", and the ALTER command will fire off: SQL>RUN SQL> ALTER TABLESPACE TABLESPACE_FOR_WAIVERS 2 OFFLINE; ALTER TABLESPACE TABLESPACE_FOR_WAIVERS * ERROR at line 1: ORA-00959: tablespace 'TABLESPACE_FOR_WAIVERS' does not exist
Now type in the other two commands DROP and CREATE and you should see these messages: SQL> DROP TABLESPACE TABLESPACE_FOR_WAIVERS 2 INCLUDING CONTENTS; Tablespace does not exist . +++++++++++++++++++++++++++++++++++++++++ SQL> RUN 1 CREATE TABLESPACE TABLESPACE_FOR_WAIVERS 2 DATAFILE 'D:\TABLESPACES\DATA\TABLESPACE_FOR_WAIVERS' 3 SIZE 1000M 3 ONLINE 4 PERMANENT Tablespace created .
Note When you drop a tablespace, unfortunately the data file is not deleted. The tablespace name is gone, but if you try to re-create it using the same data file, you will get the following error: * ERROR at line 1: ORA-01119: error in creating database file 'TABLESPACE_FOR_WAIVERS' ORA-07399: sfccf: file exists. This means that the file named TABLESPACE_FOR_WAIVERS already exists, so you cannot use it to create another tablespace. However, you can use something like TABLESPACE2_FOR_WAIVERS : SQL>RUN CREATE TABLESPACE TABLESPACE_FOR_WAIVERS DATAFILE 'D:\TABLESPACES\DATA\TABLESPACE2_FOR_WAIVERS' SIZE 1M ONLINE PERMANENT Tablespace created . Or you can delete the file (that is, go to the D:\TABLESPACES\DATA folder and delete the file called TABLESPACE_FOR_WAIVERS ) and then reuse the name. Note The editor is fine for straight SQL commands, but not for PL/SQL commands such as loops and procedures. To run PL/SQL, you have to run a script. How do you run a script from the command line? Easyjust type in "@< path > & script name.SQL". For example, to run the script New_Names that is on your C: drive in your SQL_SCRIPTS folder, type in "@C:\SQL_SCRIPTS\NEW_NAMES.SQL" at the SQL> prompt, and SQL will then execute the script. We'll talk about this more in the context of other programming examples throughout the book, but for those of you who are curious , go ahead and write a couple of the commands we're using here, save them as a .sql file, and run them from the SQL command line. Creating TablesNow we're ready to create our tables. Remember that because we have constraints in the form of referential integrity built in, the tables must be created in a certain sequence. The error in the preceding section was included to try to make this clear. First we need the TEST_TYPE_DESC table. To drop and create this table, type in the following commands. Once SQL senses the semicolon, it will run the command: DROP TABLE TEST_TYPE_DESC CASCADE CONSTRAINTS; CREATE TABLE TEST_TYPE_DESC ( TYPE_ID NUMBER(6) PRIMARY KEY, TYPE_DESC VARCHAR2(30) NOT NULL ) TABLESPACE TABLESPACE_FOR_WAIVERS ; Note CASCADE CONSTRAINTS means that Oracle will "drop" any referential integrity constraints associated with this table. So be very careful with this command. It is normally used when tables are first being created just in case they already exist, most likely from earlier testing. The DROP...CASCADE construction is also often used when we're working with a test database, where the tables are constantly changing. Next we need the AUTHORS table: DROP TABLE AUTHORS CASCADE CONSTRAINTS; CREATE TABLE AUTHORS ( AUTHOR_ID NUMBER(9) PRIMARY KEY, AUTHOR VARCHAR2(60) DEFAULT 'WAIVER ADMINISTRATION' ) TABLESPACE TABLESPACE_FOR_WAIVERS ; Then we create the TEST_ID table: DROP TABLE TEST_ID CASCADE CONSTRAINTS; CREATE TABLE TEST_ID ( TEST_ID NUMBER(6) PRIMARY KEY, TEST_NAME VARCHAR2(40) NOT NULL, DATE_CREATED DATE NOT NULL, AUTHOR NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID), COMMENTS VARCHAR2(30), TYPE_ID NUMBER(6) REFERENCES TEST_TYPE_DESC(TYPE_ID), PASSING_GRADE NUMBER(2) NOT NULL, TIME_LIMIT NUMBER(4,2) NOT NULL ) TABLESPACE TABLESPACE_FOR_WAIVERS ; Notice that if you try to create a table that references a table that has not been built yet, you get this error: SQL> CREATE TABLE TEST_QUESTIONS_LINK ( 2 LINK_TEST_ID NUMBER(6) REFERENCES TEST_ID(TEST_ID), 3 LINK_QUESTION_ID NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID), 4 PRIMARY KEY (LINK_TEST_ID, LINK_QUESTION_ID) 5 ); LINK_QUESTION_ID NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID), * ERROR at line 3: ORA-00904: invalid column name In other words, the QUESTIONS table does not exist yet, and Oracle is already enforcing referential integrity! Now work on the questions by starting with the QUESTIONS_TYPE_DESC table: DROP TABLE QUESTIONS_TYPE_DESC CASCADE CONSTRAINTS; CREATE TABLE QUESTIONS_TYPE_DESC ( QUESTION_TYPE NUMBER(6) PRIMARY KEY, QUESTION_TYPE_DESC VARCHAR2(40) NOT NULL ) TABLESPACE TABLESPACE_FOR_WAIVERS ; And next create the QUESTIONS table itself: DROP TABLE QUESTIONS CASCADE CONSTRAINTS; CREATE TABLE QUESTIONS ( QUESTION_ID NUMBER(6) PRIMARY KEY, QUESTION VARCHAR2(500) NOT NULL, CORRECT_ANSWER VARCHAR2(2) NOT NULL, QUESTION_TYPE NUMBER(6) REFERENCES QUESTIONS_TYPE_DESC(QUESTION_TYPE) ) TABLESPACE TABLESPACE_FOR_WAIVERS ; Next link the QUESTIONS and TEST_ID tables by creating the linking table TEST_QUESTIONS_LINK: DROP TABLE TEST_QUESTIONS_LINK CASCADE CONSTRAINTS; CREATE TABLE TEST_QUESTIONS_LINK ( LINK_TEST_ID NUMBER(6) REFERENCES TEST_ID(TEST_ID), LINK_QUESTION_ID NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID), WEIGHT NUMBER(2,1) DEFAULT 1, PRIMARY KEY (LINK_TEST_ID, LINK_QUESTION_ID) ) TABLESPACE TABLESPACE_FOR_WAIVERS ; Now create the last core table, ANSWERS: DROP TABLE ANSWERS CASCADE CONSTRAINTS; CREATE TABLE ANSWERS ( ANSWER_QUESTION_ID NUMBER(6) PRIMARY KEY REFERENCES QUESTIONS(QUESTION_ID), ANSWER_ID VARCHAR2(2) NOT NULL, ANSWER VARCHAR2(30) NOT NULL ) TABLESPACE TABLESPACE_FOR_WAIVERS ; Once these base tables have been defined, we can move on to the student and history tables. First create the STUDENTS table: DROP TABLE STUDENTS CASCADE CONSTRAINTS; CREATE TABLE STUDENTS ( STUDENT_ID NUMBER(9) PRIMARY KEY, F_NAME ARCHAR2(15) NOT NULL, M_I VARCHAR2(1), L_NAME VARCHAR2(20) NOT NULL, SSNUM NUMBER(9) NOT NULL UNIQUE, B_DATE DATE, SEX VARCHAR2(1) CHECK (SEX IN ('M', 'F', 'm', 'f')), STREET1 VARCHAR2(15), STREET2 VARCHAR2(15), TOWN VARCHAR2(20), STATE VARCHAR2(2), COUNTRY VARCHAR2(15) DEFAULT 'UNITED STATES', ZIP NUMBER(5) NOT NULL, STUDENT_LEVEL NUMBER(6) NOT NULL, DATE_CREATED DATE, CREATED_BY VARCHAR2(20), DEPARTMENT_MAJOR VARCHAR2(10) NOT NULL, EMAIL VARCHAR2(60) ) TABLESPACE TABLESPACE_FOR_WAIVERS ; Only two more to go! First create the TEST_HISTORY table: DROP TABLE TEST_HISTORY CASCADE CONSTRAINTS; CREATE TABLE TEST_HISTORY ( TEST_ID NUMBER(6) REFERENCES TEST_ID(TEST_ID), STUDENT_ID NUMBER(9) REFERENCES STUDENTS(STUDENT_ID), SCORE NUMBER(3) NOT NULL, DATE_TAKEN DATE NOT NULL, START_TIME VARCHAR2(8) NOT NULL, END_TIME VARCHAR2(8) NOT NULL, LOCATION VARCHAR2(15) NOT NULL, PRIMARY KEY (TEST_ID, STUDENT_ID) ) TABLESPACE TABLESPACE_FOR_WAIVERS ; And finally, the last table is STUDENT_ANSWERS_HISTORY: DROP TABLE STUDENT_ANSWER_HISTORY CASCADE CONSTRAINTS; CREATE TABLE STUDENT_ANSWER_HISTORY ( STUDENT_ID NUMBER(9) REFERENCES STUDENTS(STUDENT_ID), TEST_ID NUMBER(6) REFERENCES TEST_ID(TEST_ID), QUESTION_ID NUMBER(6) REFERENCES QUESTIONS, STUDENT_ANSWER VARCHAR2(2) NOT NULL ) TABLESPACE TABLESPACE_FOR_WAIVERS ; |