Oracle vs Oracle ODBC

Question

The following code works fine from within Oracle's SqlPlus (using Oracle 11.2.02.0g) however when I connect with and ODBC connection via C# code, I get told I have an invalid character.

Since the single quote didn't work in SQLplus, I'm assuming the characters that are consider invalid by ODBC are the double quotes. I've tried braces '{' and brackets '[' but still get the same error -> ERROR [HY000][Oracle][ODBC][Ora]ORA-00911:invalid character <-

Any help would be much appreciated. I still don't understand why SQL statements would be interpreted differently because of the connection type.

CREATE USER "AD1\EGRYXU" IDENTIFIED EXTERNALLY;

Error if ran alone that states the username conflicts with another user or role name. It does create the user in the database.

C# Code is below.

private void button1_Click(object sender, EventArgs e)
        {
            string happy = "";
            string sql1 = "";
            string sql2 = "";
            string sql3 = "";
            string sql4 = "";
            string column;
            int rownum = -1;
            bool frst = false;
            string dirIni = "\\\\ramxtxss021-f01\\hou_common_013\\globaluser\\";
            string fileIni = "add_users.sql";
            string transIniFullFileName = Path.Combine(dirIni, fileIni);
            System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection();

            num_users = (usrdetails.Count > 0);
            if (regions && num_users)
            {
               using (StreamWriter sw = new StreamWriter(transIniFullFileName))
                {
                    for (int y = 0; y < usrdetails.Count; y++)
                    {
                        switch(usrdetails[y].add_del.ToUpper())
                        {
                            case "A":
                                  sql1 = "CREATE USER \"" + usrdetails[y].userID.ToUpper() + "\" IDENTIFIED EXTERNALLY;";
                                  sql2 = "GRANT EDMROLE TO \"" + usrdetails[y].userID.ToUpper() + "\";";
                                  sql3 = "INSERT INTO MD_SITE_USER VALUES(generate_key(5), (select user_id from MD_SITE_USER where user_name = '" +
                                         usrdetails[y].group + "') , {" + usrdetails[y].userID.ToUpper() + "}, " + usrdetails[y].seclev +
                                         ", '" + usrdetails[y].username.ToUpper() + "', 'U', '" + usrdetails[y].isext.ToUpper() + "', 'N');";
                                  sw.WriteLine(sql1);
                                  sw.WriteLine(sql2);
                                  sw.WriteLine(sql3);
                                  break;
                            case "D":
                                  sql2 = "DELETE MD_SITE_APP_ACTION_OWNER WHERE user_id in (SELECT user_id FROM MD_SITE_USER where user_name  = ‘"+ usrdetails[y].userID + "’+ and user_or_group = ‘U’);";
                                  sql3 = "DELETE FROM MD_SITE_USER where user_name  = ‘"+ usrdetails[y].userID + "’ and user_or_group = ‘U’;";
                                  sql4 = "DROP USER "+ usrdetails[y].userID + " FROM USERS;";
                                  sw.WriteLine(sql2);
                                  sw.WriteLine(sql3);
                                  sw.WriteLine(sql4);
                                  break;
                            default:
                                  MessageBox.Show("Add/Delete command argument not recognized for user\r\n" + usrdetails[y].userID + " \r\n Argument -> " + usrdetails[y].add_del);
                                  break;

                        }
                    }
                 sw.Close();
                }
                for (int x = 0; x < region.Count; x++)
                {
                    OdbcCommand command = new OdbcCommand();
                    conn.ConnectionString = "Driver={Oracle in OraClient11g_home1};" +
                                            "Dbq=" + region[x].dbname +
                                            ";Uid=" + region[x].username + ";Pwd=" + region[x].password + ";";
                    try
                    {
                        string cmdTexts = File.ReadAllText(transIniFullFileName);
                        conn.Open();
                        using (conn)
                        {
                            command.Connection = conn;
                            command.CommandText = cmdTexts;
                            command.ExecuteNonQuery();
                            OdbcDataReader dr = command.ExecuteReader();
                            Form6.dataGridView2.AutoGenerateColumns = false;
                            if (!frst)
                            {
                                for (int i = 0; i < dr.FieldCount; i++)
                                {
                                    column = dr.GetName(i);
                                    Form6.dataGridView2.Columns.Add("col" + i, column);
                                    Form6.dataGridView2.Columns[i].FillWeight = 1;
                                }
                                frst = true;
                            }
                            rownum++;
                            dataGridView1.Rows.Add();
                            dataGridView1.Rows[rownum].Cells[0].Value = "Results for Region -> " + Form5.region[x].dbname;
                            dataGridView1.Refresh();
                            while (dr.Read())
                            {
                                rownum++;
                                Form6.dataGridView2.Rows.Add();
                                for (int i = 0; i < dr.FieldCount; i++)
                                {
                                    column = dr.GetValue(i).ToString();
                                    Form6.dataGridView2.Rows[rownum].Cells[i].Value = column;
                                }
                            }
                            Form6.dataGridView2.Refresh();
                            Form6.dataGridView2.Show();
                            Form6.Show();
                        }
                       conn.Close();
                       Form6.dataGridView2.Refresh();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error Message: " + ex.Message);
                    }
                }
            }
            else
            {
                if (!regions)
                    happy = "Error - You have not selected any regions.\r\n";
                else
                    happy = "Regions are now selected.\r\n";
                if (!num_users)
                    happy = happy + "Error - You have not entered any users.\r\n";
                MessageBox.Show(happy);
            }
          File.Delete(transIniFullFileName);
        }

Answer 1

Don't use ";" (semi-colon) in the command text..

The command text within ODBC or ODP should be a command, e.g. not a set of commands, therefore - ";" is not relevant, and is an invalid character.

---

it appears you are trying to run a script.. if that is your intent, it should be padded with a "begin" and "end" for the code to be able to run:

BEGIN
 INSERT...;
 DELETE ...;
END;

(refer to http://www.intertech.com/Blog/executing-sql-scripts-with-oracle-odp/ for more info)

Last thing - if you want to run a "create user" (or any other DDL) from within an anonymous block or a procedure you need to run it with "execute immediate" syntax:

BEGIN
 execute immediate 'CREATE USER test IDENTIFIED EXTERNALLY';
END;