Executing Existing Stored Procedure

7 posts, 1 answers
  1. Marc
    Marc avatar
    5 posts
    Member since:
    May 2014

    Posted 15 Jul 2014 Link to this post

    I'm trying to understand how to execute a stored procedure, but since we are using the Fluent model I cannot use some of the tools.  I can execute the stored procedure, but the response is that the number or type of the parameters is incorrect.  Is there a way to specify type for the parameters?  I attempted to specify DbType for the OAparameter, but that was unsuccessful.  I specifically have a procedure with 3 string parameters and one NUMERIC(6,0) type parameter for an Oracle database.  Any example documentation specifically for the Fluent model (not Domain GUI instructions) would be helpful.
  2. Answer
    Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 17 Jul 2014 Link to this post

    Hi Marc,
    In the following, you can find an example that uses two out parameters for the procedure.
    The procedure itself for this example is defined as this:
    create or replace PROCEDURE ExampleProcedure(p0 out number, p1 out varchar2) AS
    begin 
        Select COUNT(*) into p0 from "category";
        p1 := 'Test';
    end;

    To execute this procedure, you can use the following code.
    OAParameter param1 = new OAParameter();
    param1.Direction = ParameterDirection.Output;
    param1.Size = 10;
    param1.ParameterName = ":p0";
     
    OAParameter param2 = new OAParameter();
    param2.Direction = ParameterDirection.Output;
    param2.Size = 10;
    param2.ParameterName = ":p1";
     
    using (var connection = this.Context.Connection)
    {
        using (OACommand command = connection.CreateCommand())
        {
            command.Parameters.Add(param1);
            command.Parameters.Add(param2);
            command.CommandText = "ExampleProcedure";
            command.CommandType = CommandType.StoredProcedure;
     
            command.ExecuteNonQuery();     
            Assert.AreEqual(123, Int32.Parse(param1.Value.ToString()));
            Assert.AreEqual("Test", param2.Value);
        }
    }

    You can find more details about this API here. This approach is basically not bound to the Fluent API rather than it is the Low Level ADO API that is also generated in case you are using the Visual Designer to map your Procedures. Though if you are working without the Visual Designer, you have to implement the respective calls by yourself.

    I hope this information is helpful for you.
    Do come back in case you need further assistance.

    Regards,
    Ralph Waldenmaier
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
  4. Marc
    Marc avatar
    5 posts
    Member since:
    May 2014

    Posted 17 Jul 2014 in reply to Ralph Waldenmaier Link to this post

    That might be helpful.  One specific question I have is the instructions specify, "The name of the parameter should match the name of the corresponding stored procedure parameter in the database."  In your example the parameter names are ":p0" and ":p1", so a colon has been added.  Is this an additional requirement?  In my testing I was not matching the names of the parameters, so I'll change that next.
  5. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 17 Jul 2014 Link to this post

    Hi Marc,
    Yes the colon is necessary when binding parameters by name in Oracle. Additionally. you might find this link helpful.

    Feel free to ask in case you have any other question.

    Regards,
    Ralph Waldenmaier
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  6. Marc
    Marc avatar
    5 posts
    Member since:
    May 2014

    Posted 17 Jul 2014 in reply to Ralph Waldenmaier Link to this post

    Here is an example of the code I'm attempting to use.  I keep getting an exception with the following error "
    PLS-00306: wrong number or types of arguments in call to 'ORA_PROCEDURE_NAME' ".  The number of arguments is correct, unless an OUT argument is required that I'm not aware of.  The only other issue that comes to mind is that para1 is NUMERIC, and there is a type issue.  I've tried specifying para1.DbType = System.Data.DbType.VarNumeric;, but that didn't work either.

    ORA Procedure Example:

    CREATE PROCEDURE msdgen.procedure (
    para1 NUMERIC,
    para2 VARCHAR2,
    para3 VARCHAR2,
    para4 VARCHAR2)


    C# Code
        int para1Variable = 1;
    string para2Variable = "Test 2";

    para1.ParameterName = ":para1";
    //materialIDParameter.DbType = System.Data.DbType.VarNumeric;
    para1.Value = para1Variable;
    para2.ParameterName = ":para2";
    para2.Value = para2Variable;
    para3.ParameterName = ":para3";
    para3.Value = "Test 3";
    para4.ParameterName = ":para4";
    para4.Value = "Test 4";

    using (var connection = this.context.Connection)
    {
    using (OACommand command = connection.CreateCommand())
    {
    command.Parameters.Add(para1);
    command.Parameters.Add(para2);
    command.Parameters.Add(para3);
    command.Parameters.Add(para4);
    command.CommandText = "ora_procedure_name";
    command.CommandType = CommandType.StoredProcedure;

    command.ExecuteNonQuery();
    }
    }
  7. Marc
    Marc avatar
    5 posts
    Member since:
    May 2014

    Posted 17 Jul 2014 in reply to Marc Link to this post

    I generalized the code in my previous example, and I mistakenly didn't change the ORA procedure's name to match the CommandText.  They do match in the code I'm running.
  8. Marc
    Marc avatar
    5 posts
    Member since:
    May 2014

    Posted 17 Jul 2014 in reply to Marc Link to this post

    I got it to work.  It was a combination of issues related to parameter names, etc.  Thanks.
Back to Top
DevCraft banner