I'm trying to set up an Oracle publisher in SQL Server so I can perform replication. I've gotten connected to Oracle, the drivers work great (I have other linked servers), I can select an article I want to publish, and then I can get to the very end of the "Add Oracle Publisher" wizard.
However, when I click "Finish" and it attempts to actually add the article, I get the following error from the remote server:
OLE DB provider "OraOLEDB.ORACLE" for linked server "MYLINKEDSERVER" returned message "ORA-00936: missing expression ORA-06512: at "MYUSERNAME.HREPL", line 1722 ORA-06512: at line 1". Error: 7215, Sev: 17, State: 1, Msg: Could not execute statement on remote server 'MYLINKEDSERVER'.
I can't find this script anywhere (it seems like it's being generated by SQL Server and then executed) and I can't see what line 1722 actually contains, thought it's clear it contains a syntax error of some kind (at least from Oracle's perspective). Selecting a different table to publish gives me the exact same error on the exact same line. I can find a few posts online that reference this error (this is one of them), but they're all abandoned with no resolution.
I'm hopeful that somebody has set up successful Oracle -> SQL Server replication and can shed some light on the error I'm getting as I set it up.
Per a request in the comments, I scripted the steps and it fails on the final step of adding the actual article to the publication - this statement gives the error I listed above:
use [distribution] exec sp_addarticle @publication = N'Orcl_NativeToSQL_Publication', @article = N'TCUSTMER', @publisher = N'SNR', @source_owner = N'OGGATE', @source_object = N'TCUSTMER', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000083, @use_default_datatypes = 1, @destination_table = N'TCUSTMER', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_OGGATETCUSTMER', @del_cmd = N'CALL sp_MSdel_OGGATETCUSTMER', @upd_cmd = N'SCALL sp_MSupd_OGGATETCUSTMER' GO