Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2011
    Posts
    10

    Unanswered: What is wrong with this INSERT statement?

    It gives me the ORA-00933 code that says improperly ended Error at Command Line:4 Column:104
    Code:
    INSERT INTO customer
    (CUST_ID, F_NAME, L_NAME, ADDRESS, CITY, STATE, PHONE, LATE_FEES, DOB, RENTAL_ID, C_PASSWORD)
    VALUES
    (1, 'John', 'McDonald', '221 Second St', 'Amberville', 'SD', 605-555-5555, 4.00, '10-OCT-72', 2324, NULL),
    (2, 'Beth', 'Carr', '100 Cypress AVE', 'Amberville', 'SD', 605-345-6532, 11.00, '01-DEC-86', 9875, NULL),
    (3, 'Ben', 'McKey', '23451 256th St', 'Amberville', 'SD', 605-345-9023, 0.00, '01-JAN-83', 2233, 'lion'),
    (4, 'Judd', 'Gunther', '2020 County Rd 2', 'Norton', 'SD', 605-345-3764, 22.00, '04-JUL-84', 9098, 'pencil'),
    (5, 'Michael', 'Jordan', '605 Cypress AVE', 'Amberville', 'SD', 605-345-6363, 0.00, '11-NOV-66', 2859 0983, NULL),
    (6, 'Tukomana', 'Smith', '1200 Titan St', 'McManarberry', 'SD', 605-743-1673, 0.00, '07-APR-89', 1121, NULL),
    (7, 'Lee', 'James', '345 Main', 'Amberville', 'SD', 605-345-1234, 0.00, '24-DEC-00', 9548, NULL),
    (8, 'Sandra', 'Morgan', '780 Spooner St', 'Arland', 'SD', 605-223-9373, 0.00, '15-MAR-93', 99873 8376, NULL);
    Last edited by C_J_GO; 06-21-11 at 12:39.

  2. #2
    Join Date
    Jun 2011
    Posts
    10
    I can insert line by line, but I have a couple tables that need multiple lines inserted

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Oracle does not support this syntax (which is pretty obvious when you look into the manual)

  4. #4
    Join Date
    Jun 2011
    Posts
    10
    Is there another way to create multiple row without having to insert them one by one?

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by C_J_GO View Post
    Is there another way to create multiple row without having to insert them one by one?
    Code:
    INSERT INTO customer
    (CUST_ID, F_NAME, L_NAME, ADDRESS, CITY, STATE, PHONE, LATE_FEES, DOB, RENTAL_ID, C_PASSWORD)
    SELECT 1, 'John', 'McDonald', '221 Second St', 'Amberville', 'SD', 605-555-5555, 4.00, '10-OCT-72', 2324, NULL FROM DUAL
    UNION ALL
    SELECT 2, 'Beth', 'Carr', '100 Cypress AVE', 'Amberville', 'SD', 605-345-6532, 11.00, '01-DEC-86', 9875, NULL FROM DUAL
    UNION ALL 
    SELECT 3, 'Ben', 'McKey', '23451 256th St', 'Amberville', 'SD', 605-345-9023, 0.00, '01-JAN-83', 2233, 'lion' FROM DUAL
    ....

  6. #6
    Join Date
    Jun 2011
    Posts
    10
    This worked. Thanks for the help!

    Quote Originally Posted by shammat View Post
    Code:
    INSERT INTO customer
    (CUST_ID, F_NAME, L_NAME, ADDRESS, CITY, STATE, PHONE, LATE_FEES, DOB, RENTAL_ID, C_PASSWORD)
    SELECT 1, 'John', 'McDonald', '221 Second St', 'Amberville', 'SD', 605-555-5555, 4.00, '10-OCT-72', 2324, NULL FROM DUAL
    UNION ALL
    SELECT 2, 'Beth', 'Carr', '100 Cypress AVE', 'Amberville', 'SD', 605-345-6532, 11.00, '01-DEC-86', 9875, NULL FROM DUAL
    UNION ALL 
    SELECT 3, 'Ben', 'McKey', '23451 256th St', 'Amberville', 'SD', 605-345-9023, 0.00, '01-JAN-83', 2233, 'lion' FROM DUAL
    ....

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or, INSERT ALL:
    Code:
    SQL> create table test
      2  (id number, f_name varchar2(20), l_name varchar2(20));
    
    Table created.
    
    SQL> insert all
      2    into test (id, f_name, l_name) values (1, 'Little', 'Foot')
      3    into test (id, f_name, l_name) values (2, 'Big', 'Foot')
      4    into test (id, f_name, l_name) values (3, 'Ben', 'McKey')
      5  select * from dual;
    
    3 rows created.
    
    SQL>

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Littlefoot View Post
    Or, INSERT ALL:
    Ah, right. I always forget that.

  9. #9
    Join Date
    Jun 2011
    Posts
    2
    Hi im using this same technique but it is telling me there is too many values but everything seems right, any ideas where I am going wrong and im aware it could be the most simplest thing but I am a complete novice =/

    INSERT INTO CUSTOMERS
    (CUSTOMERS_ID,
    PURCHASED_WITHOUT_ACCOUNT,
    CUSTOMERS_GENDER,
    CUSTOMERS_FIRSTNAME,
    CUSTOMERS_LASTNAME,
    CUSTOMERS_DOB,
    CUSTOMERS_EMAIL_ADDRESS,
    CUSTOMERS_DEFAULT_ADDRESS_ID,
    CUSTOMERS_TELEPHONE,
    CUSTOMERS_FAX, CUSTOMERS_NEWSLETTER,
    GUEST_ACCOUNT,
    CUSTOMERS_LOGIN,
    CUSTOMERS_GROUP_NAME,
    CUSTOMERS_GROUP_ID,
    CUSTOMERS_GROUP_RA,
    CUSTOMERS_PAYMENT_ALLOWED,
    CUSTOMERS_SHIPMENT_ALLOWED)

    SELECT 1, 2,0,'m','David','Doe','1984-01-16 00:00:00','janejoe@doe.com',2,'','','1',0,NULL,'Re tail',0,'','', NULL FROM DUAL
    UNION ALL
    SELECT 2, 4,0,'f','Marylin','Doe','1953-08-28 00:00:00','janejoe@doe.com',4,'','','',0,NULL,'Ret ail',0,'0','', NULL FROM DUAL
    UNION ALL
    SELECT 3, 5,0,'f','Shoshanah','Doe','1983-11-28 00:00:00','janejoe@doe.com',5,'','','',0,NULL,'Ret ail',0,'0','', NULL FROM DUAL

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You are inserting 19 values into 18 columns.

    Note that this:
    Code:
    '1984-01-16 00:00:00'
    is a string. You should apply the TO_DATE function with a correct format mask.

  11. #11
    Join Date
    Jun 2011
    Posts
    2
    Quote Originally Posted by Littlefoot View Post
    You are inserting 19 values into 18 columns.

    Note that this:
    Code:
    '1984-01-16 00:00:00'
    is a string. You should apply the TO_DATE function with a correct format mask.
    Thanks, how would I go about this? I'm a complete novice not entirely sure of what im doing half the time

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A sample table that contains a DATE datatype column:
    Code:
    SQL> create table test (id number, date_col date);
    
    Table created.
    Inserting a record "your way" (i.e. relying on possible implicit conversion):
    Code:
    SQL> insert into test (id, date_col)
      2    values (1, '1984-01-16 00:00:00');
      values (1, '1984-01-16 00:00:00')
                 *
    ERROR at line 2:
    ORA-01861: literal does not match format string
    Inserting a record by letting Oracle know what the value represents:
    Code:
    SQL> insert into test (id, date_col)
      2    values (1, to_date('1984-01-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss'));
    
    1 row created.
    
    SQL>
    If I *know* date format Oracle recognizes, I can omit the TO_DATE function:
    Code:
    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> insert into test (id, date_col)
      2    values (2, '25.06.2011 19:06:42');
    
    1 row created.
    
    SQL>
    However, that is a BAD practice. ALWAYS use TO_DATE function. I know, it is a little bit of more typing, but you know what you are doing.

    For example: I'll modify NLS_DATE_FORMAT and won't tell you which one it is. I'll also insert a record into a table:
    Code:
    SQL> alter session set nls_date_format = '<hidden, just for this example>';
    
    Session altered.
    
    SQL> insert into test (id, date_col)
      2    values (3, '01.02.03');
    
    1 row created.
    What does '01.02.03' represent? What is a day here? A month? A year? No idea? Let's see:
    Code:
    SQL> alter session set nls_date_format = 'dd.mm.yyyy';
    
    Session altered.
    
    SQL> select * from test where id = 3;
    
            ID DATE_COL
    ---------- ----------
             3 02.03.2001
    So it seems that '01' was a year, '02' a day and '03' a month.

    Do you realize why it is important to use TO_DATE?

    More reading about TO_DATE function. Pay attention to "See also" links.

Posting Permissions

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