OracleScript Question

[joet919]

I need to pass a couple of parmeters to different SQL code I want to execute inside of my ORacleScript. Is there an easy way to do this?

[Shalex]

If you are working with parameters, we recommend you using the OracleCommand class:
1) with PL/SQL block in CommandText -

Code: Select all

            using (OracleConnection conn = new OracleConnection()) {
                conn.ConnectionString = "server=ora112;uid=scott;pwd=tiger;";
                conn.Open();
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = "begin" +
                    "insert into dept values (111,:p1,'London');" +
                    "insert into dept values (112,:p1,'San Francisco');" +
                    "end;";
                cmd.Parameters.Add("p1", "Development");
                cmd.ExecuteNonQuery();
            }

2) if you cannot use PL/SQL because of its limitations (e.g., DDL statements cannot be used in PL/SQL), please create the parameter collection for your command and execute command several times (with changed CommandText):

Code: Select all

            using (OracleConnection conn = new OracleConnection()) {
                conn.ConnectionString = "server=ora112;uid=scott;pwd=tiger;";
                conn.Open();
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = "insert into dept values (111,:p1,'London')";
                cmd.Parameters.Add("p1", "Development");
                cmd.ExecuteNonQuery();
                cmd.CommandText = "insert into dept values (112,:p1,'San Francisco')";
                cmd.ExecuteNonQuery();
            }