Nintex Connect

truncate table TABLE1;
truncate table TABLE2;

This SQL code returns Error performing database operation. ORA-00911: invalid character.

By trial and error I've determined that the ; is causing the error.  Does this mean that if I need to truncate 10 tables then insert data into those same 10 tables I need 20 Execute SQL functions in my workflow?  Is this correct?

Hi Steve,

I am not familar with Oracle syntax, but is the semi colon how you would usually separate statements in oracle? The Execute SQL action doesn't do any processing of the statements (except replacing the context data), it just treats the whole text as one string and passes it to the database provider. It is the database provider that is reporting an error.

One possibility though, are your two commands 'hard coded' or are you using reference data to construct them?

regards

Jaems

Hi,

Thanks for the reply.  Yes, the semicolon is how you separate commands in Oracle SQL.  No, the commands are "hard coded," no references from the workflow.  Although the one command that does use workflow references works fine as it's only 1 SQL command in an Execute SQL box with no semicolon.  I've also noticed I cannot use the command "host" (which would allow me to call an executable like imp.exe or exp.exe allowing me to run imports or exports) or a "@" (which is used to start a line that calls a .sql file from the file system to process).  All-in-all, the Execute SQL does seem to process, or at least, parse and validate the SQL code, but I agree the error I posted does come from the database.

 The process I'm trying to build is for a refresh process from our development database to our production database.  I can get by without the "host" or "@" commands, it just makes my workflow longer, but if I can't put more than one command in the Execute SQL function then the workflow becomes so large as to be unmanageable/unusable.

James,

I've confirmed that a command like "truncate TABLE1;truncate TABLE2;" returned the invalid character error.

This suggests to me that the Execute SQL function is stripping out the CR/LF.