Issues with Spool file

Hi all,

I am having problem doing the spool files for my current project. Everytime I try to spool, I get the following error:

COLUMN parts_TP_prt_unit_price FORMAT A32;
SQL> COLUMN part_num FORMAT A20;
SQL> COLUMN parts_TP FORMAT A15;
SQL> SELECT parts_TP_prt_unit_price, part_num, parts_TP FROM table_creatorfinal33114Part1.sql;
SELECT parts_TP_prt_unit_price, part_num, parts_TP FROM table_creatorfinal33114Part1.sql
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> WHERE parts_TP_prt_unit_price NOT LIKE 'BIN%';
SP2-0734: unknown command beginning "WHERE part..." - rest of line ignored.
SQL>

I am wondering if somebody could help understand on how to spool the following codes:

CREATE TABLE customers_TP
(customer_id NUMBER (6),
customer_last_name VARCHAR2 (30)
CONSTRAINT cust_TP_cust_lname_nn NOT NULL,
customer_first_name VARCHAR2 (25)
CONSTRAINT cust_TP_cust_fname_nn NOT NULL,
customer_street VARCHAR2 (25)
CONSTRAINT cust_TP_cust_street_nn NOT NULL,
customer_city VARCHAR2 (25)
CONSTRAINT cust_TP_cust_city_nn NOT NULL,
customer_state CHAR (2)
CONSTRAINT cust_TP_cust_state_nn NOT NULL,
customer_zip_code CHAR (5)
CONSTRAINT cust_TP_cust_zip_nn NOT NULL,
customer_phone_num CHAR (10)
CONSTRAINT cust_TP_cust_phone_nn NOT NULL,
customer_alt_phone CHAR (10),
CONSTRAINT cust_TP_cust_id_pk PRIMARY KEY (customer_id));

CREATE TABLE technicians_TP
(technician_num NUMBER (2),
technician_last_name VARCHAR2 (30)
CONSTRAINT tech_TP_tech_lname_nn NOT NULL,
technician_first_name VARCHAR2 (25)
CONSTRAINT tech_TP_tech_fname_nn NOT NULL,
CONSTRAINT tech_TP_tech_num_pk PRIMARY KEY (technician_num));

CREATE TABLE ownerships_TP
(customer_id NUMBER (6),
car_vin CHAR (17)
CONSTRAINT owner_TP_car_vin_nn NOT NULL,
CONSTRAINT owner_TP_cust_id_pk PRIMARY KEY (customer_id, car_vin),
CONSTRAINT owner_TP_cust_num_fk FOREIGN KEY (customer_id)
REFERENCES customers_TP (customer_id),
CONSTRAINT ownerships_TP_car_vin_fk FOREIGN KEY (car_vin)
REFERENCES vehicles_TP (car_vin));

CREATE TABLE invoices_TP
(invoice_num NUMBER (6),
customer_id NUMBER (6)
CONSTRAINT inv_TP_cust_nn NOT NULL,
car_vin CHAR (17)
CONSTRAINT inv_TP_car_vin_in_nn NOT NULL,
invoice_date_in char(8)
CONSTRAINT inv_TP_inv_date_out_nn NOT NULL,
invoice_date_out char(8)
CONSTRAINT inv_TP_inv_mile_in_nn NOT NULL,
invoice_inv_sls_tax NUMBER (5,2)
CONSTRAINT inv_TP_inv_sls_tax_nn NOT NULL,
technician_num NUMBER (2)
CONSTRAINT inv_TP_tech_num_nn NOT NULL,
service_advisor VARCHAR2 (10)
CONSTRAINT inv_TP_serv_nn NOT NULL,
CONSTRAINT inv_TP_inv_num_pk PRIMARY KEY (invoice_num),
CONSTRAINT inv_TP_car_vin_fk FOREIGN KEY (car_vin)
REFERENCES vehicles_TP (car_vin),
CONSTRAINT inv_TP_inv_car_vin_fk FOREIGN KEY (customer_id)
REFERENCES customers_TP (customer_id),
CONSTRAINT inv_TP_tech_num_fk FOREIGN KEY (technician_num)
REFERENCES technicians_TP (technician_num));

