/ home / ibre5041 / ibre5041.ansible_oracle_modules / 3.2.0 / module / oracle_sql

ibre5041.ansible_oracle_modules.oracle_sql (3.2.0) — module

Execute arbitrary sql

| "added in version" 2.1.0.0 of ibre5041.ansible_oracle_modules"

Authors: Mikael Sandström, oravirt@gmail.com, @oravirt, Ari Stark (@ari-stark), Ivan Brezina

Install collection

Install with ansible-galaxy collection install ibre5041.ansible_oracle_modules:==3.2.0

Add to requirements.yml

  collections:
    - name: ibre5041.ansible_oracle_modules
      version: 3.2.0

Description

Execute arbitrary sql against an Oracle database

This module can be used to execute arbitrary SQL queries or PL/SQL blocks against an Oracle database.

If the SQL query is a select statement, the result will be returned.

If the script contains dbms_output.put_line(), the output will be returned.

Connection is set to autocommit. There is no rollback mechanism implemented.

See connection parameters for oracle_ping

Requirements

cx_Oracle

Usage examples

Scanned by Steampunk Spotter

# Execute one arbitrary SQL statement (no trailing semicolon)
- oracle_sql:
    mode: sysdba
    sql: "select username from dba_users"
  register: _oracle_dba_users
  environment:
    ORACLE_HOME: "{{ ORACLE_HOME }}"
    ORACLE_SID:  "{{ ORACLE_SID }}"
  become: yes
  become_user: "{{ oracle_owner }}"
  become_method: sudo

Scanned by Steampunk Spotter

# Execute several arbitrary SQL statements (each statement must end with a semicolon at end of line)
- oracle_sql:
    hostname: "foo.server.net"
    username: "foo"
    password: "bar"
    service_name: "pdb001"
    script: |
        insert into foo (f1, f2) values ('ab', 'cd');
        update foo set f2 = 'fg' where f1 = 'ab';

Scanned by Steampunk Spotter

# Execute several arbitrary PL/SQL blocks (must end with a trailing slash)
- oracle_sql:
    hostname: "foo.server.net"
    username: "foo"
    password: "bar"
    service_name: "pdb001"
    script: |
        begin
            [...]
        end;
        /
        begin
            [...]
        end;
        /

Scanned by Steampunk Spotter

# Execute arbitrary SQL file on DB server
- oracle_sql:
    mode: sysdba
    script: '@/u01/scripts/create-all-the-procedures.sql'

Scanned by Steampunk Spotter

# Execute SQL file included in playbook
- oracle_sql:
    mode: sysdba
    script: "{{ lookup('file', role_path + '/files/role_script.sql') }}"

Inputs

    
sql:
    description: The sql you want to execute
    required: false

script:
    description: The script you want to execute. Doesn't handle selects
    required: false