Forum Stats

  • 3,750,785 Users
  • 2,250,246 Discussions
  • 7,867,140 Comments

Discussions

Calling Stored Procedures over a DBLink from .NET

420571
420571 Member Posts: 12
edited May 17, 2004 4:01PM in Oracle Provider for OLE DB
Hello,

Does anyone have any experience with calling a stored procedure in a remote db using MS Ole DB Provider for Oracle from a .NET app?

The following illustrates my objectives:

1. .NET client app has access rights to DBINST1
2. A public DBLink exists between DBINST1 and DBINST2
3. Using an OracleCommand object call a stored procedure that resides in DBINST2 spFoo(param1);

When I try doing this I get errors like object does not exist in DBINST1. I've tried to create a synonym for the SP in DBINST1 but I still get the same error.

One thing I've noticed is that if call a remote SP that does not take any parameters, the call succeeds. If I add parameters to the SP, the call fails.

I can, however, run the SP from SqlPlus when connected to DBINST1. I can also do a describe on it. But when I try from the .NET environment, I get an exception.

I've also tried to specify the qualified string ([email protected]) but this also fails.

Any suggestions on this would be greatly appreciated.

Thanks,
Arif
«13

Comments

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    1) Have you tried using the Oracle OLE DB provider?
    2) What exception do you get?
    3) Can you try creating a synonym on DBINST1 for the procedure on DBINST2?

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC
  • 420571
    420571 Member Posts: 12
    Hi Justin,

    I am new to this so please bear with me.

    1. Do you mean the ODP.Net driver? If so, then I did not try it.

    2. I don't have the exact test, however, the error message said: object does not exist in DBINST1. I will post the exact body of the error message asap.

    3. I did create a synonym to the SP on DBINST2 and I could successfully describe and execute the SP from SQLPlus but not through the .NET code.

    Thanks,
    Arif
  • 15208
    15208 Member Posts: 351
    Arif,
    Have you tried this way when there are parameters?

    [email protected](parameter1, parameter2, ...)

    Sinclair
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    There is an Oracle OLE DB Provider and a Microsoft OLE DB Provider for Oracle. If you want to use OLE DB, I would suggest that you try the Oracle provider rather than the Microsoft provider. If you're using .NET, I would generally tend to prefer ODB.Net over OLE DB.

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC
  • 420571
    420571 Member Posts: 12
    Here is what I've done so far. I've altered some details for security reasons:

    The steps I followed were:
    1. create dblink from DBINST1 logged in as SYSTEM.
    2. create a stored proc in DBINST2
    3. create a synonym for stored proc in DBINST1
    4. In SQLPlus logged in as myuser and issue describe on AD_TEST.
    5. Execute C# code below.

    The DBLink was created as follows:
    -- Create database link
    create public database link DBINST2.MYDOMAIN.COM
    using 'DBINST2;

    Then a stored procedure in DBINST2 was created:
    create or replace procedure AD_test_2(a number) is

    b INTEGER := 0;
    begin
    if a is not null then
    null;
    end if;
    select 'xyz' into b from dual;
    end AD_test_2;

    In DBINST1:
    A synonym was created as follows:
    Create synonym AD_test_2 for [email protected]

    The .NET code is:
    try
    {


    OleDbConnection conn = new OleDbConnection("Provider=MSDAORA;Data Source=DBINST1;User ID=myuser;Password=myuser");
    conn.Open();
    OleDbTransaction tx = conn.BeginTransaction();

    //Prepare command and params
    OleDbCommand cmd = new OleDbCommand("ad_test",conn, tx);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@a", OleDbType.Integer).Value = 6;
    cmd.ExecuteNonQuery();


    }
    catch (Exception ex)
    {
    throw ex;
    }





    When I run the above code I get this error during debug:
    {"ORA-06550: line 1, column 7:\nPLS-00201: identifier 'AD_TEST' must be declared\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored"}
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    In this example, you're creating a procedure "ad_test_2" and trying to call "ad_test", so you would expect the error you are getting.

    If your real code has the same procedure names everywhere, can you try using the Oracle OLE DB Provider rather than the Microsoft OLE DB Provider for Oracle?

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC
  • 420571
    420571 Member Posts: 12
    I'm sorry this is a typo during the edit to remove company specific info. Rest assured I am calling the right stored procedure.

    Again my apologies.
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    Can you try using the Oracle OLE DB provider?

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC
  • 420571
    420571 Member Posts: 12
    Ok so here's what I've discovered:
    The Oracle OleDB provider works fine. However, part of the infrastructure code derives the parameters using:

    System.Data.OleDb.OleDbCommandBuilder.DeriveParameters (
    (System.Data.OleDb.OleDbCommand) cmd );

    Which results in the following error:
    {"The stored procedure 'ad_test' doesn't exist." }

    Below is the body of the code:

    System.Data.OleDb.OleDbConnection con = new OleDbConnection(ConnectionString);

    try
    {
    con.Open();
    OleDbTransaction tx = con.BeginTransaction();
    //Prepare command and params
    OleDbCommand cmd = new OleDbCommand("ad_test",con, tx);
    cmd.CommandType = CommandType.StoredProcedure;

    //Derive the sp params - THIS LINE CAUSES AN EXCEPTION TO BE THROWN
    System.Data.OleDb.OleDbCommandBuilder.DeriveParameters((System.Data.OleDb.OleDbCommand) cmd );


    //cmd.Parameters.Add("@a", OracleType.Number).Value=104;

    cmd.ExecuteNonQuery();
    }
    catch ( Exception ex)
    {
    throw ex;
    }


    Thanks,
    Arif
  • 15208
    15208 Member Posts: 351
    Arif,
    DeriveParameters is not supported by OraOLEDB. You may use procedure_parameters schema rowset to determine the parameter types for a stored procedure.

    Sinclair
This discussion has been closed.