CREATE TABLE parts_sls_TP
(invoice_num NUMBER (6),
part_num NUMBER (8)
CONSTRAINT prt_num_TP_nn NOT NULL,
part_unit_price NUMBER (6,2)
CONSTRAINT prt_unit_price_nn NOT NULL,
part_qty_sold CHAR (8)
CONSTRAINT prt_qty_sold_TP_nn NOT NULL,
CONSTRAINT prt_sls_TP_inv_num_pk PRIMARY KEY (invoice_num, part_num),
CONSTRAINT prt_sls_TP_inv_num_fk FOREIGN KEY (invoice_num)
REFERENCES invoices_TP (invoice_num),
CONSTRAINT prt_sls_TP_part_num_fk FOREIGN KEY (part_num)
REFERENCES parts_TP (part_num));

/*rem this sequence is used to increase with every new customer*/
CREATE SEQUENCE cust_id_seq
INCREMENT BY 1
START WITH 367888
NOCYCLE;

/*this sequence is used to increase with every new invoice*/
CREATE SEQUENCE inv_num_seq
INCREMENT BY 1
START WITH 160000
NOCYCLE;

The following codes have to be spool with the current commands that our teacher gave us:

To get information about constraints:
COLUMN constraint_name FORMAT A32;
COLUMN column_name FORMAT A20;
COLUMN table_name FORMAT A15;
SELECT constraint_name, column_name,table_name
FROM user_cons_columns
WHERE constraint_name NOT LIKE 'BIN%'
ORDER BY constraint_name;

To get information about sequences:
SELECT sequence_name, increment_by
FROM user_sequences;

Any help would be appreciated :D

"table_creatorfinal33114Part1.sql" is the name of the file containing the create table
statement is is not the name of the table. To execute the code in the file try the following:

[CODE]
SPOOL table_creatorfinal33114Part1.log
SET ECHO ON
SET TIMING ON
@table_creatorfinal33114Part1.sql
SPOOL OFF;
[\CODE]

This is also an example on how to spool the output to a file. The file will be in the default directory.
You may want to run sqlplus from DOS, which means that you should add your environment variables
to the system control panel.

[CODE]
ORACLE_BASE=C:\Oracle
ORACLE_HOME=C:\Oracle\11.2.0.3
LD_LIBRARY_DIR=C:\Oracle\11.2.0.3\lib
ORACLE_SID=orasid
TNS_ADMIN=C:\Oracle\11.2.0.3\network\ADMIN
PATH=C:\Oracle\11.2.0.3\bin;%PATH%
[\CODE]

"CREATE TABLE customers_TP" creates a table in the schema that you are logged in as. If you are connected to the database with sys or system you could also try:

CREATE TABLE otherschema.customers_TP
(customer_id NUMBER (6),
customer_last_name VARCHAR2 (30)
CONSTRAINT cust_TP_cust_lname_nn NOT NULL,
customer_first_name VARCHAR2 (25)
CONSTRAINT cust_TP_cust_fname_nn NOT NULL,
customer_street VARCHAR2 (25)
CONSTRAINT cust_TP_cust_street_nn NOT NULL,
customer_city VARCHAR2 (25)
CONSTRAINT cust_TP_cust_city_nn NOT NULL,
customer_state CHAR (2)
CONSTRAINT cust_TP_cust_state_nn NOT NULL,
customer_zip_code CHAR (5)
CONSTRAINT cust_TP_cust_zip_nn NOT NULL,
customer_phone_num CHAR (10)
CONSTRAINT cust_TP_cust_phone_nn NOT NULL,
customer_alt_phone CHAR (10),
CONSTRAINT cust_TP_cust_id_pk PRIMARY KEY (customer_id));

It will create the table in a schema that is different from the one you are logged in as. Basically tables hold data, and the relationships between tables are defined by foreign keys. For example with an online store you have customers and products. There is an implied many to many relationship between customers and products where one customer can by many products and each product can be bought by many customers. Hence the orders table would have both product and customer as a parent table. There would be a foreign key relationship between the orders table and both the customer and product table.

An index is most often thought of as a presorted list of values stored in certain columns of a table. It is usually stored as a btree index. There is also a hash or bitmap index that is not sorted but quickly allows you to find certain rows by hashing a value and then doing a direct lookup for the data that you want.

