ORA-00933: SQL command not properly ended

Help Studio
1

Hello All,

Please can anyone help me with the problem “ORA-00933: SQL command not properly ended” which sometimes appears to me. I have a row update in the database:

“UPDATE “+StrDbProcessName+” SET STR_STATUS = '”+StrStatus+“', INT_ATTEMPS = “+IntRetriesPerform.ToString+” , STR_TIME = TO_CHAR(CAST(SYSDATE as TIMESTAMP) - CAST(DAT_KDY_START as TIMESTAMP),‘HH24:MI:SS’), DAT_KDY_END = SYSDATE, STR_NEW_ID_DOCUMENT = '”+StrNewIdDocument+“'” + " WHERE ID_ZDRAVI_LOGS = ‘“+DtItem.Rows(0).Item(“ID_ZDRAVI_LOGS”).ToString+”’"

It works for me in 99%, but sometimes the mentioned error turns out. Don’t know what could be wrong?

Thanks! John

2

Hi @jan.exner
This is most likely due to one of the variables being blank, incorrect or containing escape characters which break the query.
For example,
If a string value has a double-quote ( " ) or single quote ( ’ ) characters, it may be breaking the query.
I recommend two things:

  1. Debug the query with your erroneous test data (or in case you’re not sure when it happens, use log message to print the query string) so that you can inspect the final query string that is fired.
  2. Use the Parameters property of query editor instead of the string variable that you’re using.
    With this, you query should look like:
    "UPDATE " + StrDbProcessName +
    " SET STR_STATUS = @StrStatus,
    SET INT_ATTEMPTS = @IntAttempts,
    … and so on.
    Pass the dynamic values through parameters that are defined as StrStatus, IntAttempts, etc.
    This minimises query changes in case you decide to pass a different value to the query (and also good for generalizing the code which you seem to be doing already (dynamically passed table name)

I am not sure about the tablename being passed as a parameters, because I haven’t used it. It makes logical sense that you should be able to pass the tablename too. Please try this approach and let us know in case of any issues.

Regards.

3

Hi @RPAForEveryone

The behavior of that error is just special in that once a given “UPDATE” makes a mistake and when I start the robot again under exactly the same conditions, it passes.

I tried to eliminate the gaps between the variables and so far it has been running without falling for two days.

If it fell again, I would write for advice again :)!

Thanks so far!

1 Like