Click here to Skip to main content
12,628,919 members (30,659 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# Oracle C#4.0
I have done this for inserting procedure but not working for updating procedure

i tried this coding

type:
create type smp_emp_type as object
  (id number(3), name varchar2(30), gender varchar2(1)) NOT FINAL;

table:
create table smp_emp
(id number(3), name varchar2(30), gender varchar2(1)) ;

insert_procedure:
CREATE OR REPLACE procedure ins1 (emp IN smp_emp_type) as
 begin
    Insert into smp_emp  values (emp.id,emp.name,emp.gender);
end
 
CREATE OR REPLACE procedure upd1 (emp IN smp_emp_type) as
 begin
   update smp_emp e set e.name =emp.name,e.gender = emp.gender where id = emp.id;
end

both the procedures are created sucessfully
bt update is not working with my code:
update_pro updobj = new update_pro();
           updobj._Id = Convert.ToInt16(textBox1.Text);
           updobj._Name = textBox2.Text;
           updobj._Gender = textBox3.Text;
           OracleConnection conn = new OracleConnection("Data Source=r2;User ID=test;Password=test;");
           conn.Open();
 
           OracleCommand cmd = new OracleCommand("upd1", conn);
           OracleParameter emp_obj = new OracleParameter();
           emp_obj.OracleDbType = OracleDbType.Object;
           emp_obj.Direction = ParameterDirection.Input;
           emp_obj.UdtTypeName = "SMP_EMP_TYPE";
           emp_obj.Value = updobj;
           cmd.Parameters.Add(emp_obj);
           cmd.ExecuteNonQuery();

showing error message as ORA-00900: invalid SQL statement;

plz help me to solve this
Posted 20-Apr-12 2:46am
Updated 20-Apr-12 3:06am
v2
Comments
Sastry_kunapuli 20-Apr-12 8:51am
   
Did you check for any errors in the second stored procedure and try executing it in the PL/SQL developer/TOAD/SQL+ client?If there are any errors in the stored procedures it will give the error
Member 8814926 23-Apr-12 2:37am
   
I executed the procedure it showing error as
wrong number or type of arguments in call to upd1

can u plz help me to solve this error
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I think you forget this "e." before id = emp.id

CREATE OR REPLACE procedure upd1 (emp IN smp_emp_type) as
 begin
   update smp_emp e set e.name =emp.name,e.gender = emp.gender where e.id = emp.id;
end
  Permalink  
v3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

the correct answer is
OracleCommand cmd = new OracleCommand("upd1", conn);
cmd.CommandType = CommandType. StoredProcedure
OracleParameter emp_obj = new OracleParameter();
emp_obj.OracleDbType = OracleDbType.Object;
emp_obj.Direction = ParameterDirection.Input;
emp_obj.UdtTypeName = "SMP_EMP_TYPE";
emp_obj.Value = updobj;
cmd.Parameters.Add(emp_obj);
cmd.ExecuteNonQuery();
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161205.3 | Last Updated 20 Jun 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100