You should start reading though this, it will help:

http://docs.oracle.com/cd/E11882_01/...e40540/toc.htm

Thanks gandolf. Sorry to answer a little late. Was able to resolve the spool situation. Now we are at the stage of creating tables, inserting information to the tables so we can begin to do the queries but, I am having an issue when creating the tables. It shows every time I create a table in ownerships_TP and in parts_sold_TP, it shows the following error: (scroll down to each section)

Any way to solve this issue?

Thanks again :)

SQL> CREATE TABLE parts_TP
2 (part_num NUMBER (8),
3 part_unit_price NUMBER (6,2)
4 CONSTRAINT parts_TP_prt_unit_price_nn NOT NULL,
5 part_description VARCHAR2 (40)
6 CONSTRAINT parts_TP_prt_desc_nn NOT NULL,
7 CONSTRAINT parts_TP_prt_num_pk PRIMARY KEY (part_num));

Table created.

SQL>
SQL> CREATE TABLE vehicles_TP
2 (car_vin VARCHAR2 (17),
3 car_year CHAR (8)
4 CONSTRAINT veh_TP_car_year_nn NOT NULL,
5 car_manufacturer VARCHAR2 (32)
6 CONSTRAINT veh_TP_car_man_nn NOT NULL,
7 car_color VARCHAR2 (15)
8 CONSTRAINT veh_TP_car_col_nn NOT NULL,
9 car_engine_type VARCHAR2 (32)
10 CONSTRAINT veh_TP_car_eng_nn NOT NULL,
11 CONSTRAINT veh_TP_car_vin_pk PRIMARY KEY (car_vin));

Table created.

SQL>
SQL> CREATE TABLE customers_TP
2 (customer_id NUMBER (6),
3 customer_last_name VARCHAR2 (15)
4 CONSTRAINT cust_TP_cust_lname_nn NOT NULL,
5 customer_first_name VARCHAR2 (10)
6 CONSTRAINT cust_TP_cust_fname_nn NOT NULL,
7 customer_street VARCHAR2 (18)
8 CONSTRAINT cust_TP_cust_street_nn NOT NULL,
9 customer_city VARCHAR2 (15)
10 CONSTRAINT cust_TP_cust_city_nn NOT NULL,
11 customer_state CHAR (2)
12 CONSTRAINT cust_TP_cust_state_nn NOT NULL,
13 customer_zip_code CHAR (5)
14 CONSTRAINT cust_TP_cust_zip_nn NOT NULL,
15 customer_phone_num CHAR (12)
16 CONSTRAINT cust_TP_cust_phone_nn NOT NULL,
17 customer_alt_phone CHAR (12),
18 customer_license_num VARCHAR2 (8)
19 CONSTRAINT cust_TP_cust_licnum_nn NOT NULL,
20 CONSTRAINT cust_TP_cust_id_pk PRIMARY KEY (customer_id));

Table created.

SQL>
SQL> CREATE TABLE technicians_TP
2 (technician_num NUMBER (2),
3 technician_last_name VARCHAR2 (10)
4 CONSTRAINT tech_TP_tech_lname_nn NOT NULL,
5 technician_first_name VARCHAR2 (8)
6 CONSTRAINT tech_TP_tech_fname_nn NOT NULL,
7 CONSTRAINT tech_TP_tech_num_pk PRIMARY KEY (technician_num));

Table created.

SQL>
SQL> CREATE TABLE ownerships_TP
2 (customer_id NUMBER (6),
3 car_vin VARCHAR2 (17)
4 CONSTRAINT owner_TP_car_vin_nn NOT NULL,
5 CONSTRAINT owner_TP_cust_id_pk PRIMARY KEY (customer_id, car_vin),
6 CONSTRAINT owner_TP_cust_num_fk FOREIGN KEY (customer_id)
7 REFERENCES customers_TP (customer_id),
8 CONSTRAINT ownerships_TP_car_vin_fk FOREIGN KEY (car_vin)
9 REFERENCES vehicles_TP (car_vin));
CONSTRAINT owner_TP_car_vin_nn NOT NULL,
*
ERROR at line 4:
ORA-02264: name already used by an existing constraint


SQL>

