Solved

How to call an oracle insert function

Posted on 2010-08-25
20
451 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
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 73

Expert Comment

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

0
 

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 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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 76

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
c# string handling 1 27
Runtime Exceptions when trying to submit data 28 36
How would you add MULTITHREADING to the attached C# code? 4 51
Error in script 11 45
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…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

911 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

21 Experts available now in Live!

Get 1:1 Help Now