Reading each line in CSV file and use the items and execute the SQL statement against a remote Database.

[Raja Shekar]

Hello Ansible Gurus,

I have a CSV file as below. 

"DB_SCHEMA","SYSTEM_USER","SYSTEM_PASSWORD","TNS_NAME","TNS_DESCRIPTION"

"Client_Schema1","user","password","TNSNAME","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))"

"Client_Schema2","user","password","TNSNAME","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))"

"Client_Schema3","user","password","TNSNAME","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))"

I need to read each line of this CSV file and execute a SQL statement against a Remote Database. 

This is the play book, which logs in to shared DB and get the CSV file to Local Ansible Master and execute Client Schema on Remote DB upgrade by using the entries from Shared DB. 

  - name: Run sqlplus command on {{ Shared DB }}

    shell: source ~/.bash_profile && sqlplus -S '{{ SharedDB_username }}/{{ SharedDB_password }}@{{ SharedDB_database_host }}:1521/{{ SharedDB_service_name }}' @"/etc/ansible/playbooks/database/roles/oracle-sql/files/final_query.sql"

    delegate_to: localhost

    tags: clientSchema

  - name: Run powershell script

    script: files/DecryptCsv.ps1 -InputFilePath '{{ input_csv_path }}' \

                        -OutputFilePath '{{ output_csv_path }}' \

                        -PassPhrase '{{ pass_phrase }}' \

                        -SaltValue '{{ salt_value }}' \

                        -PasswordIterations {{ password_iterations }} \

                        -InitVector '{{ init_vector }}'

    delegate_to: localhost

    tags: clientSchema

  - name: get CSV File Content

    shell: source ~/.bash_profile && awk -F',' '!/^#/ && !/^$/ { print }' "{{ output_csv_path }}"

    register: groups_out

    delegate_to: localhost

  - debug: var=groups_out.stdout_lines

  - name: Update the Client Schema

    shell: source ~/.bash_profile && sqlplus -S '{{ item.1.groups_out }}/{{ item.2.groups_out }}@{{ item.4.groups_out }}' @"roles/oracle-sql/files/Scripts/client_hotfix.sql" {{ item.0.groups_out }}

    sudo: no

    with_items: "{{ groups_out.stdout_lines }}"

Failing with below error message

fatal: [10.202.98.198]: FAILED! => {"failed": true, "msg": "The task includes an option with an undefined variable. The error was: 'unicode object' has no attribute 'groups_out'\n\nThe error appears to have been in '/etc/ansible/playbooks/database/roles/oracle-sql/tasks/main.yml': line 57, column 5, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n\n  - name: Update the Schema info\n    ^ here\n\nexception type: <class 'ansible.errors.AnsibleUndefinedVariable'>\nexception: 'unicode object' has no attribute 'groups_out'"}

And also I might need to use When condition to check particular client schema and shared schema version on remote DB then only apply this hotfix. it would be helpful if you guide me here to fix this issue. I tried different options nothing is working for me :( 

Thanks and Regards
Shekar

[Kai Stian Olstad]

On Monday, 26 February 2018 07.15.27 CET Raja Shekar wrote:
> - debug: var=groups_out.stdout_lines
>
> - name: Update the Client Schema
> shell: source ~/.bash_profile && sqlplus -S '{{ item.1.groups_out }}/{{
> item.2.groups_out }}@{{ item.4.groups_out }}'
> @"roles/oracle-sql/files/Scripts/client_hotfix.sql" {{ item.0.groups_out }}
> sudo: no
> with_items: "{{ groups_out.stdout_lines }}"
>
>
> Failing with below error message
>
> fatal: [10.202.98.198]: FAILED! => {"failed": true, "msg": "The task
> includes an option with an undefined variable. The error was: 'unicode
> object' has no attribute 'groups_out'\n\nThe error appears to have been in
> '/etc/ansible/playbooks/database/roles/oracle-sql/tasks/main.yml': line 57,
> column 5, but may\nbe elsewhere in the file depending on the exact syntax
> problem.\n\nThe offending line appears to be:\n\n\n - name: Update the
> Schema info\n ^ here\n\nexception type: <class
> 'ansible.errors.AnsibleUndefinedVariable'>\nexception: 'unicode object' has
> no attribute 'groups_out'"}

It says item.X.groups_out do not exist.
You would need to show you the content of groups_out.stdout_lines

g
--
Kai Stian Olstad

[Raja Shekar]

Hello Kai, 

Thanks for looking into this. here is the content of "groups_out.stdout_lines"

    "groups_out.stdout_lines": [

        "\"DB_SCHEMA\",\"SYSTEM_USER\",\"SYSTEM_PASSWORD\",\"TNS_NAME\",\"TNS_DESCRIPTION\"",

        "\"Client_Schema1\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

        "\"Client_Schema2\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

        "\"Client_Schema3\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

        "\"Client_Schema4\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

        "\"Client_Schema5\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

        "\"Client_Schema6\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

    ]

} 

