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!

i cant enter data into a table?!?!?!?

Discussion in 'SQL PL/SQL' started by ajcastillo, Oct 9, 2013.

  1. ajcastillo

    ajcastillo Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    SQL> desc music;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ARTIST VARCHAR2(50)
    HOMETOWN VARCHAR2(20)
    ALBUMS VARCHAR2(3)
    MEMBERS NUMBER(1)
    FOUNDED DATE
    LARGESTSALES NUMBER(1,1)

    SQL> insert into music
    2 values('tool','LA,USA','4',5,'01-JAN-90',300.000);
    values('tool','LA,USA','4',5,'01-JAN-90',300.000)
    *
    ERROR at line 2:
    ORA-01438: value larger than specified precision allowed for this column


    what the heck does this mean? i am in an intro to oracle class...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's the NUMBER(1,1) definition. You cannot insert 300..000 into that column as it will contain only 1 decimal point precision, like .1:

    Code (SQL):
    SQL> CREATE TABLE music(
      2  artist varchar2(50),
      3  hometown varchar2(20),
      4  albums     varchar2(3),
      5  members NUMBER(1),
      6  founded DATE,
      7  largestsales NUMBER(1,1));
     
    TABLE created.
     
    SQL>
    SQL> INSERT INTO music
      2  VALUES('tool','LA,USA','4',5,'01-JAN-90',300.000);
    VALUES('tool','LA,USA','4',5,'01-JAN-90',300.000)
                                             *
    ERROR at line 2:
    ORA-01438: VALUE larger than specified PRECISION allowed FOR this COLUMN

    SQL>
    SQL> ALTER TABLE music MODIFY largestsales NUMBER(12,3);
     
    TABLE altered.
     
    SQL>
    SQL> INSERT INTO music
      2  VALUES('tool','LA,USA','4',5,to_date('01-JAN-90', 'DD-MON-RR'),300.000);
     
    1 ROW created.
     
    SQL>
    SQL> SELECT artist, hometown, albums, members, founded, largestsales FROM music;
     
    ARTIST                                             HOMETOWN             ALB
    -------------------------------------------------- -------------------- ---
       MEMBERS FOUNDED   LARGESTSALES
    ---------- --------- ------------
    tool                                               LA,USA               4
             5 01-JAN-90          300

    SQL>
    Also note that I used a to_date conversion for your date string; you cannot rely on default date formats to insert dates using strings because default formats can be changed without notice:

    Code (SQL):
    SQL> ALTER SESSION SET nls_date_format='MON DD, RRRR';
    SESSION altered.
    SQL>
    SQL> INSERT INTO music
      2  VALUES('tool','LA,USA','4',5,'01-JAN-90',300.000);
    VALUES('tool','LA,USA','4',5,'01-JAN-90',300.000)
                                 *
    ERROR at line 2:
    ORA-01843: NOT a valid MONTH

    SQL>
    I realize this is an Intro to Oracle class but you need to know this information so you won't be wondering why your date strings aren't recognized.
     
  3. ajcastillo

    ajcastillo Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    thank you so much. I was under the impression that number(3,3) meant you can have 333.333 or 555.555
     
  4. ajcastillo

    ajcastillo Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    i see what i did now. i did number(1,1) instead of defining a larger precision. i have been so frustrated with this but now i see the dumb mistake
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's a mistake, it's definitely NOT a 'dumb mistake'. You cannot blame yourself for not knowing how NUMBER fields should be configured, especially since you are just starting out with Oracle.

    The online documentation is found here:

    http://tahiti.oracle.com

    Please visit this site and get familiar with it as it will be of help to you as you learn.