The Output Spool File


Here's the output spool file with our notes from creation of the tables. Your spool file should look similar:

 SQL>SET ECHO ON; graphics/1_icon.gif SQL>SPOOL C:\SPOOL_FILES\WAIVER.LST SQL> SQL> ALTER TABLESPACE TABLESPACE_FOR_WAIVERS graphics/2_icon.gif 2        OFFLINE NORMAL; ALTER TABLESPACE TABLESPACE_FOR_WAIVERS  *  ERROR at line 1:   ORA-00959: tablespace 'TABLESPACE_FOR_WAIVERS' does not exist  graphics/3_icon.gif SQL> SQL> DROP TABLESPACE TABLESPACE_FOR_WAIVERS   2         INCLUDING CONTENTS; DROP TABLESPACE TABLESPACE_FOR_WAIVERS *  ERROR at line 1:   ORA-00959: tablespace 'TABLESPACE_FOR_WAIVERS' does not exist  SQL> SQL> SQL> CREATE TABLESPACE TABLESPACE_FOR_WAIVERS   2 DATAFILE 'TABLESPACE_FOR_WAIVERS' SIZE 500M   3 ONLINE   4 PERMANENT;  Tablespace created  . graphics/4_icon.gif SQL> SQL> SQL> /*                                       */ SQL> DROP TABLE TEST_TYPE_DESC CASCADE CONSTRAINTS;  DROP TABLE TEST_TYPE_DESC CASCADE CONSTRAINTS            *  ERROR at line 1:   ORA-00942: table or view does not exist  graphics/5_icon.gif SQL> SQL> CREATE TABLE TEST_TYPE_DESC (   2   TYPE_ID      NUMBER(6) PRIMARY KEY,   3   TYPE_DESC     VARCHAR2(30) NOT NULL   4                             )   5   TABLESPACE TABLESPACE_FOR_WAIVERS   6                                ;  Table created  . graphics/6_icon.gif 

(1) Start by turning spooling and echo on.

(2) First create a special tablespace for the data files.

(3) Since the tablespace doesn't exist, this message is OK.

(4) This is the message you want to see!

(5) Since the file doesn't exist, this message is OK.

(6) This is the message you want to see!

