locked
Can not call Oracle Stored procedures when using Oracle on server(Emergency) RRS feed

  • Question

  •  

    Hi

    I am newbie and try to use Microsoft Synchronization Services for ADO.NET in my project.

    Server side uses Oracle and I wish when data change on client side, server can update with procdure.

     

    The procedure like belowSadTo make the question simpler, only give least parameters)

    CREATE OR REPLACE PROCEDURE sp_update_country
    ( CountryID IN varchar,
    CountryCode IN varchar,
    CountryName IN varchar
    )
    as
    BEGIN

    update country set countrycode = sp_update_country.countrycode , countryname = sp_update_country.CountryName
    where countryid = sp_update_country.CountryID;
    commit;

    END;

     

    Test 1

    The code for SyncAdaptor is

    OracleCommand updCmd = new OracleCommand();

    updCmd.CommandType = CommandType.StoredProcedure;

    updCmd.CommandText = "sp_insert_country";

    updCmd.Parameters.Add("countryid", OracleType.VarChar);

    updCmd.Parameters.Add("countryname", OracleType.VarChar);

    updCmd.Parameters.Add("countrycode", OracleType.VarChar);

     

    The error will be

    Unable to set session parameters in DbServerSyncProvider. Cannot obtain the value for command parameter 'countryid'.

    Test 2

    The code for SyncAdaptor is

    OracleCommand updCmd = new OracleCommand();

    updCmd.CommandType = CommandType.StoredProcedure;

    updCmd.CommandText = "sp_insert_country";

    updCmd.Parameters.Add("@countryid", OracleType.VarChar);

    updCmd.Parameters.Add("@countryname", OracleType.VarChar);

    updCmd.Parameters.Add("@countrycode", OracleType.VarChar);

    _syncAdaptor.UpdateCommand = updCmd;

    The error will be

    ORA-01036: illegal variable name/number

     

    ++++++++++++++

    It seems that Oracle does not support the format "@countryid"

    And "@countryid" is  required here

    So I can not go on now.

     

    Can anybody help me?

    Thanks a lot

    • Moved by Max Wang_1983 Friday, April 22, 2011 8:22 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, February 15, 2008 9:38 AM

All replies

  •  

    Hi Paul:

     

    can you try

     

    updCmd.CommandText = "sp_insert_country";

    updCmd.Parameters.Add(":countryid", OracleType.VarChar);

    updCmd.Parameters.Add(":countryname", OracleType.VarChar);

    updCmd.Parameters.Add(":countrycode", OracleType.VarChar);

     

     

    thanks

    Yunwen

    Saturday, February 16, 2008 12:53 AM
    Moderator
  • Thank you YunWen

    Do you mean this?

    Test 1

    The code for SyncAdaptor is

    OracleCommand updCmd = new OracleCommand();

    updCmd.CommandType = CommandType.StoredProcedure;

    updCmd.CommandText = "sp_insert_country";

    updCmd.Parameters.Add("countryid", OracleType.VarChar);

    updCmd.Parameters.Add("countryname", OracleType.VarChar);

    updCmd.Parameters.Add("countrycode", OracleType.VarChar);

     

    The error will be

    Unable to set session parameters in DbServerSyncProvider. Cannot obtain the value for command parameter 'countryid'.

    Saturday, February 16, 2008 3:46 PM
  • Can anybody help me?
    Thanks a lot
    Wednesday, February 20, 2008 11:13 AM
  • Sorry Paul for not replying in time.

     

    You need to add a ":" as the prefix of the parameter name in order to work with Oracle db server.

     

    updCmd.CommandText = "sp_insert_country";

    updCmd.Parameters.Add(":countryid", OracleType.VarChar);

    updCmd.Parameters.Add(":countryname", OracleType.VarChar);

    updCmd.Parameters.Add(":countrycode", OracleType.VarChar);

     

     

    Thanks

    Yunwen

    Saturday, February 23, 2008 5:54 PM
    Moderator
  •  

    updCmd.CommandText = "sp_insert_country";

    updCmd.Parameters.Add("countryid", OracleType.VarChar).value = "22" ;

    updCmd.Parameters.Add("countryname", OracleType.VarChar).value = "Palestine" ;

    updCmd.Parameters.Add("countrycode", OracleType.VarChar).value = "972 ;

     

    i thing thats you need

    Monday, March 17, 2008 8:42 AM
  • You forgot the colon ":" in front of the variable names instead of "@".  Can you try that out and let us know if it corrects the issue.

     

    Sean Kelley

    Program Manager

    Microsoft

     

     

     

    Monday, March 17, 2008 3:08 PM
    Moderator