[Raja Shekar]

and also I updated the code now 

---

  - name: Run sqlplus command on {{ Shared DB }}

    shell: source ~/.bash_profile && sqlplus -S '{{ SharedDB_username }}/{{ SharedDB_password }}@{{ SharedDB_database_host }}:1521/{{ SharedDB_service_name }}' @"/etc/ansible/playbooks/database/roles/oracle-sql/files/final_query.sql"

    delegate_to: localhost

    tags: clientSchema

  - name: Run powershell script

    script: files/DecryptCsv.ps1 -InputFilePath '{{ input_csv_path }}' \

                        -OutputFilePath '{{ output_csv_path }}' \

                        -PassPhrase '{{ pass_phrase }}' \

                        -SaltValue '{{ salt_value }}' \

                        -PasswordIterations {{ password_iterations }} \

                        -InitVector '{{ init_vector }}'

    delegate_to: localhost

    tags: clientSchema

  - name: get CSV File Content

    shell: source ~/.bash_profile && awk -F',' '!/^#/ && !/^$/ { print }' "{{ output_csv_path }}"

    register: groups_out

    delegate_to: localhost

  - debug: var=groups_out.stdout_lines

  - name: Update the Client Schema

    shell: source ~/.bash_profile && sqlplus -S '{{ item.1 }}/{{ item.2 }}@{{ item.4 }}' @"roles/oracle-sql/files/Scripts/client_hotfix.sql" {{ item.0 }}

    with_items: "{{ groups_out.stdout_lines }}"

    delegate_to: localhost

 
And getting below Output

TASK [oracle-sql : Run sqlplus command on SharedDB] ****************************************************************************************

Monday 26 February 2018  16:43:11 -0600 (0:00:00.186)       0:00:00.186 *******

changed: [10.202.98.146 -> localhost]

TASK [oracle-sql : Run powershell script] ******************************************************************************************************************

Monday 26 February 2018  16:43:12 -0600 (0:00:01.067)       0:00:01.254 *******

changed: [10.202.98.146 -> localhost]

TASK [oracle-sql : get CSV File Content] *******************************************************************************************************************

Monday 26 February 2018  16:43:14 -0600 (0:00:01.524)       0:00:02.778 *******

changed: [10.202.98.146 -> localhost]

TASK [oracle-sql : debug] **********************************************************************************************************************************

Monday 26 February 2018  16:43:14 -0600 (0:00:00.241)       0:00:03.019 *******

ok: [10.202.98.146 -> localhost] => {

    "groups_out.stdout_lines": [

        "\"DB_SCHEMA\",\"SYSTEM_USER\",\"SYSTEM_PASSWORD\",\"TNS_NAME\",\"TNS_DESCRIPTION\"",

        "\"Client_Schema1\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",  Client_Schema DB_Schema

        "\"Client_Schema2\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

        "\"Client_Schema3\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

        "\"Client_Schema4\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

        "\"Client_Schema5\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

        "\"Client_Schema6\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

    ]

}

