4 Replies Latest reply on Oct 6, 2015 12:30 AM by rp0428

    Sql creating tables

    3043652

      Hi, first time user of sql developer and i cant understand why some only some table were created and some werent. i made sure to follow sql coding rules. but i keep getting error of table or view does not exist. and Error starting at line : 103 in command -

       

      here is my sql script

       

      /* CREATE TABLE STATEMENTS*/

       

       

       

       

      CREATE TABLE SHOP

      (

      SHOPID CHAR(4) NOT NULL,

      ADDRESS VARCHAR2(40),

      CITY VARCHAR2(15),

      STATE VARCHAR2 (3),

      CONTACTNO CHAR(10)

       

       

      CONSTRAINT SHOP_PK PRIMARY KEY (SHOPID)

      );

       

       

       

       

      CREATE TABLE APPLIANCE

         (

      APPLIANCEID CHAR(4) NOT NULL,

      MODELID CHAR (4) NOT NULL,

      APPLIANCENAME VARCHAR2(25),

      MANUFACTUREYEAR NUMBER (4),

      PRICE NUMBER (6,2),

      CONSTRAINT pk_APPLIANCEID PRIMARY KEY (APPLIANCEID, MODELID)

      );

       

       

      CREATE TABLE WHOLESALER(

      WHOLESALERID CHAR(4)NOT NULL,

      BUSINESSNAME VARCHAR2(25),

      ADDRESS VARCHAR2(40),

      CITY VARCHAR2(15),

      STATE VARCHAR2 (3),

      CONTACTNO CHAR(10),

      CONSTRAINT WHOLESALER_PK PRIMARY KEY(WHOLESALERID));

       

       

       

       

      CREATE TABLE DELIVERY

        (

      DELIVERYID CHAR(5) NOT NULL,

      SHOPID CHAR(4) NOT NULL,

      APPLIANCEID CHAR(4) NOT NULL,

      MODELID CHAR(4) NOT NULL,

      WHOLESALERID CHAR(4) NOT NULL,

      DELIVERYDATE DATE,

      QUANTITY NUMBER(2),

      PRICEPERUNIT NUMBER(6,2),

       

       

      CONSTRAINT DELIVERY_PK PRIMARY KEY(DELIVERYID),

      CONSTRAINT DELIVERY_FK1 FOREIGN KEY (SHOPID) REFERENCES SHOP(SHOPID),

      CONSTRAINT DELIVERY_FK2 FOREIGN KEY (WHOLESALERID) REFERENCES WHOLESALER(WHOLESALERID),

      CONSTRAINT DELIVERY_FK3 FOREIGN KEY (MODELID, APPLIANCEID) REFERENCES APPLIANCE(MODELID, APPLIANCEID),

      CONSTRAINT DELIVERY_FK4 FOREGIN KEY (APPLIANCEID) REFERENCES APPLIANCES(APPLIANCEID));

       

       

      -------------------------------------------------------------------------------------------

       

       

      INSERT INTO SHOP VALUES ('S001', '23 BROADWAY', 'SYDNEY', 'NSW', '0298762234');

      INSERT INTO SHOP VALUES ('S002', '39 AUBURN', 'SYDNEY', 'NSW', '0290224533');

      INSERT INTO SHOP VALUES ('S003', '486 PHILLIP', 'CANBERRA', 'ACT', '0278923455');

      INSERT INTO SHOP VALUES ('S004', '56 TORQUAY', 'GEELONG', 'VIC', '0357788990');

      INSERT INTO SHOP VALUES ('S005', '99 ST KILDA', 'MELBOURNE', 'VIC', '0356331121');

      INSERT INTO SHOP VALUES ('S006', '30 RIVERSIDE', 'LAUNCESTON', 'TAS', '0344556611');

      INSERT INTP SHOP VALUES ('S007','7 CAIRNS ST', 'CAIRNS', 'QLD', '0756112098');

      INSERT INTO SHOP VALUES ('S008','19 CLARE', 'ADELAIDE', 'SA', '0834553555');

      INSERT INTO SHOP VALUES ('S009','903 HERBERT', 'DARWIN', 'NT', '0855667788');

      INSERT INTO SHOP VALUES ('S010','19 YOKINE', 'PERTH', 'WA', '0823451122');

       

       

       

       

      INSERT INTO APPLIANCE VALUES ('F001', 'M001', '2 DOOR FRIDGE','2015', '2344.67');

      INSERT INTO APPLIANCE VALUES ('F001', 'M034', 'SMART FRIDGE', '2014', '8899.09');

      INSERT INTO APPLIANCE VALUES ('F001', 'M099', '1 DOOR FRIDGE', '2013', '777.99');

      INSERT INTO APPLIANCE VALUES ('TV01', 'M001', 'SMART LED TV', '2015', '6778.93');

      INSERT INTO APPLIANCE VALUES ('TV01', 'M003', 'LCD TV', '2012', '2211.12');

      INSERT INTO APPLIANCE VALUES ('TV01', 'M015', '3D SMART LCD TV', '2014', '3446.78');

      INSERT INTO APPLIANCE VALUES ('W001', 'M001', 'WASHING MACHINE TL', '2014', '904.83');

      INSERT INTO APPLIANCE VALUES ('W001', 'M099', 'WASHING MACHINE FL', '2015', '1988.29');

      INSERT INTO APPLIANCE VALUES ('W001', 'M321', 'WASHING MACHINE NODRYER', '2010', '655.21');

      INSERT INTO APPLIANCE VALUES ('K001', 'M001', 'KETTLE-1L', '2013', '88.50');

      INSERT INTO APPLIANCE VALUES ('K001', 'M089', 'KETTLE-5L', '2015', '121.90');

      INSERT INTO APPLIANCE VALUES ('MI01', 'M034', 'MICROWAVE-20L', '2012', '200.00');

      INSERT INTO APPLIANCE VALUES ('MI01', 'M009', 'MICROWAVE-34L', '2013', '300.00');

       

       

      INSERT INTO WHOLESALER VALUES ('W001', 'GOT WHOLESALERS', '24 LANECOVE RD', 'SYDNEY', 'NSW', '0298999099');

      INSERT INTO WHOLESALER VALUES ('W002', 'SUITS WHOLESALERS', '562 PRINCESS ST', 'CANBERRA', 'ACT', '0290009000');

      INSERT INTO WHOLESALER VALUES ('W003', 'RING WHOLESALERS', '435 REASON RD', 'PERTH', 'WA', '0844445555');

      INSERT INTO WHOLESALER VALUES ('W004', 'BARRY WHOLESALERS', '11 SYMPHONY DRIVE', 'DARWIN', 'NT', '0811112222');

      INSERT INTO WHOLESALER VALUES ('W005', 'MURPHY WHOLESALERS', '77 PILOT ST', 'HOBART', 'TAS', '0355567778');

      INSERT INTO WHOLESALER VALUES ('W006', 'PABLO WHOLESALERS', '49 COGNO RD', 'ADELAIDE', 'SA', '0899990000');

      INSERT INTO WHOLESALER VALUES ('W007', 'ELLIS WHOLESALERS', '133 PARTNER DRIVE', 'MELBOURNE', 'VIC', '0344556622');

       

       

       

       

       

       

      INSERT INTO DELIVERY VALUES ('D001', 'S001', 'F001', 'M001', 'W001', '01/AUG/15', '45', '2000.9');

      INSERT INTO DELIVERY VALUES ('D002', 'S001', 'TV01', 'M015', 'W003', '03/AUG/15', '23', '3000.7');

      INSERT INTO DELIVERY VALUES ('D003', 'S003', 'TV01', 'M001', 'W005', '11/AUG/15', '21', '6000.4');

      INSERT INTO DELIVERY VALUES ('D004', 'S004', 'F001', 'M034', 'W002', '19/AUG/15', '18', '8000.9');

      INSERT INTO DELIVERY VALUES ('D005', 'S004', 'W001', 'M099', 'W003', '16/AUG/15', '10', '1500');

      INSERT INTO DELIVERY VALUES ('D006', 'S005', 'TV01', 'M001', 'W002', '10/AUG/15', '21', '6000');

      INSERT INTO DELIVERY VALUES ('D007', 'S007', 'W001', 'M099', 'W002', '21/AUG/15', '34', '1500');

      INSERT INTO DELIVERY VALUES ('D008', 'S002', 'F001', 'M001', 'W007', '21/AUG/15', '45', '2000.9');

      INSERT INTO DELIVERY VALUES ('D009', 'S006', 'W001', 'M001', 'W006', '11/AUG/15', '41', '800.55');

      INSERT INTO DELIVERY VALUES ('D010', 'S010', 'W001', 'M001', 'W003', '10/AUG/15', '26', '800.55');

      INSERT INTO DELIVERY VALUES ('D011', 'S008', 'F001', 'M034', 'W001', '29/AUG/15', '50', '8000');

      INSERT INTO DELIVERY VALUES ('D012', 'S009', 'TV01', 'M015', 'W003', '22/AUG/15', '10', '3000.7');

      INSERT INTO DELIVERY VALUES ('D013', 'S009', 'MI01', 'M034', 'W004', '29/AUG/15', '70', '150');

      INSERT INTO DELIVERY VALUES ('D014', 'S007', 'K001', 'M001', 'W005', '20/AUG/15', '23', '50.3');

      INSERT INTO DELIVERY VALUES ('D015', 'S002', 'MI01', 'M009', 'W002', '07/AUG/15', '40', '200');

        • 1. Re: Sql creating tables
          thatJeffSmith-Oracle

          Did you run this in SQL Developer? We show the errors and line numbers.

           

          CREATE TABLE SHOP

          (

          SHOPID CHAR(4) NOT NULL,

          ADDRESS VARCHAR2(40),

          CITY VARCHAR2(15),

          STATE VARCHAR2 (3),

          CONTACTNO CHAR(10)

           

           

          CONSTRAINT SHOP_PK PRIMARY KEY (SHOPID)

          );

           

          run this, results in:

           

          Error starting at line : 6 in command -

          CREATE TABLE SHOP

          (

          SHOPID CHAR(4) NOT NULL,

          ADDRESS VARCHAR2(40),

          CITY VARCHAR2(15),

          STATE VARCHAR2 (3),

          CONTACTNO CHAR(10) -- add a COMMA here

          CONSTRAINT SHOP_PK PRIMARY KEY (SHOPID)

          )

          Error report -

          SQL Error: ORA-00907: missing right parenthesis

          00907. 00000 -  "missing right parenthesis"

          *Cause:   

          *Action:

           

           

          Look up..you'll see you're missing a comma after the last column and before your table constraint.

           

          Fix that and the INSERTs for SHOP will work.

          • 2. Re: Sql creating tables

            Hi, first time user of sql developer and i cant understand why some only some table were created and some werent. i made sure to follow sql coding rules. but i keep getting error of table or view does not exist. and Error starting at line : 103 in command -

            You are trying to RUN before you learn how to WALK. By that I mean you are trying to write a script that contains many individual pieces before you have tested EVERY piece to make sure it works.

             

            It is considered BEST PRACTICE to write MODULAR code/scripts.

             

            1. create simple pieces where each piece does ONE, and ONLY ONE, job

             

            2. test each piece by executing it by itself - that will identify any syntax issues and determine if it produces the correct result.

             

            3. combine pieces that ALREADY WORK to make more complex modules.

             

            Start over and do it that way.

             

            1. Open a second window

            2. copy and test each piece of your script, one at a time to that second window and test it.

             

            If you do you will see IMMEDIATELY what is wrong.

             

            And because you are only testing one piece at a time it will be OBVIOUS which piece, or pieces, is causing the problem.

             

            When you do NOT use that process you wind up with a script that contains tens, or hundreds, of steps. There could be tens or hundreds of problems in the script but the script may blow up and terminate after finding the first one., Then you fix that first problem only to have the script blow up when it gets to the second problem.

             

            Learn how to do it right the first time.

             

            Then if you need help post a question in the forums. But when you do post provide ALL of the info needed to help you.

            but i keep getting error of table or view does not exist. and Error starting at line : 103 in command -

            Don't tell us - SHOW US!

             

            We have NO IDEA what line #103 is but you know - so why not SHOW US? And SHOW US the actual result you get - the ACTUAL exception code and error message.

            • 3. Re: Sql creating tables
              3043652

              thank you for guidance and i realized where i went wrong after i  tested each piece one by one. also there is one table where i can't figure out the error.

               

              CREATE TABLE DELIVERY

                (

              DELIVERYID CHAR(5) NOT NULL,

              SHOPID CHAR(4) NOT NULL,

              APPLIANCEID CHAR(4) NOT NULL,

              MODELID CHAR(4) NOT NULL,

              WHOLESALERID CHAR(4) NOT NULL,

              DELIVERYDATE DATE,

              QUANTITY NUMBER(2),

              PRICEPERUNIT NUMBER(6,2),

               

               

              CONSTRAINT DELIVERY_PK PRIMARY KEY(DELIVERYID),

              CONSTRAINT DELIVERY_FK1 FOREIGN KEY (SHOPID) REFERENCES SHOP(SHOPID),

              CONSTRAINT DELIVERY_FK2 FOREIGN KEY (WHOLESALERID) REFERENCES WHOLESALER(WHOLESALERID),

              CONSTRAINT DELIVERY_FK3 FOREIGN KEYS (MODELID, APPLIANCEID) REFERENCES APPLIANCE(MODELID, APPLIANCEID),

              );

               

              oracle community.png

              • 4. Re: Sql creating tables

                thank you for guidance and i realized where i went wrong after i  tested each piece one by one. also there is one table where i can't figure out the error.

                Hmmm - let's see if you can analyze what you need to do.

                 

                1. you test each piece one by one and were able to figure out the problem

                2. you now have a new problem with one piece

                3. but you don't test that one piece 'one part at a time'

                 

                I already told you to break a compound piece into SMALL pieces.

                 

                You already tried that and found that it worked.

                 

                So why haven't you done that again with the CREATE TABLE ddl that you are having a problem with?

                 

                1. it creates a table

                2. it creates four constraints

                 

                So try just creating the table and see if that works.

                 

                When you get that to work try adding ONE CONSTRAINT and see if that works.

                 

                When you get that to work try adding ANOTHER constraint and see if that works.

                 

                There are NO SHORTCUTS. Troubleshooting is HARD WORK. You need to use a process that you already knows will work and test ONE THING AT A TIME.

                 

                This is how your code ends.

                APPLIANCE(MODELID, APPLIANCEID),

                );

                Look at EVERY CHARACTER, one at a time.

                 

                Do you SEE the problem now?