-
Title
ORA-01797 error while running compare repair -
Description
When running compare with a "where" clause to limit the # of rows to be compared, you run into error. On further probing, you look into the sp_desvr log file for that compare on the source $SP_SYS_VARDIR/log directory and see the Oracle error ORA-01797 in it as below:
022: Batch Size : 10000 rows
022: Repair : Off
022: Key Compare : Off
022: Port Number : 2112
022: Where Clause: dial_start_time>=to_date('MAY-19-2008','MON-dd-YYYY') and dial_start_time<=('JUN-12-2008','MON-dd-YYYY')
022: Combined Where Clauses: ( ( dial_start_time>=to_date('MAY-19-2008','MON-dd-YYYY') and dial_start_time<=('JUN-12-2008','MON-dd-Y
YYY') ) )
022: Select Hint :
022: Order By :
022: Oracle Error 1797: ORA-01797: this operator must be followed by ANY or ALL
022: Error 1797 calling OCIStmtExecute in de_table_ask_row_count (203)
022: Status : Error
022: Error 1797 obtaining row count.
022: Compare SQM20.SQM_BB to SQM20.SQM_BB failed. See error message above.
000: DEQ done reading.
000: Exiting....
The command issued for the problem compare can be extracted from the commands_log file from the same directory. It is:
compare table SQM20.SQM_BB to SQM20.SQM_BB target us-rwc-ods1@o.ioq where " dial_start_time>=to_date('MAY
-19-2008','MON-dd-YYYY') and dial_start_time<=('JUN-12-2008','MON-dd-YYYY') " repair port 2112 -
Cause
A syntax error in the command issued for compare causes Shareplex to think that it is a subquery.
-
Resolution
The problem has to do with a missing to_date function in the compare command in the clause after the AND. The command issued was:
compare table SQM20.SQM_BB to SQM20.SQM_BB target us-rwc-ods1@o.ioq where " dial_start_time>=to_date('MAY
-19-2008','MON-dd-YYYY') and dial_start_time<=('JUN-12-2008','MON-dd-YYYY') " repair port 2112
If a SELECT is issued through SQL*Plus on the table in question with the conditions as specified after the "where" clause, it would result in ORA-01797 as Oracle would think of it as a subquery and would require ANY or ALL to be specified. This is how Shareplex would also parse it. The correct syntax for the compare would be:
compare table SQM20.SQM_BB to SQM20.SQM_BB target us-rwc-ods1@o.ioq where " dial_start_time>=to_date('MAY
-19-2008','MON-dd-YYYY') and dial_start_time<=to_date('JUN-12-2008','MON-dd-YYYY') " repair port 2112