TASK [oracle-sql : Update the Client Schema] ***************************************************************************************************************

Monday 26 February 2018  16:43:14 -0600 (0:00:00.052)       0:00:03.072 *******

failed: [10.202.98.146 -> localhost] (item="DB_SCHEMA","SYSTEM_USER","SYSTEM_PASSWORD","TNS_NAME","TNS_DESCRIPTION") => {"changed": true, "cmd": "source ~/.bash_profile && sqlplus -S 'D/B@S' @\"roles/oracle-sql/files/Scripts/client_hotfix.sql\" \"", "delta": "0:00:00.004017", "end": "2018-02-26 16:43:14.830389", "failed": true, "item": "\"DB_SCHEMA\",\"SYSTEM_USER\",\"SYSTEM_PASSWORD\",\"TNS_NAME\",\"TNS_DESCRIPTION\"", "msg": "non-zero return code", "rc": 1, "start": "2018-02-26 16:43:14.826372", "stderr": "/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'\n/bin/bash: -c: line 1: syntax error: unexpected end of file", "stderr_lines": ["/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'", "/bin/bash: -c: line 1: syntax error: unexpected end of file"], "stdout": "", "stdout_lines": []}

failed: [10.202.98.146 -> localhost] (item="Client_Schema1","user","password","TNS_NAME","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))") => {"changed": true, "cmd": "source ~/.bash_profile && sqlplus -S 'C/l@e' @\"roles/oracle-sql/files/Scripts/client_hotfix.sql\" \"", "delta": "0:00:00.003745", "end": "2018-02-26 16:43:15.008136", "failed": true, "item": "\"Client_Schema1\",\"user\",\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"", "msg": "non-zero return code", "rc": 1, "start": "2018-02-26 16:43:15.004391", "stderr": "/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'\n/bin/bash: -c: line 1: syntax error: unexpected end of file", "stderr_lines": ["/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'", "/bin/bash: -c: line 1: syntax error: unexpected end of file"], "stdout": "", "stdout_lines": []}

failed: [10.202.98.146 -> localhost] (item="Client_Schema2","user","password","TNS_NAME","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))") => {"changed": true, "cmd": "source ~/.bash_profile && sqlplus -S 'C/l@e' @\"roles/oracle-sql/files/Scripts/client_hotfix.sql\" \"", "delta": "0:00:00.003985", "end": "2018-02-26 16:43:15.184393", "failed": true, "item": "\"Client_Schema2\",\"user\",\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"", "msg": "non-zero return code", "rc": 1, "start": "2018-02-26 16:43:15.180408", "stderr": "/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'\n/bin/bash: -c: line 1: syntax error: unexpected end of file", "stderr_lines": ["/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'", "/bin/bash: -c: line 1: syntax error: unexpected end of file"], "stdout": "", "stdout_lines": []}

failed: [10.202.98.146 -> localhost] (item="Client_Schema3","user","password","TNS_NAME","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))") => {"changed": true, "cmd": "source ~/.bash_profile && sqlplus -S 'C/l@e' @\"roles/oracle-sql/files/Scripts/client_hotfix.sql\" \"", "delta": "0:00:00.003499", "end": "2018-02-26 16:43:15.359487", "failed": true, "item": "\"Client_Schema3\",\"user\",\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"", "msg": "non-zero return code", "rc": 1, "start": "2018-02-26 16:43:15.355988", "stderr": "/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'\n/bin/bash: -c: line 1: syntax error: unexpected end of file", "stderr_lines": ["/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'", "/bin/bash: -c: line 1: syntax error: unexpected end of file"], "stdout": "", "stdout_lines": []}

failed: [10.202.98.146 -> localhost] (item="Client_Schema4","user","password","TNS_NAME","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))") => {"changed": true, "cmd": "source ~/.bash_profile && sqlplus -S 'C/l@e' @\"roles/oracle-sql/files/Scripts/client_hotfix.sql\" \"", "delta": "0:00:00.003757", "end": "2018-02-26 16:43:15.533251", "failed": true, "item": "\"Client_Schema4\",\"user\",\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"", "msg": "non-zero return code", "rc": 1, "start": "2018-02-26 16:43:15.529494", "stderr": "/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'\n/bin/bash: -c: line 1: syntax error: unexpected end of file", "stderr_lines": ["/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'", "/bin/bash: -c: line 1: syntax error: unexpected end of file"], "stdout": "", "stdout_lines": []}

failed: [10.202.98.146 -> localhost] (item="Client_Schema5","user","password","TNS_NAME","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))") => {"changed": true, "cmd": "source ~/.bash_profile && sqlplus -S 'C/l@e' @\"roles/oracle-sql/files/Scripts/client_hotfix.sql\" \"", "delta": "0:00:00.003864", "end": "2018-02-26 16:43:15.709202", "failed": true, "item": "\"Client_Schema5\",\"user\",\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"", "msg": "non-zero return code", "rc": 1, "start": "2018-02-26 16:43:15.705338", "stderr": "/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'\n/bin/bash: -c: line 1: syntax error: unexpected end of file", "stderr_lines": ["/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'", "/bin/bash: -c: line 1: syntax error: unexpected end of file"], "stdout": "", "stdout_lines": []}

failed: [10.202.98.146 -> localhost] (item="Client_Schema6","user","password","TNS_NAME","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))") => {"changed": true, "cmd": "source ~/.bash_profile && sqlplus -S 'C/l@e' @\"roles/oracle-sql/files/Scripts/client_hotfix.sql\" \"", "delta": "0:00:00.003533", "end": "2018-02-26 16:43:15.880787", "failed": true, "item": "\"Client_Schema6\",\"user\",\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"", "msg": "non-zero return code", "rc": 1, "start": "2018-02-26 16:43:15.877254", "stderr": "/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'\n/bin/bash: -c: line 1: syntax error: unexpected end of file", "stderr_lines": ["/bin/bash: -c: line 0: unexpected EOF while looking for matching `\"'", "/bin/bash: -c: line 1: syntax error: unexpected end of file"], "stdout": "", "stdout_lines": []}

        to retry, use: --limit @/home/user/.ansible/oracle_sql.retry

For some reason it's thinking that Item.0 as 1st position of line and item.1 as second position of line. I am looking more like DB_SCHEMA as item.0, SYSTEM_USER as item.1 and so on......

Am I doing something wrong here?  and also Is that possible I can ignore line 1 which contains Field information? 

[Kai Stian Olstad]

On Tuesday, 27 February 2018 00.04.31 CET Raja Shekar wrote:
>
> On Monday, February 26, 2018 at 11:00:57 AM UTC-6, Raja Shekar wrote:
> >
> > "groups_out.stdout_lines": [
> >
> > "\"DB_SCHEMA\",\"SYSTEM_USER\",\"SYSTEM_PASSWORD\",\"TNS_NAME\",\"TNS_DESCRIPTION\"",
> >
> > "\"Client_Schema1\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",
> >
> > "\"Client_Schema2\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",
> >
> > "\"Client_Schema3\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",
> >
> > "\"Client_Schema4\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",
> >
> > "\"Client_Schema5\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",
> >
> > "\"Client_Schema6\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",
> > ]
> > }

groups_out.stdout_lines is a list element is a line so you can say groups_out.stdout_lines.0 for the fist line

"\"DB_SCHEMA\",\"SYSTEM_USER\",\"SYSTEM_PASSWORD\",\"TNS_NAME\",\"TNS_DESCRIPTION\""

and groups_out.stdout_lines.1 for the second line

"\"Client_Schema1\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\"",

Since your csv contains the headers of the column this is going to be problematic so I suggest you filter them out in your awk command.

with_items: "{{ groups_out.stdout_lines }}" will take the first item into the variable item.
(I assume the line with the column names are removed)

"\"Client_Schema1\",\"user\"password\",\"TNS_NAME\",\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SANDBOX1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))\""

This is one string so item will contain the whole line.

So getting element 0 from a string, item.0, will be the first character and item.1 will be the second and so on.

You need to create list that split on the comma, item.split(',') will create a list and item.split(',')[0] is the fist element "Client_Schema1" and item.split(',')[1] the second one and so on.
Since you date contains double qoutes it will contain those qoutes, so if they causing a problem you need to remove them.

> For some reason it's thinking that Item.0 as 1st position of line and
> item.1 as second position of line.

That is correct since you are doing this on a string...

> I am looking more like DB_SCHEMA as
> item.0, SYSTEM_USER as item.1 and so on......

...and not on a list

> Am I doing something wrong here? and also Is that possible I can ignore
> line 1 which contains Field information?

Remove it in you awk where you remove empty lines and lines starting with #


--
Kai Stian Olstad

[Raja Shekar]

Thanks Kai for detailed response, this looks more cleaner.

TASK [oracle-sql : debug] **********************************************************************************************************
Tuesday 27 February 2018  11:30:36 -0600 (0:00:00.224)       0:00:05.277 ******
    "groups_out.stdout_lines": [
        "Client_Schema1,user,password,TNS_NAME,(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))",
        "Client_Schema2,user,password,TNS_NAME,(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))",
        "Client_Schema3,user,password,TNS_NAME,(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))",
        "Client_Schema4,user,password,TNS_NAME,(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))",
        "Client_Schema5,user,password,TNS_NAME,(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))",
        "Client_Schema6,user,password,TNS_NAME,(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))",
     ]
}

---

  - name: Run sqlplus command on {{ Shared DB }}

    shell: source ~/.bash_profile && sqlplus -S '{{ SharedDB_username }}/{{ SharedDB_password }}@{{ SharedDB_database_host }}:1521/{{ SharedDB_service_name }}' @"/etc/ansible/playbooks/database/roles/oracle-sql/files/final_query.sql"

    delegate_to: localhost

    tags: clientSchema

  - name: Run powershell script

    script: files/DecryptCsv.ps1 -InputFilePath '{{ input_csv_path }}' \

                        -OutputFilePath '{{ output_csv_path }}' \

                        -PassPhrase '{{ pass_phrase }}' \

                        -SaltValue '{{ salt_value }}' \

                        -PasswordIterations {{ password_iterations }} \

                        -InitVector '{{ init_vector }}'

    delegate_to: localhost

    tags: clientSchema

  - name: Get CSV File Content with out header

    shell: source ~/.bash_profile && awk -F',' '!/^#/ && !/^$/ && (NR!=1) { print }' "{{ output_csv_path }}" | sed 's/\"//g'

    register: groups_out

    delegate_to: localhost

  - debug: var=groups_out.stdout_lines

    delegate_to: localhost

  - name: Update the Client Schema

    shell: source ~/.bash_profile && sqlplus -S '{{ item.1 }}/{{ item.2 }}@{{ item.4 }}' @"roles/oracle-sql/files/Scripts/client_hotfix.sql" {{ item.0 }}

    with_items: "{{ groups_out.stdout_lines }}"

    delegate_to: localhost

How ever split option not working for me, Can you please provide sample snippet if possible for me to fetch individual items and split with comma based. 
Your help is highly appreciated on this. 

Thanks and Regards
Shekar

[Kai Stian Olstad]

On Tuesday, 27 February 2018 18.39.22 CET Raja Shekar wrote:
>
> - name: Update the Client Schema
> shell: source ~/.bash_profile && sqlplus -S '{{ item.1 }}/{{ item.2
> }}@{{ item.4 }}' @"roles/oracle-sql/files/Scripts/client_hotfix.sql" {{
> item.0 }}
> with_items: "{{ groups_out.stdout_lines }}"
> delegate_to: localhost
>
>
> How ever split option not working for me, Can you please provide sample
> snippet if possible for me to fetch individual items and split with comma
> based.
> Your help is highly appreciated on this.

