FAQ
Hi,

For some non-obvious reason, one of my insert statements out of about 90
refuses to work. The error being "missing comma". I know for sure there is
no missing comma or any other syntax error, as it follows exact syntax as
all the other insert statements.

I have found though, that it only throws this error when the number of input
fields exceed a particular number, say 14. I have inserted tables that were
in excess of this, so its not that it can't insert more than 14. Even after
swapping around what fields it inserts it still throws an error when it has
in excess of 14 input fields. No matter how many lines I format it into,
the "missing comma" is thrown on the second line unless I place all of the
statement onto one line then it doesn't get executed (which is unexplainable
in itself). I have also tried removed trailing spaces also.

Either I'm missing something obvious or something weird is going on. Has
anyone got any idea why, or how to overcome this problem.

Following is the sql:

SQL> insert into unsecured.TPROGRESSCERTIFICATES (PROGRESSCERTIFICATESID,
PAYMENTID, CONTRACTID, CERTIFICATENO, INVOICENO, AMOUNTAPPROVED, DATE_R,

CURRENTCONTRACTPRICE, VARIATIONS, NOW_DUE, THISRETENTION, PREVIOUSRETENTION,

PREVIOUSPAYMENTS, PPSPERCENT, EXPORTDATE)

2 select ID, PAYMENTID, CONTRACTID, CERTIFICATENO, INVOICENO,
AMOUNTAPPROVED, DATE_R, CURRENTCONTRACTPRICE, VARIATIONS, NOW_DUE,

THISRETENTION, PREVIOUSRETENTION, PREVIOUSPAYMENTS, PPSPERCENT, EXPORTDATE

3 from contract.TPROGRESSCERTIFICATES t
4 where t.paymentid IN (select paymentid from unsecured.tpayments);
select ID, PAYMENTID, CONTRACTID, CERTIFICATENO, INVOICENO, AMOUNTAPPROVED,
DATE_R, CURRENTCONTRACTP

*
ERROR at line 2:
ORA-00917: missing comma

The above works if I take out any one of the insert fields (well it attempts
to insert it anyway, and then finds constraint error etc. if say primary key
is taken out)

Thankyou in advance for any assistance.

