Executing Existing Stored Procedure

[Marc]

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.

[Ralph Waldenmaier]

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

 

 

[Marc]

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.

[Ralph Waldenmaier]

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

 

 

[Marc]

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();
}
}

[Marc]

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.

[Marc]

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