Now continue with the rest of the tables.

 SQL> /*                                     */ SQL> SQL>  DROP TABLE  TEST_ID   CASCADE CONSTRAINTS;  DROP TABLE  TEST_ID   CASCADE CONSTRAINTS             *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> SQL>  CREATE TABLE TEST_ID (   2    TEST_ID      NUMBER(6) PRIMARY KEY,   3    TEST_NAME    VARCHAR2(40) NOT NULL,   4    DATE_CREATED DATE NOT NULL,   5    AUTHOR       VARCHAR(30) DEFAULT 'WAIVER ADMINISTRATION',   6    COMMENTS     VARCHAR2(30),   7    TYPE_ID      NUMBER(6) REFERENCES TEST_TYPE_DESC(TYPE_ID),   8    PASSING_GRADE NUMBER(2) NOT NULL,   9    TIME_LIMIT    NUMBER(4,2) NOT NULL  10                          )  11    TABLESPACE TABLESPACE_FOR_WAIVERS  12                                   ;  Table created  . SQL> SQL> /*                                 */ SQL>   DROP TABLE QUESTIONS_TYPE_DESC CASCADE CONSTRAINTS;   DROP TABLE QUESTIONS_TYPE_DESC CASCADE CONSTRAINTS        *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> SQL>   CREATE TABLE QUESTIONS QUESTIONS_TYPE_DESC (   2    QUESTION_TYPE      NUMBER(6) PRIMARY KEY,   3    QUESTION_TYPE_DESC VARCHAR2(40) NOT NULL   4                                )   5    TABLESPACE TABLESPACE_FOR_WAIVERS   6                                ;   CREATE TABLE QUESTIONS  QUESTIONS_TYPE_DESC   (          *  ERROR at line 1:   ORA-00922: missing or invalid option  

Note

Here's something you may come across if you use MS Word, Notepad, or another text editor. Every so often a command simply doesn't work, and you cannot find the problem.

The preceding code is an example: Even though the command is perfectly constructed , it generates an error. In these cases, all you can do is retype the command or copy the script into another text editor. Strange but true.

In this case, then, we try retyping the commands and it works! The original error was probably caused by simple, poor typing that resulted in an unprintable character or escape sequence being entered.


[View full width]
 SQL>  DROP TABLE   QUESTIONS_TYPE_DESC   CASCADE CONSTRAINTS;   DROP TABLE   QUESTIONS_TYPE_DESC   CASCADE CONSTRAINTS              *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> SQL>       CREATE TABLE QUESTIONS_TYPE_DESC    (   2          QUESTION_TYPE         NUMBER(6) PRIMARY KEY,   3          QUESTIONS_TYPE_DESC   VARCHAR2(50) NOT NULL   4                                      )   5          TABLESPACE TABLESPACE_FOR_WAIVERS   6                                      ;  Table created  . SQL> /*                                      */ SQL>   DROP TABLE  QUESTIONS   CASCADE CONSTRAINTS;   DROP TABLE  QUESTIONS    CASCADE CONSTRAINTS              *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> SQL>   create table QUESTIONS     (   2        QUESTION_ID    NUMBER(6) PRIMARY KEY,   3        QUESTION       VARCHAR2(500) NOT NULL,   4        CORRECT_ANSWER VARCHAR2(2) NOT NULL,   5        QUESTION_TYPE  NUMBER(6) REFERENCES QUESTIONS_TYPE_DESC(QUESTION_TYPE),   6        DATE_CREATED   DATE,   7        AUTHOR_ID      NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID)   8                               )   9        TABLESPACE TABLESPACE_FOR_WAIVERS  10                                 ;    AUTHOR_ID         NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID)                                      *  ERROR at line 7:   ORA-00942: table or view does not exist  SQL> /* Following is a retype of the CREATE TABLE statement because what's shown above graphics/ccc.gif keeps producing an error */ SQL> SQL>  CREATE TABLE QUESTIONS         (   2       QUESTION_ID     NUMBER(6) PRIMARY KEY,   3       QUESTION        VARCHAR2(500) NOT NULL,   4       CORRECT_ANSWER  VARCHAR2(2) NOT NULL,   5       QUESTION_TYPE   NUMBER(6) REFERENCES QUESTIONS_TYPE_DESC(QUESTION_TYPE),   6       DATE_CREATED    DATE,   7       AUTHOR_ID       NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID)   8                                 )   9       TABLESPACE    TABLESPACE_FOR_WAIVERS  10                                ;    AUTHOR_ID     NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID)                                     *  ERROR at line 7:   ORA-00942: table or view does not exist  SQL> SQL> SQL> /*                                      */ SQL>  DROP TABLE AUTHORS   CASCADE CONSTRAINTS;   DROP TABLE AUTHORS  CASCADE CONSTRAINTS            *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> SQL>  CREATE TABLE AUTHORS  (   2                      AUTHOR_ID  NUMBER(9) PRIMARY KEY,   3                      AUTHOR     VARCHAR2(75) DEFAULT 'WAIVER ADMINISTRATION'   4                         )   5                       TABLESPACE TABLESPACE_FOR_WAIVERS   6                          ;  Table created  . SQL> SQL> /*                                     */ SQL>  DROP TABLE QUESTIONS    CASCADE CONSTRAINTS;   DROP TABLE  QUESTIONS  CASCADE CONSTRAINTS             *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> SQL>   create table  QUESTIONS     (   2        QUESTION_ID     NUMBER(6) PRIMARY KEY,   3        QUESTION        VARCHAR2(500) NOT NULL,   4        CORRECT_ANSWER  VARCHAR2(2) NOT NULL,   5        QUESTION_TYPE NUMBER(6) REFERENCES QUESTIONS_TYPE_DESC(QUESTION_TYPE),   6        DATE_CREATED    DATE,   7        AUTHOR_ID       NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID)   8                               )   9        TABLESPACE TABLESPACE_FOR_WAIVERS  10                                 ;  Table created  . SQL> SQL> SQL> /*                                       */ SQL>   DROP TABLE  TEST_QUESTIONS_LINK CASCADE CONSTRAINTS;   DROP TABLE  TEST_QUESTIONS_LINK  CASCADE CONSTRAINTS              *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> 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                                   )   6       TABLESPACE TABLESPACE_FOR_WAIVERS   7                                   ;  Table created  . SQL> SQL> SQL> /*                                     */ SQL>   DROP TABLE ANSWERS CASCADE CONSTRAINTS;   DROP TABLE ANSWERS CASCADE CONSTRAINTS              *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> SQL>   CREATE TABLE ANSWERS   (   2        ANSWER_QUESTION_ID  NUMBER(6) PRIMARY KEY REFERENCES QUESTIONS(QUESTION_ID),   3        ANSWER_ID          VARCHAR2(2) NOT NULL,   4        ANSWER             VARCHAR2(30) NOT NULL   5                           )   6        TABLESPACE TABLESPACE_FOR_WAIVERS   7                           ;  Table created  . SQL> SQL> /*                                   */ SQL>   DROP TABLE STUDENTS    CASCADE CONSTRAINTS;   DROP TABLE STUDENTS    CASCADE CONSTRAINTS             *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> SQL>   CREATE TABLE       STUDENTS     (   2        STUDENT_ID      NUMBER(9) PRIMARY KEY,   3        F_NAME          VARCHAR2(15) NOT NULL,   4        M_I             VARCHAR2(1),   5        L_NAME          VARCHAR2(20) NOT NULL,   6        SSNUM           NUMBER(9) NOT NULL UNIQUE,   7        B_DATE          DATE,   8       SEX              VARCHAR2(1) CHECK (SEX IN ('M', 'F',                              'm', 'f')),   9       STREET1          VARCHAR2(15),  10       STREET2          VARCHAR2(15),  11       TOWN             VARCHAR2(20),  12       STATE            VARCHAR2(2),  13       COUNTRY          VARCHAR2(15) DEFAULT 'UNITED STATES',  14       ZIP              NUMBER(5) NOT NULL,  15       STUDENT_LEVEL    NUMBER(6) NOT NULL,  16       DATE_CREATED     DATE,  17       CREATED_BY       VARCHAR2(20),  18       DEPARTMENT_MAJOR VARCHAR2(10) NOT NULL,  19       EMAIL            VARCHAR2(60)  20                                )  21       TABLESPACE TABLESPACE_FOR_WAIVERS  22                                ;  Table created  . SQL> SQL> SQL> /*                                                  */ SQL>   DROP TABLE TEST_HISTORY CASCADE CONSTRAINTS;  DROP TABLE TEST_HISTORY CASCADE CONSTRAINTS            *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> SQL>  CREATE TABLE   TEST_HISTORY      (   2       TEST_ID         NUMBER(6) REFERENCES TEST_ID(TEST_ID),   3         STUDENT_ID  NUMBER(9) REFERENCES STUDENTS(STUDENT_ID),   4       SCORE            NUMBER(3) NOT NULL,   5       DATE_TAKEN       DATE NOT NULL,   6       START_TIME       VARCHAR2(8) NOT NULL,   7       END_TIME         VARCHAR2(8) NOT NULL,   8       LOCATION         ARCHAR2(15) NOT NULL,   9       PRIMARY KEY (TEST_ID, STUDENT_ID)  10                                        )  11       TABLESPACE TABLESPACE_FOR_WAIVERS  12                                        ;  Table created  . SQL> SQL> /*                                                  */ SQL>  DROP TABLE  STUDENT_ANSWER_HISTORY CASCADE CONSTRAINTS;  DROP TABLE  STUDENT_ANSWER_HISTORY CASCADE CONSTRAINTS             *  ERROR at line 1:   ORA-00942: table or view does not exist  SQL> SQL>   CREATE TABLE  STUDENT_ANSWER_HISTORY  (   2                 STUDENT_ID      NUMBER(9) REFERENCES STUDENTS(STUDENT_ID),   3                 TEST_ID         NUMBER(6) REFERENCES TEST_ID(TEST_ID),   4                 QUESTION_ID     NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID),   5                 STUDENT_ANSWER   VARCHAR2(2)      NOT NULL   6                                           )   7                 TABLESPACE TABLESPACE_FOR_WAIVERS   8                                           ;  Table created  . SQL> SQL> SPOOL OFF; graphics/7_icon.gif 

(7) Finish by turning spooling off. If you don't do this, your spool file will be empty until you either exit SQL or turn spooling off.



Guerrilla Oracle
Guerrilla Oracle: The Succinct Windows Perspective
ISBN: 0201750775
EAN: 2147483647
Year: 2003
Pages: 84

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net