remote sql script from jenkins

Question

I am suppose to run an sql script which is in a remote box the manual steps are as below after connecting to the box with userid and password
Then sudo su – fa3dev

account Password:*******

sqlplus / as sysdba

connect XX_fusion_custom

password *****

@abc.sql
When I run this manually all works fine When I run from jenkins as a remote shell script as below

ssh fa3dev@server 'source .bash_profile; sqlplus xx_fusion_custom/password'

In console it gives Sql connected and disconnected

if I run ssh fa3dev@server 'source .bash_profile; sqlplus xx_fusion_custom/password;sqlplus / as sysdba;connect XX_fusion_custom;select user from dual' it gives syntax error near unexpected token `from' in jenkins console , i guess I am not able to connect to the database , any help in this would really be appriciable, I cannot user SQL plugin as I cant install oracle cleint in the jenkins box

Stack Overflow is a site for programming and development questions. This question appears to be off-topic because it is not about programming or development. See What topics can I ask about here in the Help Center. Perhaps Super User or Unix & Linux Stack Exchange would be a better place to ask. Also see Where do I post questions about Dev Ops? — jww

berserck berserck · Accepted Answer · 2017-03-02T08:59:37

your problem is in the bash, the sql command select user from dual is being executed after sqlplus exits and the bash picks it up.

You can fix this in 2 ways:

echo into the sqlplus:

ssh fa3dev@server 'source .bash_profile; echo select user from dual | sqlplus xx_fusion_custom/password;sqlplus / as sysdba;connect XX_fusion_custom;'
Pass a file with the command (this is nice if the SQL commands are many and fixed)

2.1 Remote file

ssh fa3dev@server 'source .bash_profile; sqlplus xx_fusion_custom/password;sqlplus / as sysdba;connect XX_fusion_custom < files_with_commands.sql'

2.2 Local file (note the position of the closing ' changed from after thehfilename to before the filename):

ssh fa3dev@server 'source .bash_profile; sqlplus xx_fusion_custom/password;sqlplus / as sysdba;connect XX_fusion_custom < files_with_commands.sql'

remote sql script from jenkins

1 Answers