Post Error:"Unexpected Oracle error: ORA-01741: illegal zero-length identifier" (4339973)

After new tables are added into the replication, and new configuration is activated. Post starts stopping due to error.

Post stopped due to "Unexpected Oracle error: ORA-01741: illegal zero-length identifier". In the event_log will show multiple columns without proper names.

Notice 2009-02-15 17:28:17.014789 754158 772 Poster: column "USERNAME"."TABLENAME"."`" num 1409287398, type 0, len 0 (posting from boss1, queue crm1, to sharedb) [module osp]
Notice 2009-02-15 17:28:17.014981 754158 772 Poster: column ""USERNAME"."TABLENAME"."" num 0, type 0, len 0 (posting from boss1, queue crm1, to sharedb) [module osp]
Notice 2009-02-15 17:28:17.015447 754158 772 Poster: column "USERNAME"."TABLENAME"."" num 0, type 0, len 0 (posting from boss1, queue crm1, to sharedb) [module osp]
Notice 2009-02-15 17:28:17.015600 754158 772 Poster: column "USERNAME"."TABLENAME"."`" num 1409287398, type 0, len 0 (posting from boss1, queue crm1, to sharedb) [module osp]
Notice 2009-02-15 17:28:17.015721 754158 772 Poster: column "USERNAME"."TABLENAME"."" num 0, type 0, len 0 (posting from boss1, queue crm1, to sharedb) [module osp]

When new tables are added to the replication, and activation of configuration is unsuccessfull few times. Messages of new objects are captured and forwarded to target, but target does not completely switch to use new post object cache yet. Therefore, when post uses the old object cache, it did not have any information that related to the new tables, and it would generated sql statement that did not have column name.

Set full debug for post process (Refer Sol25)

Start post

once post stopped due to error

Grep the current opo log for odr_actid

     >> it will show old and current actid intertwined

     For example:
     odr_actid 13
     odr_actid 17
     odr_actid 17
     odr_actid 13
     odr_actid 17

     Make copy of previous object cache .
     Copy the current object cache and rename it to previous object name .(Refer to Solution SOL2459 )

     Start post.

      Reset post debug.

If "grep the current opo log for odr_actid" only returns the latest <act_id>, then dump the object cache and compare with the table.

     a. get the path + the name of the latest object cache. For example /apps/quest/sp01/var/2429/state/0x9b400c1e+PP+vgcdp+sp_opst+o.vgcdp-o.odsup-objcache_sp_opst.86

     b. go to $SP_SYS_PRODDIR/util folder

     c. execute dumpfile and follow its prompt to dump object cache (4), entire cache (1), name the output file.

     d. searche and compare for the object with the output of sqlplus> desc <owner>.<object_name>

     *** must compare for both source and target sqlplus output.

     if they are different, must perform the following steps:

        SOURCE

         i. sp_ctrl>activate config <config_file>

         ii. sp_ctrl>show config

          >> get new config id

         iii. cd $SP_SYS_VARDIR/state

         iv. ls -ltr *objcache*<new config_id>

         v. copy the new *objcache*<new config_id> to TARGET

      TARGET.

         i. rename the existing *objcache*<previous config_id> to a backup

         ii. rename the new  *objcache*<new config_id> to the existing *objcache*<previous config_id>

         iii. Start post.

         iv. Reset post debug.

if they are all the same, please contact support for further help.

Sometimes post can throw this error message "Unexpected Oracle error: ORA-01036: illegal variable name/number." instead of "Unexpected Oracle error: ORA-01741: illegal zero-length identifier"