Using "Run SQL File on Oracle" action returns error

book

Article ID: 128438

calendar_today

Updated On:

Products

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

Issue/Introduction

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

Cause

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. 

Environment

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

Resolution

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