Simple Oracle query returns OracleCommand.CommandText is invalid-CodePudding

I have a SQL statement I try to run in C# and Oracle but I get the OracleCommand.CommandText error. My code creates an external table where data is loaded from a .tsv file and inserts it into my table CI_FT. Finally it drops the external table.

I don't see any reason why OracleCommand.CommandText would show.

The query is as follows:

CREATE TABLE STGMUAG.CI_FT_EXT
   (FT_ID CHAR(12) DEFAULT ' ' NOT NULL ENABLE, 
    SIBLING_ID CHAR(12) DEFAULT ' ' NOT NULL ENABLE
   )
ORGANIZATION EXTERNAL
( 
    DEFAULT DIRECTORY FLAT_FILES
    ACCESS PARAMETERS
    ( 
        records delimited by '\\r\\n'
        skip 1
        fields terminated by '\\t'
    )
    LOCATION('STGMUAG_CI_FT.tsv')  
);

INSERT INTO STGMUAG.CI_FT (
    FT_ID,
    SIBLING_ID
)
SELECT 
 FT_ID,
 SIBLING_ID
FROM STGMUAG.CI_FT_EXT;

DROP TABLE STGMUAG.CI_FT_EXT;

And here is my C# script

public void ExecNonQuery(string sqlStmt, OracleConnection con, ref string currentSql)
        {
            try
            {
                var sqlArr = sqlStmt.Split(';');
                foreach (string sql in sqlArr)
                {
                    currentSql = sql;
                    OracleCommand cmd = new OracleCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();

                    bool fireAgain = false;
                    Dts.Events.FireInformation(0, "PSRM Execute SQL", string.Format("SQL command {0} executed successfully.", sql), "", 0, ref fireAgain);
                }
            }
            catch (Exception e)
            {
                Dts.Events.FireError(0, "PSRM Execute SQL", "SQL command failed. "  e.Message, null,0);
                throw;
            }
        }

CodePudding user response：

You could do one of:

Remove the very final semicolon from the end of the SQL string
Call sqlStmt.Trim().Split(new[]{';'}, StringSplitOptions.RemoveEmptyEntries)
Put if(string.IsNullOrWhiteSpace(sql)) continue; on the first line of the loop

The latter 2 are a bit more code, but they will stop this error creeping back in if you accidentally typo a ;; into the string one day.. Only the third option protects against a typo of ; ;

I am having a hard time figuring out which sql causes the error

Side tip, also consider something like this, perhaps:

Dts.Events.FireError(0, "PSRM Execute SQL", "SQL command failed. "  e.Message 
  " the faulting SQL was:"   currentSql, null, 0);