Simple Oracle query returns OracleCommand.CommandText is invalid

Question

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;
            }
        }

Your code splits your script up into 3 statements. Which of the three statements is failing? I'm not sure why you'd be creating and dropping the table each time-- normally that's something that you'd just do once. — Justin Cave
– Justin Cave, Commented Nov 18, 2021 at 21:06
@CaiusJard where would I add the RemoveEmptyEntries? I want to check if adding that fixes it. It might be that the 4th statement, the empty string, could cause it. — doomdaam
– doomdaam, Commented Nov 18, 2021 at 21:12
I am having a hard time figuring out which sql causes the error. — doomdaam
– doomdaam, Commented Nov 18, 2021 at 21:13

Caius Jard · Accepted Answer · 2021-11-18 21:24:33Z

4

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);

edited Nov 18, 2021 at 21:24

answered Nov 18, 2021 at 21:16

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

doomdaam Over a year ago

Removing the semicolon did the trick. Thank you!

William Robertson Over a year ago

Good spot. Oracle SQL has no ; character.

Collectives™ on Stack Overflow

Simple Oracle query returns OracleCommand.CommandText is invalid

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related