Solved

How to call an oracle insert function

Posted on 2010-08-25
20
446 Views
Last Modified: 2013-12-19
hi Experts,

I have this senario....

I have a gridview control with data such as
0001
0002
0003
etc

when users clicks on any of the Ids (0001, etc) they are hyperlinked to another page where this sample record is displayed on a gridview

0001 | John  | Date | Result

 the result and data columns are editable and so they can insert new dates and results and hit the save button.
On clicking the save button I would like to call this function

with this c# code

 OracleConnection con = new OracleConnection();
        con = new OracleConnection("Data Source=**; User ID***;Password=***");
        OracleCommand cmd = new OracleCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.CommandText = "he_entergrades";
        OracleParameter id = new OracleParameter("va_percentage", OracleType.Number);
        id.Direction = ParameterDirection.Input;
        OracleParameter Date = new OracleParameter("va_assessmententrydate", OracleType.Number);
        id.Direction = ParameterDirection.Input;    
        con.Open();    
        cmd.ExecuteNonQuery();

I am currently getting this error

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'HE_ENTERGRADES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

please how do fix this

thanks
CREATE OR REPLACE PROCEDURE HE_REPORTING_DATABASE.he_entergrades (va_id IN number, va_percentage in number, va_assessmententrydate in date)
AS 
BEGIN
    update unite.capd_assessment@utest
    set a_assessmententrydate=va_assessmententrydate,
    a_percentage=va_percentage
    where
    a_id=va_id;
    
    commit;

END;

Open in new window

0
Comment
Question by:SirReadAlot
  • 9
  • 4
  • 4
  • +1
20 Comments
 
LVL 14

Expert Comment

by:DragonSlayer
Comment Utility
Would

 OracleParameter Date = new OracleParameter("va_assessmententrydate", OracleType.Number);

work if replaced by

 OracleParameter Date = new OracleParameter("va_assessmententrydate", OracleType.Date);

?
0
 

Author Comment

by:SirReadAlot
Comment Utility
sorry===full code that errors
 OracleConnection con = new OracleConnection();
        con = new OracleConnection("Data Source=**; User ID***;Password=***");
        OracleCommand cmd = new OracleCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.CommandText = "he_entergrades";
OracleParameter perc = new OracleParameter("va_percentage", OracleType.Number);
        perc.Direction = ParameterDirection.Input;
        OracleParameter Date = new OracleParameter("va_assessmententrydate", OracleType.Number);
        Date.Direction = ParameterDirection.Input;
  con.Open();    
        cmd.ExecuteNonQuery();  
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
the definition has 3 params, you are only passing in 2 that  I can see

0
 

Author Comment

by:SirReadAlot
Comment Utility
no, still errored

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'HE_ENTERGRADES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
thats is, your procedure needs 2 numbers and a date, you are only sending 2 numbers
0
 
LVL 14

Expert Comment

by:DragonSlayer
Comment Utility
OK, upon further inspection, some other comments:
1. You created OracleConnection twice (you're lucky C# has GC, heh).
2. SP expects 3 parameters and you only entered 2

Here's something that might work:
OracleConnection con = new OracleConnection("user id=***;password=***");

con.Open();

OracleCommand cmd = new OracleCommand("he_entergrades", con);

cmd.CommandType = CommandType.StoredProcedure;

retval.Direction = ParameterDirection.Input;

OracleParameter inval = new OracleParameter("va_id", OracleType.Number);

inval.Direction = ParameterDirection.Input;  

inval.Value = 1; // or whatever id you want to update

cmd.Parameters.Add(inval);

OracleParameter inval = new OracleParameter("va_percentage", OracleType.Number);

inval.Direction = ParameterDirection.Input;  

inval.Value = 84; // or whatever percentage it is

cmd.Parameters.Add(inval);

OracleParameter inval = new OracleParameter("va_assessmententrydate", OracleType.Date);

inval.Direction = ParameterDirection.Input;  

inval.Value = DateTime.Today; // or whatever date

cmd.Parameters.Add(inval);

 

cmd.ExecuteNonQuery();

con.Close();

Open in new window

0
 
LVL 14

Expert Comment

by:DragonSlayer
Comment Utility
Ooops I took a bit too long to enter the code, so many other comments now, heh :-)
0
 

Author Comment

by:SirReadAlot
Comment Utility
@sdstuber...

didn't think i needed the 3rd one, but it still errors

cmd.CommandText = "he_entergrades";
        OracleParameter id = new OracleParameter("va_id", OracleType.Number);
        id.Direction = ParameterDirection.Input;
        OracleParameter perc = new OracleParameter("va_percentage", OracleType.Number);
        perc.Direction = ParameterDirection.Input;
        OracleParameter Date = new OracleParameter("va_assessmententrydate", OracleType.DateTime);
        Date.Direction = ParameterDirection.Input;
0
 

Author Comment

by:SirReadAlot
Comment Utility
hang on slayer!!
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
yes, you do need all 3 parameters for the procedure you have declared above

should "OracleType.DateTime"  be "OracleType.Data"   ?

also, is HE_REPORTING_DATABASE  as schema name or a package name?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:SirReadAlot
Comment Utility
HE_REPORTING_DATABASE is a schema
0
 

Author Comment

by:SirReadAlot
Comment Utility
Hi Slayer,
I have managed to do this with your  code

 OracleConnection con = new OracleConnection("Data Source=");
        con.Open();
        OracleCommand cmd = new OracleCommand("he_entergrades", con);
        cmd.CommandType = CommandType.StoredProcedure;

      //  retval.Direction = ParameterDirection.Input;
        OracleParameter inval = new OracleParameter("va_id", OracleType.Number);
        inval.Direction = ParameterDirection.Input;
        inval.Value = 15000028435389; // or whatever id you want to update
        cmd.Parameters.Add(inval);
        OracleParameter invalq = new OracleParameter("va_percentage", OracleType.Number);
        invalq.Direction = ParameterDirection.Input;
        invalq.Value = 23; // or whatever percentage it is
        cmd.Parameters.Add(invalq);
        OracleParameter invalw = new OracleParameter("va_assessmententrydate", OracleType.DateTime);
        invalw.Direction = ParameterDirection.Input;
        invalw.Value = DateTime.Today; // or whatever date
        cmd.Parameters.Add(invalw);
        cmd.ExecuteNonQuery();
        con.Close();


but nothing seems to be going into the database
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
I thought your previous questions were using ODP.Net?

Anyway, check out the following code using ODP.Net:
using System;

using System.Data;



using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;



public class Bob

{



	public static void Main(string[] args)

	{





			OracleConnection con = new OracleConnection();



			con = new OracleConnection("User Id=bud;Password=bud;Data Source=bud");



			OracleCommand cmd = new OracleCommand();

			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Connection = con;

			cmd.CommandText = " he_entergrades ";



			cmd.Parameters.Add("va_id", OracleDbType.Int64).Value = 25000028435389;

			cmd.Parameters.Add("va_percentage", OracleDbType.Int64).Value = 10;

			cmd.Parameters.Add("va_assessmententrydate", OracleDbType.Date).Value = Convert.ToDateTime("01/01/2010");



			con.Open();





			cmd.ExecuteNonQuery();



			con.Close();

			con.Dispose();



	}



}

Open in new window

0
 

Author Comment

by:SirReadAlot
Comment Utility
hey
>>I thought your previous questions were using ODP.Net?
had to revert to .net provider...


will check your code
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>had to revert to .net provider...

Why???  I would strongly suggest you use Oracle's provider for .Net.
0
 
LVL 14

Expert Comment

by:DragonSlayer
Comment Utility
Nothing goes into your database because your SP is an UPDATE, not an INSERT
0
 

Author Comment

by:SirReadAlot
Comment Utility
>>had to revert to .net provider...

Why???  I would strongly suggest you use Oracle's provider for .Net.

Had some problems and tight deadlines but will use it again.

Our dba thinks that there is an error with the function and will resolve it soon

0
 

Author Comment

by:SirReadAlot
Comment Utility
Hi slightwv,

I had to close this down due to the fact  I thought many had lost interest. I am still facing the same problem as described here....

http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_26446783.html
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I saw that question and it looked like a different question.

In the future the correct way to get renewed interest is to click the 'Request Attention' link at the top of the question instead of opening a duplicate one.

Duplicate questions tend to violate the Experts-Exchange rule of 500 points for a single question.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
The other question does appear to be unrelated this one.  It's about grid iteration
this one was about passing 2 values of the wrong type into a function that needed 3 values.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now