Re: Custom Procedure Replication Error with Oracle Subscriber

Tech-Archive recommends: Fix windows errors by optimizing your registry

The reason we were drawn to the custom procedure is that we have long column
names > 30 characters in SQL Server and Oracle can only handle up to 30.
We are trying to handle the mapping in the custom stored procedure in Oracle
since there is no way to do this in SQL Server that we could come up with.

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:O$MTbRmwHHA.4076@xxxxxxxxxxxxxxxxxxxxxxx
Configure the publication to use SQL Statements instead of the stored
procedures. In the Publication Properties, Articles tab, Select the
properties of the article and Insert, Update and delete deliver formats
select insert statements.

--
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Meyer" <michael_meyer@xxxxxxxxxxxxxx> wrote in message
news:OSxakFlwHHA.484@xxxxxxxxxxxxxxxxxxxxxxx
The Oracle side is actually the subscriber. It does appear that it is
treating this as a table instead of a stored procedure. I can't see
anything wrong with the article. The article has the insert and update
commands defined as CALL stored procedures and the actual command names
are the correct stored procedure names.


"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:uIyD82kwHHA.600@xxxxxxxxxxxxxxxxxxxxxxx
Oracle publishers only replicate tables. It looks like here Oracle is
treating the stored procedure as a table, and is trying to return all
the data as opposed to only the columns (where 1=1).

Oracle publications do add the following objects on the Oracle server:

http://msdn2.microsoft.com/en-us/library/ms152557.aspx


--
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Meyer" <michael_meyer@xxxxxxxxxxxxxx> wrote in message
news:uoQ%232bjwHHA.4800@xxxxxxxxxxxxxxxxxxxxxxx
The custom procedure was created successfully on Oracle.

The following is the error message that is recieved. What is really
odd is why SQL Server is sending a select of a stored procedure name?
Any ideas as to what is going on here?

SQL> select * from sp_upd_crms_repl where 0 = 1;

select * from sp_upd_crms_repl where 0 = 1

*

ERROR at line 1:

ORA-04044: procedure, function, package, or type is not allowed here












.

Relevant Pages

  • Re: IOT, memory and transaction time
    ... easily generate it with DBMS_METADATA.GET_DDL if your verison of Oracle ... were pretty consistent no matter how big the table (this transaction ... FOREIGN KEY (versionNo) REFERENCES T_TRANSACTIONS, ... Oracle provides read consistency and SQL Server ...
    (comp.databases.oracle.misc)
  • Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec
    ... "Writers block readers and readers block writers in SQLServer. ... getting around this fundamental issue and because of it SQL Server ... admitted they can happen in Oracle, I guess Oracle should never been used ... isolation level but you will always disagree because you follow doctorine ...
    (comp.databases.oracle.server)
  • Re: Can you use the OleDb classes for SQL Server?
    ... No. Oracle uses different syntax, data flow and other methodologies in their ... that uses the OracleClient .NET data provider to access Oracle. ... Hitchhiker's Guide to Visual Studio and SQL Server ... I agree that it would be neat to just call a stored procedure for the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: IOT, memory and transaction time
    ... easily generate it with DBMS_METADATA.GET_DDL if your verison of Oracle ... If it was the update statement that references ... FOREIGN KEY (versionNo) REFERENCES T_TRANSACTIONS, ... There are however differences how both products deal with concurrency; in Oracle readers don't get blocked while this may happen in SQL Server. ...
    (comp.databases.oracle.misc)
  • RE: parameter name problem
    ... stored procedure positionally. ... if you have a proc with two parameters you can call it as: ... > sql server and in oracle it is a simple string, ...
    (microsoft.public.sqlserver.programming)