Click here to Skip to main content
15,893,904 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I have done this for inserting procedure but not working for updating procedure

i tried this coding

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


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


insert_procedure:
SQL
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:
SQL
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
Updated 20-Apr-12 2:06am
v2
Comments
S@53K^S 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
kevin_ze 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

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

SQL
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
 
Share this answer
 
v3
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();
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900