OracleScript Question

OracleScript Question

Postby joet919 » Fri 22 Oct 2010 16:19

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?
joet919
 
Posts: 5
Joined: Mon 10 Sep 2007 18:17

Postby Shalex » Mon 25 Oct 2010 15:41

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();
            }
Shalex
Devart Team
 
Posts: 7831
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle