I want to drop procedures in a database on click in C#.

Sql File: EXP_DROP_PROCEDURES.sql

drop procedure EXP_NAMES;
drop procedure EXP_TYPS;
drop procedure EXP_CARS;
drop procedure EXP_SHIPS;

Code C#:

            try
            {
                // Geht die SQL Skript in Basei
                var statement = File.ReadAllText(AppDomain.CurrentDomain.BaseDirectory   "\\"   "sql"   "\\"   "EXP_DROP_PROCEDURES.sql");
                using (OracleCommand cmd = new OracleCommand(statement, oraCon))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }                  
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

I tried commit; at the end of EXP_DROP_PROCEDURES.sql and either without semicolons. Both did not work. It always brings me the exception ORA-00933 - SQL command not properly ended.

CodePudding user response：

In Oracle, one command can only contain one statement.

You either need to:

• read each line from the file and run those as separate commands (without the trailing semi-colon); or

• wrap the SQL statements in a PL/SQL anonymous block (and use EXECUTE IMMEDIATE since you cannot use DROP statements directly in PL/SQL):

  BEGIN
    EXECUTE IMMEDIATE 'DROP PROCEDURE EXP_NAMES';
    EXECUTE IMMEDIATE 'DROP PROCEDURE EXP_TYPS';
    EXECUTE IMMEDIATE 'DROP PROCEDURE EXP_CARS';
    EXECUTE IMMEDIATE 'DROP PROCEDURE EXP_SHIPS';
  END;