You just need to replace your items like this:

- name: Update the Client Schema

shell: source ~/.bash_profile && sqlplus -S '{{ item.split(",").1 }}/{{ item.split(",").2 }}@{{ item.split(",").4 }}' @"roles/oracle-sql/files/Scripts/client_hotfix.sql" {{ item.split(",").0 }}
with_items: "{{ groups_out.stdout_lines }}"
delegate_to: localhost


--
Kai Stian Olstad

[Raja Shekar]

Awesome Thanks Kai, This works like a charm. Sorry I misunderstood your statement earlier and trying to split that ahead of my execution of client_hotfix.sql

[Raja Shekar]

@Kai Stian Olstad

On top of what we done, I have a requirement to compare shared Schema version comparison, updated code is below. 

Step:1 Fetch the Shared Schema version Value and register for future reference. 
Step:2 Stream Line CSV file and register the Output 
Step:3 Use the Streamlined CSV file and Client schema values in Column 1, Get the client schema Build version, these values are array of stdout_lines. How can I register this? 
Step:4 And use the Registered values and upgrade client schema when Shared Schema Version and Client Schema Version matches.(This is tricky part) 

Can I combine Step:3 and Step:4 ? Any clues. 

---

  - name: Run sqlplus command on Target DB server

    shell: source ~/.bash_profile && sqlplus -S '{{ admin }}/{{ password }}@{{ deploy_server }}:1521/{{ oracle_db_name }}' @"sharedschema_version.sql"

    args:

      chdir: roles/oracle-sql/files/

    delegate_to: localhost

    register: sharedschema_version

    tags: shared_schema

  - set_fact:

       sharedschema_version_build_number: "{{ sharedschema_version.stdout_lines }}"

    delegate_to: localhost

  - name: Get CSV File Content with out header

    shell: source ~/.bash_profile && awk -F',' '!/^#/ && !/^$/ && (NR!=1) { print }' "{{ output_csv_path }}" | sed 's/\"//g'

    register: groups_out

    delegate_to: localhost

    tags: client_schema

  - debug: var=groups_out.stdout_lines

    delegate_to: localhost

  - name: Run sqlplus command on Client DB

    shell: source ~/.bash_profile && sqlplus -S '{{ admin }}/{{ password }}@{{ deploy_server }}:1521/{{ oracle_db_name }}' @"clientschema_version.sql" {{ item.split(",").0 }}

    args:

      chdir: roles/oracle-sql/files/

    register: "clientschema_version"

    with_items: "{{ groups_out.stdout_lines }}"

    delegate_to: localhost

    tags: client_schema

  - debug: var=clientschema_version

    delegate_to: localhost

  - debug: msg="item.item={{item.item}}, item.stdout={{item.stdout}}, item.changed={{item.changed}}"

    with_items: "{{clientschema_version.results}}"

    delegate_to: localhost

  - name: Update the Client Schema

    shell: source ~/.bash_profile && sqlplus -S '{{ admin }}/{{ password }}@{{ item.split(",").3 }}' @"hotfix_client.sql" {{ item.split(",").0 }}

    with_items:

                - "{{ groups_out.stdout_lines }}"

                - "{{ clientschema_version.results }}"

    args:

      chdir: roles/oracle-sql/files/DbScripts/Scripts/

    delegate_to: localhost

    when: sharedschema_version_build_number == "{{ clientschema_version.stdout_lines }}"

Here is my error log 

 FAILED! => {
    "failed": true,
    "msg": "The conditional check 'sharedschema_version_build_number == \"{{ clientschema_version.stdout_lines }}\"' failed. The error was: error while evaluating conditional (sharedschema_version_build_number == \"{{ clientschema_version.stdout_lines }}\"): 'dict object' has no attribute 'stdout_lines'\n\nThe error appears to have been in '/etc/ansible/playbooks/database/roles/oracle-sql/tasks/main.yml': line 64, column 5, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n\n  - name: Update the Client Schema\n    ^ here\n"
}