Call an Oracle Stored Procedure from .NET using OraDirect

Call an Oracle Stored Procedure from .NET using OraDirect

Postby 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.
scott.pedersen
 
Posts: 21
Joined: Tue 06 Jun 2006 03:14
Location: Auckland, New Zealand

Postby 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?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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.
scott.pedersen
 
Posts: 21
Joined: Tue 06 Jun 2006 03:14
Location: Auckland, New Zealand

Postby 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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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.
scott.pedersen
 
Posts: 21
Joined: Tue 06 Jun 2006 03:14
Location: Auckland, New Zealand


Return to dotConnect for Oracle