Ansible playbook 来执行 Oracle 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41796466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Ansible playbook to execute Oracle script
提问by Prashanth_Ramanathan
I am attempting to run a playbook to execute Oracle Scripts.
我正在尝试运行剧本来执行 Oracle 脚本。
---
- hosts: localhost
- tasks:
- set_fact:
execute_command: "sqlplus {{ Oracle_Username }}/{{ Oracle_Password }} @{{ sqlfile.sql }}"
- name: Get Object_details
shell: "echo exit | {{ execute_command }} >> ./Oracle_Output.csv"
environment:
ORACLE_HOME: "{{ Oracle_DBServer }}"
ORACLE_SID: "{{ Oracle_SID }}"
I have declared all the variables in vars.When I execute it , am getting error "set_fact is not valid attribute for a play" . Which is the best way to run SQL script using Ansible? I have to declare all the connection details in variable.
我已经在 vars 中声明了所有变量。当我执行它时，出现错误“set_fact 不是一个游戏的有效属性”。使用 Ansible 运行 SQL 脚本的最佳方法是什么？我必须在变量中声明所有连接细节。
回答by Prashanth_Ramanathan
---
- hosts: localhost
- tasks:
- name: Get Object_details
shell: echo exit |sqlplus "{{ oracle_username }}/ {{ oracle_pwd}} @(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host={{ oracle_hostname }} )(Port={{ oracle_port }}))(CONNECT_DATA=(SERVICE_NAME= {{ service_name }})))"@sqlfile.sql;
With the above code , we will be directly able to connect to Oracle Host and execute sql script. If the Oracle Environment variables are not defined by default, we can set that too in the playbook task itself. Below is the example for that :
有了上面的代码，我们就可以直接连接到Oracle Host并执行sql脚本了。如果默认情况下未定义 Oracle 环境变量，我们也可以在剧本任务本身中进行设置。下面是一个例子：
---
- hosts: localhost
- tasks:
- name: Get Object_details
shell: echo exit |sqlplus "{{ oracle_username }}/ {{ oracle_pwd}} @(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host={{ oracle_hostname }} )(Port={{ oracle_port }}))(CONNECT_DATA=(SERVICE_NAME= {{ service_name }})))"@sqlfile.sql;
environment:
ORACLE_HOME: <<Oracle Home path >>
PATH: << bin path >>
LD_LIBRARY_PATH: << lib path >>
回答by Konstantin Suvorov
You forgot to declare taskssection:
您忘记声明tasks部分：
---
- hosts: localhost
- tasks:
- set_fact:
execute_command: "sqlplus {{ Oracle_Username }}/{{ Oracle_Password }} @{{ sqlfile.sql }}"
- name: Get Object_details
shell: "echo exit | {{ execute_command }} >> ./Oracle_Output.csv"
environment:
ORACLE_HOME: "{{ Oracle_DBServer }}"
ORACLE_SID: "{{ Oracle_SID }}"
P.S. and I'm not aware of waitattribute for tasks.
PS，我不知道wait任务的属性。
回答by Yi Ren
You need to export ORACLE_HOME first. Then you can call sqlplus from oracle's bin folder.
您需要先导出 ORACLE_HOME。然后就可以从oracle 的bin 文件夹中调用sqlplus。
---
- hosts: localhost
- tasks:
- name: Execute table.sql using sqlplus
shell: $ORACLE_HOME/bin/sqlplus -s username/password@connect @table.sql
environment:
ORACLE_HOME: "{{oracle_home_path}}"
LD_LIBRARY_PATH: "{{ld_library_path}}"
PATH: "{{bin_path}}"
args:
chdir: "{{sql_path}}"
become: true
become_method: su
become_user: oracle