SQL> CREATE TABLE invoices_TP
2 (invoice_num NUMBER (6),
3 customer_id NUMBER (6)
4 CONSTRAINT inv_TP_cust_nn NOT NULL,
5 car_vin VARCHAR2 (17)
6 CONSTRAINT inv_TP_car_vin_nn NOT NULL,
7 invoice_date_in DATE
8 CONSTRAINT inv_TP_inv_date_in_nn NOT NULL,
9 invoice_date_out DATE
10 CONSTRAINT inv_TP_inv_date_out_nn NOT NULL,
11 invoice_sales_tax NUMBER (5,2)
12 CONSTRAINT inv_TP_inv_sls_tax_nn NOT NULL,
13 invoice_mileage_in Number (6)
14 CONSTRAINT inv_TP_inv_mile_nn NOT NULL,
15 technician_num NUMBER (2)
16 CONSTRAINT inv_TP_tech_num_nn NOT NULL,
17 service_advisor VARCHAR2 (15)
18 CONSTRAINT inv_TP_serv_nn NOT NULL,
19 CONSTRAINT inv_TP_inv_num_pk PRIMARY KEY (invoice_num),
20 CONSTRAINT inv_TP_car_vin_fk FOREIGN KEY (car_vin)
21 REFERENCES vehicles_TP (car_vin),
22 CONSTRAINT inv_TP_inv_car_vin_fk FOREIGN KEY (customer_id)
23 REFERENCES customers_TP (customer_id),
24 CONSTRAINT inv_TP_tech_num_fk FOREIGN KEY (technician_num)
25 REFERENCES technicians_TP (technician_num));

Table created.

SQL>
SQL> CREATE TABLE parts_sold_TP
2 (invoice_num NUMBER (6),
3 part_num NUMBER (8)
4 CONSTRAINT prt_num_TP_nn NOT NULL,
5 part_unit_price NUMBER (6,2)
6 CONSTRAINT prt_unit_price_nn NOT NULL,
7 part_qty_sold CHAR (3)
8 CONSTRAINT prt_qty_sold_TP_nn NOT NULL,
9 CONSTRAINT prt_sls_TP_inv_num_pk PRIMARY KEY (invoice_num, part_num),
10 CONSTRAINT prt_sls_TP_inv_num_fk FOREIGN KEY (invoice_num)
11 REFERENCES invoices_TP (invoice_num),
12 CONSTRAINT prt_sls_TP_part_num_fk FOREIGN KEY (part_num)
13 REFERENCES parts_TP (part_num));
CONSTRAINT prt_num_TP_nn NOT NULL,
*
ERROR at line 4:
ORA-02264: name already used by an existing constraint


SQL>
SQL> /* rem this sequence is used to increase with every new customer */
SQL> CREATE SEQUENCE cust_id_seq
2 INCREMENT BY 1
3 START WITH 367888
4 NOCYCLE
5 NOCACHE;

Sequence created.

SQL> /* this sequence is used to increase with every new invoice */
SQL> CREATE SEQUENCE invoice_num_seq
2 INCREMENT BY 1
3 START WITH 160000
4 NOCYCLE
5 NOCACHE;

Sequence created.

You are creating a table with a constraint name that
already exists in the schema that you are creating it in.
You can also grep for the constraint name in the file
that you are creating the schema from.

Code:

---

SELECT object_name, object_Type, status
  FROM user_objects
 WHERE object_name=UPPER('owner_TP_car_vin_nn')
 ORDER BY 1;
 
SQL> CREATE TABLE ownerships_TP
2 (customer_id NUMBER (6),
3 car_vin VARCHAR2 (17)
4 CONSTRAINT owner_TP_car_vin_nn NOT NULL,
5 CONSTRAINT owner_TP_cust_id_pk PRIMARY KEY (customer_id, car_vin),
6 CONSTRAINT owner_TP_cust_num_fk FOREIGN KEY (customer_id)
7 REFERENCES customers_TP (customer_id),
8 CONSTRAINT ownerships_TP_car_vin_fk FOREIGN KEY (car_vin)
9 REFERENCES vehicles_TP (car_vin));
CONSTRAINT owner_TP_car_vin_nn NOT NULL,
*
ERROR at line 4:
ORA-02264: name already used by an existing constraint

---