How to spool the sql script ?

[anandsolomon]

Hi,
I need to run a playbook with spool like below. I am not able to use oracle_sql module to exeute.

spool log.log
select username from dba_users;
spool off;

I get the below error
},
"msg": "Something went wrong while executing sql - ORA-00900: invalid SQL statement sql: spool log.log\nselect username from dba_users"
}

[oravirt]

Hi,
Yes, that is probably not supported right now. Are you running this as a script?
I think I look for the ; or / delimiters for each individual sql, and since the spool command doesn't have either it will interpret that as part of the entire statement.

I might add that as a feature eventually, but in the meantime you should be able to register the module output and use that in a template.

[anandsolomon]

Thanks very much. This is what I am trying to do. Is there a way we can do ?

1 Task.
spool script.sql
select username from dba_users;
spool off;

2 task
@script.sql

[oravirt]

This is the overall (simplistic) way of achieving what you want, you may have to parse the output in the template somehow but I'll leave that as an exercise ;-)

pseudo-code:

- name: run sql
  oracle_sql:
      sql: "select ......."
    register: sqloutput

- name: build sqlfile from template
  template:
      src: mysqltemplate.sql.j2
      dest: /where/ever/script.sql

- name: run sqlscript
  oracle_sql:
     script: /where/ever/script.sql

And then the minimum content of the template is something like:

cat mysqltemplate.sql.j2

-- This is the sql template which will take the output 
-- from the first oracle_sql task and paste it into the template
{{ sqlputput.stdout_lines }}

[anandsolomon]

Thanks for the explanation.

I will be running a script file in the first task. I need to take the output of the first script and run that as a script in my second task.

• name: run sql
  oracle_sql:
  script: "/where/ever/mainsript.sql"
  register: sqloutput

• name: build sqlfile from template
  template:
  src: mysqltemplate.sql.j2
  dest: /where/ever/script.sql

• name: run sqlscript
  oracle_sql:
  script: /where/ever/script.sql

[anandsolomon]

also, the below returns the results properly in debug msg.
sql: "select grantee from dba_tab_privs where table_name='UTL_FILE' and grantee not like 'PUBLIC' and grantee != 'WMSYS'"

script: /home/ansible/sql/record.sql

TASK [display output] **************************************************************************************************************************
ok: [22.174.101.30] => {
"msg": {
"changed": false,
"failed": false,
"msg": [
[
"XDB"
],
[
"TSTSCH"
]
]
}
}

but running as a sql script, says finished running the script,but where do we see the output ?
TASK [display output] **************************************************************************************************************************
ok: [22.174.101.30] => {
"msg": {
"changed": true,
"failed": false,
"msg": "Finished running script /home/ansible/sql/record.sql \nContents: \nselect grantee from dba_tab_privs"
}
}