1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Error after loading DDL script

Discussion in 'SQL PL/SQL' started by OracleTechie, Feb 10, 2011.

  1. OracleTechie

    OracleTechie Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hello All,

    I am running Oracle 10g on my machine. I have create an ER diagram in Toad Data Modeler, which includes all the keys, contraints etc.
    I have generated a DDL script which I want to biuld my tables with in Oracle.

    I have loaded the DDL script using SQL*Plus Worksheet and there are no problems.
    I then tryed to insert some test data into my tables and I keep getting an error code of:

    ORA-00942, table or view does not exist.


    Now I know the tables have been created and also by verifying this with the data dictionary using

    select table_name
    from user_tables;

    TABLE_NAME
    =========
    Table1
    Table2
    Table3
    .
    .
    etc


    It then displays all 20 of my tables, as above. I am using a Visual Basic front end and I can see all 20 tables listed there also, with attribute names for each table.
    So guys, where am I going wrong?

    Thankyou in advance,
    OracleTechie
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Are you inserting using Front End? Then check if the front end is connected to the same schema where the tables are created.
     
  3. OracleTechie

    OracleTechie Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hello Raj,
    I now have this working.

    Toad data modeler automatically puts tables and attributes in double quotes.
    select * from "Customer"; will work
    but
    select * from Customer; will not work

    I have read elsewhere it is bad practice to use double quotes as this can cause problems later on in coding. Also it is better to put table names and attributes in upper case.
    I will not make the alterations and let you know how I get on. I will also see if I can manipulate the records in the front end/Visual Basic. :)
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Oracle, when left to its own devices, will make object names uppercase when created without quotes:

    create table Table1(....);

    will create a table named TABLE1 in the data dictionary and will allow statements such as:

    insert into table1 (...) values (...);

    and

    insert into TABLE1 (...) ...

    and

    insert into Table1 (....) ...

    As you've discovered using quotes can cause difficulties as you will need to use quotes around object names which are not all uppercase:

    create table "Table1" (...)

    will require

    insert into "Table1" (...) ...

    to allow Oracle to 'find' the table and insert into it, which is why your initial insert statements failed as Oracle has no table named TABLE1 in your database (the output of your select from your original post proves that). This is an easy problem to miss as those who write English are so used to seeing names capitalized that the issue isn't noticeable at first glance (I, too, missed this when I first saw your post).

    As you've mentioned it's best to not quote object names in order to avoid this problem that had you confused.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Yes.. You got the reason. Try to avoid double quotes and execute the same script. It should work