Call an Oracle Stored Procedure from .NET using OraDirect

scott.pedersen · Post by **scott.pedersen** » Tue 13 Jun 2006 23:18

Hi,

I am trying to call an oracle stored procedure that takes a number as a parameter. From the .NET code I do the following -

subscriberResults.CommandType = System.Data.CommandType.StoredProcedure;
subscriberResults.CommandText = "extend_billing.billing_results_pkg.create_all_subscriber_charges";
subscriberResults.Connection = oraConnection;
subscriberResults.Name = "createAllSubscriberCharges";
MessageBox.Show(billingPeriod.SelectedValue.ToString(), "Billing Period No.");
subscriberResults.Parameters.Add(new CoreLab.Oracle.OracleParameter("P_CURRENT_PERIOD_NO", CoreLab.Oracle.OracleDbType.Number, 0, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, ((OracleNumber)billingPeriod.SelectedValue.ToString()))));
oraConnection.Open();
subscriberResults.ExecuteNonQuery();
oraConnection.Close();

The problem that occurs is that Oracle cannot find the procedure being called, I suspect this is due to the parameter type that I am passing in not being correct.

What do I use for an Oracle Number in .NET?

Regards,

Scott.

Alexey · Post by **Alexey** » Wed 14 Jun 2006 06:42

Code: Select all

subscriberResults.CommandText = "extend_billing.billing_results_pkg.create_all_subscriber_charges";

What is extend_billing.billing_results_pkg.create_all_subscriber_charges? Why there are two dots in this statement?
What is billingPeriod?
What is the full text of the error?

scott.pedersen · Post by **scott.pedersen** » Wed 14 Jun 2006 08:56

Hi Alexey,

extend_billing is the schema, billing_results_pkg is the package and create_all_subscriber_charges is the procedure.

billingPeriod is a ComboBox with the value to pass into the stored procedure.

I will have to copy and paste the message into a reply tomorrow as I am not at work at the moment, but basically it is saying that the procedure create_all_subscriber_charges does not exist.

Cheers,

Scott.

Alexey · Post by **Alexey** » Wed 14 Jun 2006 09:39

We cannot reproduce the problem. Everything works fine with our test database objects.
Send us your project including definition of your own database objects. Do not use third party components.

scott.pedersen · Post by **scott.pedersen** » Thu 15 Jun 2006 02:40

Hi Alexey,

I have managed to get it to work by creating a test application and calling the create_all_subscriber_charges procedure. It was a copy and paste problem in the actual application, and what Oracle was telling me was correct that the stored procedure didn't exist, as I specified the wrong package.

The code I supplied was correct except for that small detail.

Cheers,

Scott.