Search Discussions

  • Tim Sawmiller at Jan 24, 2001 at 12:51 pm
    Maybe you should try using the continuation character '-' at the end of =
    each line...just a thought as I sip my first coffee of the day...
    renee_at_openwindows.com.au 01/23/01 11:45PM >>>
    Hi,

    For some non-obvious reason, one of my insert statements out of about 90
    refuses to work. The error being "missing comma". I know for sure there is
    no missing comma or any other syntax error, as it follows exact syntax as
    all the other insert statements.

    I have found though, that it only throws this error when the number of =
    input
    fields exceed a particular number, say 14. I have inserted tables that =
    were
    in excess of this, so its not that it can't insert more than 14. Even =
    after
    swapping around what fields it inserts it still throws an error when it =
    has
    in excess of 14 input fields. No matter how many lines I format it into,
    the "missing comma" is thrown on the second line unless I place all of the
    statement onto one line then it doesn't get executed (which is unexplainabl=
    e
    in itself). I have also tried removed trailing spaces also.

    Either I'm missing something obvious or something weird is going on. Has
    anyone got any idea why, or how to overcome this problem.

    Following is the sql:

    SQL> insert into unsecured.TPROGRESSCERTIFICATES (PROGRESSCERTIFICATESID,
    PAYMENTID, CONTRACTID, CERTIFICATENO, INVOICENO, AMOUNTAPPROVED, DATE_R,

    CURRENTCONTRACTPRICE, VARIATIONS, NOW_DUE, THISRETENTION, PREVIOUSRETENTION=,
    PREVIOUSPAYMENTS, PPSPERCENT, EXPORTDATE)

    2 select ID, PAYMENTID, CONTRACTID, CERTIFICATENO, INVOICENO,
    AMOUNTAPPROVED, DATE_R, CURRENTCONTRACTPRICE, VARIATIONS, NOW_DUE,

    THISRETENTION, PREVIOUSRETENTION, PREVIOUSPAYMENTS, PPSPERCENT, EXPORTDATE

    3 from contract.TPROGRESSCERTIFICATES t
    4 where t.paymentid IN (select paymentid from unsecured.tpayments);
    select ID, PAYMENTID, CONTRACTID, CERTIFICATENO, INVOICENO, AMOUNTAPPROVED,=

    DATE_R, CURRENTCONTRACTP

    *
    ERROR at line 2:
    ORA-00917: missing comma

    The above works if I take out any one of the insert fields (well it =
    attempts
    to insert it anyway, and then finds constraint error etc. if say primary =
    key
    is taken out)

    Thankyou in advance for any assistance.

    ren=E9e

    --=20
    Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
    --=20
    Author: Renee Bonnett
    INET: renee_at_openwindows.com.au=20

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Yosi_at_comhill.com at Jan 24, 2001 at 4:31 pm
    shot in the dark - could it have to do with a too long
    sql script? have you tried cutting the file into two
    pieces?

    fwiw,

    yosi
    -----Original Message-----
    From: Renee Bonnett
    Sent: Tuesday, January 23, 2001 11:45 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Unable to insert data
    =20
    =20
    Hi,
    =20
    For some non-obvious reason, one of my insert statements out=20
    of about 90
    refuses to work. The error being "missing comma". I know for=20
    sure there is
    no missing comma or any other syntax error, as it follows=20
    exact syntax as
    all the other insert statements.
    =20
    I have found though, that it only throws this error when the=20
    number of input
    fields exceed a particular number, say 14. I have inserted=20
    tables that were
    in excess of this, so its not that it can't insert more than=20
    14. Even after
    swapping around what fields it inserts it still throws an=20
    error when it has
    in excess of 14 input fields. No matter how many lines I=20
    format it into,
    the "missing comma" is thrown on the second line unless I=20
    place all of the
    statement onto one line then it doesn't get executed (which=20
    is unexplainable
    in itself). I have also tried removed trailing spaces also.
    =20
    Either I'm missing something obvious or something weird is=20
    going on. Has
    anyone got any idea why, or how to overcome this problem.
    =20
    Following is the sql:
    =20
    SQL> insert into unsecured.TPROGRESSCERTIFICATES=20
    (PROGRESSCERTIFICATESID,
    PAYMENTID, CONTRACTID, CERTIFICATENO, INVOICENO,=20
    AMOUNTAPPROVED, DATE_R,
    CURRENTCONTRACTPRICE, VARIATIONS, NOW_DUE, THISRETENTION,=20
    PREVIOUSRETENTION,
    PREVIOUSPAYMENTS, PPSPERCENT, EXPORTDATE)
    2 select ID, PAYMENTID, CONTRACTID, CERTIFICATENO, INVOICENO,
    AMOUNTAPPROVED, DATE_R, CURRENTCONTRACTPRICE, VARIATIONS, NOW_DUE,
    THISRETENTION, PREVIOUSRETENTION, PREVIOUSPAYMENTS,=20
    PPSPERCENT, EXPORTDATE
    3 from contract.TPROGRESSCERTIFICATES t
    4 where t.paymentid IN (select paymentid from =
    unsecured.tpayments);
    select ID, PAYMENTID, CONTRACTID, CERTIFICATENO, INVOICENO,=20
    AMOUNTAPPROVED,
    DATE_R, CURRENTCONTRACTP
    *
    ERROR at line 2:
    ORA-00917: missing comma
    =20
    The above works if I take out any one of the insert fields=20
    (well it attempts
    to insert it anyway, and then finds constraint error etc. if=20
    say primary key
    is taken out)
    =20
    Thankyou in advance for any assistance.
    =20
    ren=E9e
    =20
    --=20
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --=20
    Author: Renee Bonnett
    INET: renee_at_openwindows.com.au
    =20
    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing = Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
  • Jamadagni, Rajendra at Jan 24, 2001 at 7:04 pm
    Okay,

    I surely read your emails 3 times to ensure that I am not missing something.
    This seems to me a script of DML statements. So you have *semicolon after
    first statement missing* somehow. Oracle is dumb enough to report that as a
    comma missing.

    You need a; after EXPORTDATE)

    HTH

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    QOTD: Any clod can have facts, but having an opinion is an art !
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you.
  • Renee Bonnett at Jan 24, 2001 at 10:20 pm
    Hi,

    Thankyou for everyones reply.

    I understand that it could been hard to figure out the exactly what my sql
    statement was doing, but it consisted of only the one command, an insert
    statement pulling data from another table (ie. the select statement) so
    placing a ';' at end of line would have separated the command into 2.

    Still relatively ignorant when it comes to oracle, I did come to realise
    that I had exceeded the line buffer (256 characters) that exists in sql*plus
    which I knew but became mixed up in all the errors I was receiving yesterday
    (and lack of sleep). Because originally all insert statements existed in a
    script file and this table threw a different error so when tested on command
    line, at that point it didn't become provalent that it exceeded the line
    buffer.

    -renee *feeling stoopid*

    -----Original Message-----
    From: root_at_fatcity.com On Behalf Of Jamadagni,
    Rajendra
    Sent: Thursday, January 25, 2001 6:05 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Unable to insert data

    Okay,

    I surely read your emails 3 times to ensure that I am not missing something.
    This seems to me a script of DML statements. So you have *semicolon after
    first statement missing* somehow. Oracle is dumb enough to report that as a
    comma missing.

    You need a; after EXPORTDATE)

    HTH

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    QOTD: Any clod can have facts, but having an opinion is an art !
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    This e-mail message is confidential, intended only for the named
    recipient(s) above and may contain information that is privileged, attorney
    work product or exempt from disclosure under applicable law. If you have
    received this message in error, or are not the named recipient(s), please
    immediately notify ESPN at (860) 766-2000 and delete this e-mail message
    from your computer, Thank you.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jamadagni, Rajendra
    INET: rajendra.jamadagni_at_espn.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 24, '01 at 4:43a
activeJan 24, '01 at 10:20p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase