Solved

How to call an oracle insert function

Posted on 2010-08-25
20
458 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 4
  • +1
20 Comments
 
LVL 14

Expert Comment

by:DragonSlayer
ID: 33522204
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
ID: 33522212
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 74

Expert Comment

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

0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:SirReadAlot
ID: 33522236
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 74

Expert Comment

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

Expert Comment

by:DragonSlayer
ID: 33522335
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
ID: 33522346
Ooops I took a bit too long to enter the code, so many other comments now, heh :-)
0
 

Author Comment

by:SirReadAlot
ID: 33522354
@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
ID: 33522369
hang on slayer!!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 33522463
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
 

Author Comment

by:SirReadAlot
ID: 33522508
HE_REPORTING_DATABASE is a schema
0
 

Author Comment

by:SirReadAlot
ID: 33522534
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 33522563
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
ID: 33522594
hey
>>I thought your previous questions were using ODP.Net?
had to revert to .net provider...


will check your code
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33522626
>>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
ID: 33522650
Nothing goes into your database because your SP is an UPDATE, not an INSERT
0
 

Author Comment

by:SirReadAlot
ID: 33522792
>>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
ID: 33585885
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33586082
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 74

Expert Comment

by:sdstuber
ID: 33586147
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

734 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