oracle_user module error expecting cx_oracle in DB server

Archives Ansible Project
1

I am trying to create oracle user using oracle_user module in a RDS server. Here is my playbook

  • hosts: all
    vars:
    oracle_home: /home/ansible/instantclient_12_2
    user: SYSDBA
    password: ******
    prt: “1521”
    hostname: “RDS Instance”
    sname: TESTDB
    oracle_env:
    ORACLE_HOME: “{{oracle_home}}”
    LD_LIBRARY_PATH: “{{oracle_home}}”
    PATH: “{{oracle_home}}”

vars_files:

  • vault-pass.yml

tasks:

  • name: This playbook will create the user.
    oracle_user:
    oracle_home: /home/ansible/instantclient_12_2
    user: “{{user}}”
    password: “{{password}}”
    service_name: “{{sname}}”
    port: “{{prt}}”
    hostname: “{{hostname}}”
    schema: *****
    schema_password: ****
    default_tablespace: USERS

state: present

grants: “‘create session’, create any table’”
environment: “{{oracle_env}}”

This fails with below error
FAILED! => {“changed”: false, “msg”: “The cx_Oracle module is required. ‘pip install cx_Oracle’ should do the trick. If cx_Oracle is installed, make sure ORACLE_HOME & LD_LIBRARY_PATH is set”}

DO we need to have cx_oracle installed in the target server too? I had this same issue with oracle_sql module too, but I used “connection: local” to enforced the playbook to look at the local cx_oracle install. This is not working in oracle_user module.

2

Hi

Ansible doesn’t have such a module, so I assume this is a 3rd party module?
In that case you might have more luck asking the module’s authors for support.

3

Hi,
I am using oracle_sql module and running a sql script. The sql script contains comments. The playbook fails because "
FAILED! => {“changed”: false, “msg”: "Something went wrong while executing sql - ORA-00911: invalid character sql:

Is there a way I can keep the comments and execute the sql script ?

4

Hi,
I am using oracle_sql module and running a sql script. The sql script contains comments. The playbook fails because "
FAILED! => {"changed": false, "msg": "Something went wrong while executing sql - ORA-00911: invalid character sql:

Is there a way I can keep the comments and execute the sql script ?

I suppose you could filter out the comments. How does your SQL script looks like?

Regards
        Racke

5

My sql script will look like

/* #######################################################################################################################################

Program : xxxx.sql

Programmer : xxxxx

Date : xxxxx

Usage : xxxx

Revisions : xxxxx

Dependencies :

1. Sqlplus Binaries.

6

My sql script will look like

/*
#######################################################################################################################################
# Program : xxxx.sql
# Programmer : xxxxx
# Date : xxxxx
# Usage : xxxx
# Revisions : xxxxx
# Dependencies :
# 1. Sqlplus Binaries.
#
# Notes : Script will creates Oracle packages on AWS RDS.
# Author : xxxx
# Creates change passwords and lock a/c's
# after the user is done .

#######################################################################################################################################
*/

So where is the SQL code?

Regards
         Racke

7

It goes soon after the comments

/*

8

It goes soon after the comments

/*
#######################################################################################################################################
# Program : xxxx.sql
# Programmer : xxxxx
# Date : xxxxx
# Usage : xxxx
# Revisions : xxxxx
# Dependencies :
# 1. Sqlplus Binaries.
#
# Notes : Script will creates Oracle packages on AWS RDS.
# Author : xxxx
# Creates change passwords and lock a/c's
# after the user is done .
#######################################################################################################################################
*/
set serveroutput on;
select username from dba_users;
/
..

Hello Anand,

given that this text is in the variable "sql", you can use the following filter:

{{sql | regex_replace('/\\*([\\s\\S]*)\\*/', '') | trim() }}

The result would be:

set serveroutput on;
select username from dba_users;

I found out that you have to use [\\s\\S]* instead of the more straightforward .* as .* doesn't match newlines.

Regards
          Racke

9

Hi,
Thank you so much. I made this work by commenting with – instead of #

Thank you for your help.