Search code examples
oraclesymmetricds

SymmetricDS: Can I call a stored procedure using initial.load.before.sql

I can get "initial.load.before.sql" in symmetric-server.properties to send a simple SQL statement to a receiving node. But the documentation on what is allowed as a value for this parameter is pretty slim.

This works:

initial.load.before.sql=ALTER TABLE FOO.BAR disable CONSTRAINT BAR_PTY_FK

But what I would like to do is have SymmetricDS (SDS) execute a stored procedure on the receiving node prior to initial load that disables all the foreign key constraints (there are a lot).

I tried chaining a couple statements in "initial.load.before.sql". This works too:

initial.load.before.sql=ALTER TABLE FOO.BAR disable CONSTRAINT FOO_PTY_FK;ALTER TABLE FOO.BAR disable CONSTRAINT FOO_CTP_FK

But, if I include all the FK's I need, the "initial.load.before.sql" line will be pretty long.

I tried something like this:

initial.load.before.sql=FOO.MY_STORED_PROCEDURE()

But that value results in this error on the sending node:

2020-10-30 20:34:50,363 ERROR [sds-foo] [ManageIncomingBatchListener] [sds-foo-dataloader-2] Failed to load batch sds-master-1594 StackTraceKey.init [SqlException:2002564836] org.jumpmind.db.sql.SqlException: ORA-00900: invalid SQL statement

Not sure if stored procedure are totally out or if I just need to keep tweaking the value I am providing for "initial.load.before.sql". I experimented with different types of DML SQL statements (INSERT, UPDATE, DELETE) and those work well. ALTER TABLE works fine too (as long as the SDS Oracle account has the required privileges).

If it helps w/ the answer, I am using Oracle and the SDS user db account on the receiving node has the required privileges to execute the store procedure. I can sucessfully execute the stored procedure as the SDS user (outside the scope the SDS software . . . . i.e. using a db client tool such as SQLDeveloper). The version of SDS is 3.12.3.

Solution

  • To call a stored procedure, use a "call" statement, like this:

    initial.load.before.sql=call FOO.MY_STORED_PROCEDURE()

    If you are interested in deferring indexes, constraints, and foreign keys automatically during the initial load, take a look at parameters for auto.create=true and initial.load.defer.create.constraints=true.