Running SQL scripts in chef recipe - correct way to execute

Chef Infra (archive)
1

Hi, I need to run sql scripts ( create an instance , create spfile from pfile, create a pluggable database etc)

  1. Sid creation got successful - this is run as a direct batch script execution
execute 'create_instance_sid' do
  command "C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\oradim.exe -NEW -SID orclcdb -STARTMODE MANUAL -PFILE \'C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\database\\initorclcdb.ora\'"
  only_if {File.exist?("#{node['oracle']['oracle_home']}")}	
end
  1. creation of spfile is not successful, no error is logged and seems it is not executed in the back end and the sqlplus is not invoked.
execute 'create_spfile' do
  environment ({"ORACLE_SID" => "orclcdb"})
  command "echo CREATE SPFILE='C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\database\\spfileorclcdb.ora' FROM PFILE='C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\database\\initorclcdb.ora'; | C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\sqlplus -s sys/MyPassword_12@orclcdb as sysdba"
  not_if  {File.exists?('C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\database\\spfileorclcdb.ora')}
  timeout 1800
end
  1. this script is giving a script error. Can some one please give a suggestion on how to run sql scripts in chef recipes. There is not good examples on the syntax in anywhere
batch 'Create_SPFile_Batch' do 
code <<-EOH
    sqlplus sys/MyPassword_12<<-SQLEOH
    CREATE SPFILE="C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\database\\spfileorclcdb.ora" FROM PFILE="C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\database\\initorclcdb.ora"; 
    exit;
    SQLEOH
  EOH
cwd "C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\"
end

Thank you

2

Did you mean for that echo to be in there?

command "echo CREATE

3

Hey @chefstudent

Running sql scripts directly from a chef recipe should work fine, using the execute resource, assuming you are careful about whitespace and quoting and the normal types of execute cli concerns. You will definitely want a guard on any of those resources for idempotency. Personally I tend to avoid using batch, execute or script type resources wherever I can.

If I needed to do this I would avoid executing the scripts directly and would instead make my chef recipe or resources a wrapper around the behavior from a tool like sqitch: https://github.com/sqitchers/sqitch

There's even a good community cookbook to do it: https://github.com/chef-cookbooks/sqitch

4

Yes, since it is a command which runs in sqlplus window, not direct cmd. But that didnt work :frowning:

5

Could you please give a working example ? My code given above doesnt work for some reason and I cant figure out why. I have not kept any unwanted spaces or anything.
Also thanks for the details about guard conditions. It was the next thing I was getting stuck at when running the queries idempotent.

6

The solution I found for this issue was to create a windows batch file ( .bat ) which calls sql scripts in it and I called that .bat file inside my chef recipe.

execute 'run_sql_scripts' do
  command "path_to_bat_file/mybatfile.bat"
  not_if {some guard condition here}
  timeout node['module']['script']['timeout']
end

Within the batch file I call the scripts are below.

dir:\ORACLE_HOME\bin\sqlplus /nolog @dir:\path_to_scripts\scriptname.sql

This worked fine. Make sure you exit at last line of the sql script to avoid hanging of the program.

Thank you! all who helped.