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_COL ", "
EPTORDER_EPTNUMBER_COL ", "
TBS_COLUMN_REGION ", "
TBS_COLUMN_TIME ", "
TBS_COLUMN_PROCESS_INFO
") VALUES ("
":soid, "
":eptno, "
":eptRegion:eptRegionInd, "
" (SELECT TO_DATE(TO_CHAR("
" TO_DATE('01011970','ddmmyyyy') + 1/24/60/60 * :t,"
TBS_ISO_FORMAT "), " TBS_ISO_FORMAT ") FROM dual), "
"i"
")",
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,
info;

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_COL ", "
EPTORDER_EPTNUMBER_COL ", "
TBS_COLUMN_REGION ", "
TBS_COLUMN_PROCESS_INFO ", "
TBS_COLUMN_TIME
") VALUES ("
":soid, "
":eptno, "
":eptRegion:eptRegionInd, "
"i,"
" (SELECT TO_DATE(TO_CHAR("
" TO_DATE('01011970','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,
info,
: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.