How to write parameterized oracle insert query?

Question

I am using oracle as my back end and i write insert query as follows, i don't know is it correct way are not,

insert into Emp_table (SL_NO,empane,empid,salaray) values(1,'sree',1002 ,:salary);

here in query i am calculating salary with stored procedure and getting as my out parameter salary so i has to pass that to my insert query so how can i write. if i write like as shown i am getting below errors

ORA-06550: line 1, column 175:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored

kindly help me.

James Lawruk · Accepted Answer · 2018-07-12 11:50:53Z

13

Microsoft deprecated their Oracle provider (System.Data.OracleClient) so use a third party provider such as Oracle's Data Provider for .NET. The code example below is essentially the same as FSX's answer, just without the convenience of the AddWithValue method.

command.Parameters.Add(new OracleParameter("SL_NO", 1));
command.Parameters.Add(new OracleParameter("empane", "sree"));

edited Jul 12, 2018 at 11:50

answered Sep 4, 2014 at 14:31

James Lawruk

31.5k19 gold badges135 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nishanth Shaan Over a year ago

Please note that you have written 'New OracleParameter' => 'n' shouldn't be in capital letter in the 'new' keyword.

James Lawruk Over a year ago

Thanks, Nishanth. Converted from VB to C#.

Furqan Safdar · Accepted Answer · 2012-10-10 05:56:36Z

10

Assuming salary amount is 20000, You can try this code:

var commandText = "insert into Emp_table (SL_NO,empane,empid,salaray) values(:SL_NO,:empane,:empid,:salary)";

using (OracleConnection connection = new OracleConnection(connectionString))
using (OracleCommand command = new OracleCommand(commandText, connection))
{
    command.Parameters.AddWithValue("SL_NO", 1);
    command.Parameters.AddWithValue("empane", "sree");
    command.Parameters.AddWithValue("empid", 1002);
    command.Parameters.AddWithValue("salaray", 20000);
    command.Connection.Open();
    command.ExecuteNonQuery();
    command.Connection.Close();
}

edited Oct 10, 2012 at 5:56

answered Oct 10, 2012 at 5:50

Furqan Safdar

16.8k13 gold badges62 silver badges98 bronze badges

Comments

Ashish Modi · Accepted Answer · 2016-02-29 07:26:50Z

2

   strin sql= "insert into Emp_table (SL_NO,empane,empid,salaray) values(:SL_NO,:empane,:empid,:salary)";
    OracleCommand command = new OracleCommand(sql, connection)
    command.Parameters.Add(new OracleParameter("SL_NO", 1);
    command.Parameters.Add(new OracleParameter("empane", "sree"));
    command.Parameters.Add(new OracleParameter(("empid", 1002));
    command.Parameters.Add(new OracleParameter(("salaray", 20000));
    command.Connection.Open();
    command.ExecuteNonQuery();
    command.Connection.Close();

answered Feb 29, 2016 at 7:26

Ashish Modi

211 bronze badge

1 Comment

user7157732 Over a year ago

I get error while adding Zero. For ex: ("salary",0).. Salary is defined as NUMBER in Oracle. @AshishModi

Collectives™ on Stack Overflow

How to write parameterized oracle insert query?

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related