Solved

How to call an oracle insert function

Posted on 2010-08-25
20
456 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Title # Comments Views Activity
Name Space error VS2015 1 36
C# Linq - Join two objects into one 3 33
I need help adding validation to my MVC.Net view 6 21
Build a string of emails from a gridview 2 15
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

749 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