oracle_jobs/oracle_sql

[topher-debusk]

This is a 2 part issue... I was hoping to accomplish this using a module - but maybe it is not easily doable.

If not, then I was trying to put it into file and run it... but that doesn't seem to work either (error below)

`DECLARE L_N_JOB_COUNT NUMBER := 0; L_V_AUD_PARM_VALUE VARCHAR2(30); L_V_AUD_TS_NAME VARCHAR2(30); L_V_AUD_TS_CONF_NAME VARCHAR2(30);

BEGIN EXECUTE IMMEDIATE 'SELECT VALUE FROM V$PARAMETER WHERE NAME = ''audit_trail''' INTO L_V_AUD_PARM_VALUE; EXECUTE IMMEDIATE 'SELECT TABLESPACE_NAME FROM ALL_TABLES WHERE TABLE_NAME = ''AUD$''' INTO L_V_AUD_TS_NAME; EXECUTE IMMEDIATE 'SELECT PARAMETER_VALUE FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE PARAMETER_NAME = ''DB AUDIT TABLESPACE'' AND AUDIT_TRAIL = ''STANDARD AUDIT TRAIL''' INTO L_V_AUD_TS_CONF_NAME;

IF (L_V_AUD_PARM_VALUE <> 'DB') THEN DBMS_OUTPUT.put_line('audit_trail value not set to DB - it is ' || L_V_AUD_PARM_VALUE || '.'); END IF;

-- keep AUD$ tablespace name if different from config name --IF (L_V_AUD_TS_NAME <> L_V_AUD_TS_CONF_NAME) THEN IF (L_V_AUD_TS_NAME <> 'AUD_DATA') THEN DBMS_OUTPUT.PUT_LINE('Moving AUDIT_TRAIL_AUD_STD location to AUD_DATA.'); --' || L_V_AUD_TS_NAME || '.'); DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUD_DATA'--L_V_AUD_TS_NAME ); END IF;

-- initialize AUD$ cleanup if not done so already IF NOT DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN DBMS_AUDIT_MGMT.init_cleanup( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, DEFAULT_CLEANUP_INTERVAL => 24 / hours /); END IF;

-- report AUD$ cleanup intialization status IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN DBMS_OUTPUT.put_line('AUDIT_TRAIL_AUD_STD has been initialized.'); ELSE DBMS_OUTPUT.put_line('AUDIT_TRAIL_AUD_STD has NOT been initialized.'); END;`

Error: `The full traceback is: File "/tmp/ansible_ZUoQ7U/ansible_module_oracle_sql.py", line 107, in execute_sql cursor.execute(sql)

failed: [dbdevdb00chuk02.marketpipe.com -> localhost] (item={u'script': u'/home/tdebusk/techops-ansible/conf.d/roles/oracle_common/tasks/scripts/audit_setup.sql'}) => { "changed": false, "invocation": { "module_args": { "hostname": "dbdevdb00chuk02.marketpipe.com", "mode": "sysdba", "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "port": "1521", "script": "/home/tdebusk/techops-ansible/conf.d/roles/oracle_common/tasks/scripts/audit_setup.sql", "service_name": "dbdev", "sql": null, "user": "sys" } }, "item": { "script": "/home/tdebusk/techops-ansible/conf.d/roles/oracle_common/tasks/scripts/audit_setup.sql" }, "msg": "Something went wrong while executing sql - ORA-06550: line 2, column 27:\nPLS-00103: Encountered the symbol \"end-of-file\" when expecting one of the following:\n\n * & = - + ; < / > at in is mod remainder not rem\n <an exponent (**)> <> or != or ~= >= <= <> and or like like2\n like4 likec between || multiset member submultiset sql: DECLARE\n L_N_JOB_COUNT NUMBER := 0" } `

[oravirt]

I’ll look into the oracle_sql issue.

(It’s in a sort of perpetual alpha state so I’d expect issues ;-) )