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" } }