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!

ORA-00917: missing comma; while every comma is in place. Why?

Discussion in 'SQL PL/SQL' started by Ermiast, May 6, 2013.

  1. Ermiast

    Ermiast Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    In the following code the insert into statement can't insert data instead it says ORA-00917: missing comma. But there's no missed comma. What is the problem?
    I'm using Oracle10gR2

    Code (Text):
    create or replace type animal_ty as object
    (
    breed varchar2(25),
    name varchar2(25),
    birthdate DATE
    );
    /

    create type animals_nt as table of animal_ty;
    /

    create table breeder
    (
    breedername varchar(25),
    animals animals_nt
    )
    nested table animals store as animals_nt_tab;

    insert into breeder values
    (
    'jane james',
    animals_nt(
    animal_ty('dog', 'butch', '4-MAY-2013'),
    animal_ty('dog', 'rover', '4-MAY-2013'),
    animal_ty('dog', 'julio', '4-MAY-2013')
    );
     
  2. Ermiast

    Ermiast Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    The error message I got was misleading, it wasn't a comma I missed but one right parenthesis before the ending semicolon

    insert into breeder values
    (
    'jane james',
    animals_nt(
    animal_ty('dog', 'butch', '4-MAY-2013'),
    animal_ty('dog', 'rover', '4-MAY-2013'),
    animal_ty('dog', 'julio', '4-MAY-2013')
    ));

    this worked !!
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The error message you received was correct as far as Oracle 'knew' since the first possible cause for that statement to be invalid is a missing comma. This is why it's imperative that DBAs and developers read error messages carefully and even more carefully examine their code. You found the actual error in a missing right parenthesis; even though that is a valid and reported error in Oracle in this case it was missed in favor of the missing comma statement.