oracle_sql run pl/sql package issue

matthiaslink77 commented 5 years ago

Hi,

I am trying to run a basic sql package, but to me it seems, the parser is cutting off the line after the first semicolon. Did I miss something here again?

 - name: apply application fixes by SQL
   oracle_sql:
     username: "{{ user_name }}"
     mode: "{{ user_mode }}"
     password: "{{ user_password }}"
     service_name: "{{ service_name }}"
     script: "{{ script_name }}"
   environment: "{{oracle_env}}"

The script currently only consists of this one command:

BEGIN DBMS_STATS.set_global_prefs ( pname => 'CONCURRENT', pvalue => 'ALL'); END; /

The output is as follows:

TASK [apply application fixes by SQL] **** fatal: []: FAILED! => {"changed": false, "msg": "Something went wrong while executing sql - ORA-06550: line 3, column 71:\nPLS-00103: Encountered the symbol \"end-of-file\" when expecting one of the following:\n\n := . ( % ; sql: \nBEGIN\n DBMS_STATS.set_global_prefs ( pname => 'CONCURRENT', pvalue => 'ALL')"}

I already tried several syntax variations, but none made it work for me. What am I missing?

Best regards, Matthias.

oravirt commented 5 years ago

Hi,

The oracle_sql module is pretty much in a perpetual alpha state as I haven't really done anything with it for about 2 years. I will look into this, but it's unfortunately not a priority at the moment.

There is a oracle_stat_prefs module that should be able to help with at least the stats prefs issue.

matthiaslink77 commented 5 years ago

Thanks - I will take a look at it!

I assume the part to check would be here:

... else: sqlfile = read_file(module, script) sqlfile = clean_sqlfile(sqlfile) sqldelim = ';' if 'create or replace' in sqlfile.lower(): sqldelim = '/'

So this might need a modification?

Best regards, Matthias.

matthiaslink77 commented 5 years ago

Hi Mikael, did you have a chance to look at this? Would be highly appreciated? Best regards, Matthias.

oravirt commented 5 years ago

Hi, I (hopefully) did a quick fix for this. Try it and let me know if it works out.

And just a note: If you want to manage database objects, you definitely should look into something like liquibase or flyway instead of using this module.

serpro69 commented 4 years ago

Also getting the same error. Any chances on this being fixed?

ari-stark commented 3 years ago

Hi,

I refactored oracle_sql module. You can find it here : https://github.com/ari-stark/ansible-oracle-modules/blob/devel/plugins/modules/oracle_sql.py.

It works for me, from simple request to several procedures. Tell me if it helps.

oravirt / ansible-oracle-modules

oracle_sql run pl/sql package issue #86