Execute SQL in Oracle with tables that contain the "$" character in the name

[Luiz Gustavo]

Hello experts,

I'm testing a playbook and I intend to make a SQL query on an Oracle database.
If the query contains the character "$" or is protected "\$", I need them to be treated as protected "\$".
I'm using the regex_replace filter but I'm having difficulty.
It would be something like:


# Start code
---
- hosts: "{{ awxhostname }}"
  gather_facts: false
  become: yes
  become_user: oracle
 
  tasks:

    - name: Exec SQL
      shell:
        cmd: |
          /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/sqlplus -silent "/ as sysdba" << EOF
              SET HEADING OFF;
              SET FEEDBACK OFF;
              SET TAB OFF;
              {{ query | regex_replace('([\\])?([$])', '\1\2') }}
            exit
          EOF
      environment:
        ORACLE_HOME: /u01/app/oracle/product/19.0.0.0/dbhome_1
        ORACLE_SID: "{{ sid_name }}"
        ORACLE_BASE: /u01/app/oracle
      register: cdb_out

    - name: Output
      debug:
        msg: "{{ cdb_out.stdout_lines }}"
# Final code

#-> Obs.: The execution is carried out by AWX through a survey, where:
awxhostname = Text
query = Textarea

-> And values:

awxhostname: hostname
query: select instance_name from v$instance;


#--> But in both I am getting the error below. Could you please help me?

"stdout_lines": [
    "    select instance_name from v",
    "                              *",
    "ERROR at line 1:",
    "ORA-00942: table or view does not exist"

[Todd Lewis]

See if this helps. It uses a regex negative lookbehind, only inserting a backslash in front of the dollar sign if there isn't already one there. You'll need to adapt it to your use case a little bit.

---
# escape-dollar.yml
- name: Escape dollar signs
  hosts: localhost
  gather_facts: false
  vars:
    dollar_string: |
      A. Thi$ line contain$ une$caped dollar $igns.
      B. Thi\$ line contain\$ e\$caped dollar \$igns.
  tasks:
    - name: Exec SQL
      shell:
        cmd: |
          printf "%s\n" '{{ dollar_string }}'
          printf "%s\n" '{{ dollar_string | regex_replace('(?<!\\)(\$)', '\\\1') }}'
      register: cdb_out

    - name: Output
      debug:
        msg: "{{ cdb_out.stdout_lines }}"

## TASK [Output] ***************************************
## ok: [localhost] => 
##   msg:
##   - A. Thi$ line contain$ une$caped dollar $igns.
##   - B. Thi\$ line contain\$ e\$caped dollar \$igns.
##   - ''
##   - A. Thi\$ line contain\$ une\$caped dollar \$igns.
##   - B. Thi\$ line contain\$ e\$caped dollar \$igns.

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAL-Q%3DKtFFNN9hYJnO4q9Rnpb-jMQvBSZQ3FKhOx6tGH2vxWTPA%40mail.gmail.com.

-- 
Todd

[Luiz Gustavo]

Worked perfectly! Thanks Todd.