ari_stark.ansible_oracle_modules.oracle_sql (1.2.1) — module

Install collection

Install with ansible-galaxy collection install ari_stark.ansible_oracle_modules:==1.2.1

Add to requirements.yml

  collections:
    - name: ari_stark.ansible_oracle_modules
      version: 1.2.1

Description

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.

Requirements

• Python module cx_Oracle
• Oracle basic tools.

Usage examples

Scanned by Steampunk Spotter

• Success
  Steampunk Spotter scan finished with no errors, warnings or hints.

# Execute one arbitrary SQL statement (no trailing semicolon)
- oracle_sql:
    hostname: "foo.server.net"
    username: "foo"
    password: "bar"
    service_name: "pdb001"
    sql: "select username from dba_users"

Scanned by Steampunk Spotter

• Success
  Steampunk Spotter scan finished with no errors, warnings or hints.

# 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

• Success
  Steampunk Spotter scan finished with no errors, warnings or hints.

# 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

• Success
  Steampunk Spotter scan finished with no errors, warnings or hints.

# Execute arbitrary SQL file
- oracle_sql:
    hostname: "foo.server.net"
    username: "foo"
    password: "bar"
    service_name: "pdb001"
    script: '@/u01/scripts/create-all-the-procedures.sql'

Inputs

    
sql:
    description:
    - A single SQL statement.
    - If this statement is a select query (starts with select) the result is returned.
    - The statement mustn't end with a semicolon.
    type: str

mode:
    choices:
    - normal
    - sysdba
    default: normal
    description:
    - This option is the database administration privileges.
    type: str

port:
    default: 1521
    description:
    - Specify the listening port on the database server.
    type: int

script:
    description:
    - The SQL script to execute. It can be direct SQL statements, PL/SQL blocks or the
      name of a file.
    - If I(script) is SQL statements, each statements must end with a semicolon at end
      of line.
    - If I(script) is PL/SQL blocks, each block must end with a slash on a new line.
    - If I(script) is a file, it must start with a @ (i.e @/path/to/file.sql).
    type: str

hostname:
    default: localhost
    description:
    - Specify the host name or IP address of the database server computer.
    type: str

password:
    description:
    - Set the password to use to connect the database server.
    - Must not be set if using Oracle wallet.
    type: str

username:
    aliases:
    - user
    description:
    - Set the login to use to connect the database server.
    - Must not be set if using Oracle wallet.
    type: str

oracle_home:
    description:
    - Define the directory into which all Oracle software is installed.
    - Define ORACLE_HOME environment variable if set.
    type: str

service_name:
    description:
    - Specify the service name of the database you want to access.
    required: true
    type: str

Outputs

data:
  description: Contains a two dimensionnal array containing the fetched lines and
    columns of the select query.
  elements: list
  returned: if I(sql) is a select statement
  type: list
output_lines:
  description: Contains the output of scripts made by dbms_output.put_line().
  elements: str
  returned: always, but is empty if I(script) doesn't contain dbms_output.put_line().
  type: list
statements:
  description: Contains a list of SQL statements executed.
  elements: str
  returned: if I(sql) is not a select statement or I(script) is used
  type: list