CODEWITHSUNDEEP
oracle-databasejenkinsssh
Subrata Fouzdar
Subrata Fouzdar

Reputation: 733

remote sql script from jenkins

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

Upvotes: 0

Views: 2359

Answers (1)

berserck
berserck

Reputation: 500

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:

  1. 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;'

  2. 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'

Upvotes: 1

Related Questions