Issues with Spool file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Issues with Spool file

Hybrid View

  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Exclamation 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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    "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
    this space intentionally left blank

  3. #3
    Join Date
    Apr 2014
    Posts
    2

    Exclamation

    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width