ORA-00917: missing comma error

[anupamadatta]

Hi there,

I am trying to execute one Insert query using Pro C . the Insert query is as below:

Prepared statement is :

stmnt.len =
sprintf((char*) stmnt.arr,
"INSERT INTO %s ("
EPTORDER_SOID_C OL ", "
EPTORDER_EPTNUM BER_COL ", "
TBS_COLUMN_REGI ON ", "
TBS_COLUMN_TIME ", "
TBS_COLUMN_PROC ESS_INFO
") VALUES ("
":soid, "
":eptno, "
":eptRegion:ept RegionInd, "
" (SELECT TO_DATE(TO_CHAR ("
" TO_DATE('010119 70','ddmmyyyy') + 1/24/60/60 * :t,"
TBS_ISO_FORMAT "), " TBS_ISO_FORMAT ") FROM dual), "
":pi"
")",
table.c_str());

and the Values go in as bind variables as follows:

EXEC SQL EXECUTE TBS_INSERT_ID USING
:soid,
:eptno,
:dbRegionId INDICATOR :dbRegionIdInd,
:when,
:pinfo;

the value of soid=some number
eptno=some number
dbRegionId INDICATOR :dbRegionIdInd = some number
when = something like 1183579173 - which is of data type long.
and pinfo=some number.

The above query does the Insert into the a table and the Insert happens properly on one server and doesn't happen on the other. It throws Missing comma error on one of the servers.

but if we alter the query like

stmnt.len =
sprintf((char*) stmnt.arr,
"INSERT INTO %s ("
EPTORDER_SOID_C OL ", "
EPTORDER_EPTNUM BER_COL ", "
TBS_COLUMN_REGI ON ", "
TBS_COLUMN_PROC ESS_INFO ", "
TBS_COLUMN_TIME
") VALUES ("
":soid, "
":eptno, "
":eptRegion:ept RegionInd, "
":pi,"
" (SELECT TO_DATE(TO_CHAR ("
" TO_DATE('010119 70','ddmmyyyy') + 1/24/60/60 * :t,"
TBS_ISO_FORMAT "), " TBS_ISO_FORMAT ") FROM dual)"
")",
table.c_str());

EXEC SQL EXECUTE TBS_INSERT_ID USING
:soid,
:eptno,
:dbRegionId INDICATOR :dbRegionIdInd,
:pinfo,
:when;

it works fine on both Servers.

And also when the query is run directly on the SQL*Plus, it throws no error on either servers. Only when run through the application, even though syntactically its very correct, it throw an error on one of the server.

I am using 10.2 of the oracle version on both the servers. Can anyone explain me about this strange behavior? Does it have something to do with the Pro C complier? or is it problem with the Oracle?

Thanks in advance.

[debasisdas]

if query is run directly on the SQL*Plus, and it throws no error then it is ok from oracle side.
U just need to check for syntax in your frontend .

[cshilling]

I know this is about 6 years too late, but in case anyone (like me) stumbles upon this error - it looks like this is an Oracle bug related to the CURSOR_SHARING database parameter. The workaround for this bug is to change the parameter to EXACT. It's listed as bug # 9877980 on Oracle's support website. They claim they've fixed the issues on 11.2.0.3, but I have seen this error occur on 11.2.0.3 databases, so it's not completely resolved in that version either.

[PreethiGowri]

Even if i'm late to reply a very useful site that i use "Oracle Database Server Error Codes"