Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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 1:46am
Edited 20-Apr-12 2:06am
v2
Comments
Sastry_kunapuli at 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 at 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
0 Dnyaneshwar@Pune 604
1 OriginalGriff 394
2 thatraja 370
3 Sergey Alexandrovich Kryukov 334
4 CPallini 219
0 OriginalGriff 697
1 Dnyaneshwar@Pune 604
2 Kornfeld Eliyahu Peter 495
3 thatraja 370
4 Sergey Alexandrovich Kryukov 344


Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 20 Jun 2013
Copyright © CodeProject, 1999-2014
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