Using "Run SQL File on Oracle" action returns error


Article ID: 128438


Updated On:


CA Release Automation - Release Operations Center (Nolio) CA Release Automation - DataManagement Server (Nolio)


Error executing: ...
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement 


It comes down to the nuances of various sql clients (ex: sqlplus, sqldeveloper, toad, others). Each of these clients have certain settings which are used by default and lead to a specific user experience. The actions we provide don't specifically try and match any of these these. There are some options that we make available that might make your experience similar. However, all settings that you can set (or are set by default) for the various sql clients may not be settable through the actions available configuration fields. 


CA Release Automation v6.6.
Action Name: Run SQL File on Oracle(c) Database Server


The action is okay for running simple statements. Once you start getting into more advanced scripts (for example: nested begin blocks, calling procedures, etc..) then it is recommended to execute your .sql scripts using the "Run Command Line" action configured to call the scripting with your preferred sql client.

The following points are worth nothing while using third party sql clients (for example: sqlplus) to execute your sql scripts via CARA:
  1. Many of these sql clients (for example: sqlplus) have the ability to execute sql scripts against remote servers. So you don't need an agent on every db server where you want to run your sql scripts. 
  2. It is recommended to create a Server Type that represents the sql client system where you will run the scripts. 

Additional Information

Related URLs:
Idea/Enhancement: SQL actions should be enhanced to execute PL/SQL procedures
How to run PL/SQL script using action