6 Replies Latest reply: Apr 20, 2013 8:44 PM by rp0428 RSS

    ORA-166512: string value too long for attribute "job_action"

    963638
      Hello I am trying to set up a schedule to execute daily for the following purge script however when i attempted to use the oracle sql developer job wizard i got the error ORA-166512 in the thread topic.

      Here is the script i am referring to

      spool soa11g_purge_script.log

      set echo on
      set verify on
      set timing on

      -- delete all instances older than 30 days
      define days=30

      alter session set current_schema=dev_soainfra;
      alter session set nls_date_format='yyyymmdd hh24mi';

      variable cur_datetime varchar2(13)
      exec select (sysdate - &days) into :cur_datetime from dual;

      prompt Purging data until:
      print cur_datetime
      --
      -- Purge the MEDIATOR data
      --
      delete from mediator_case_instance a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from mediator_audit_document a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from mediator_callback a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from mediator_group_status a where exists (select b.id from mediator_instance b where b.group_id = a.group_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));

      delete from mediator_payload where modify_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from mediator_deferred_message where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from mediator_resequencer_message where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from mediator_case_detail where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from mediator_correlation where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from mediator_instance where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      commit;

      --
      -- Purge the BPEL data
      --
      delete from headers_properties where modify_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from ag_instance where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from audit_counter where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from audit_trail where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from audit_details where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from ci_indexes where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from work_item where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from wi_fault where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from xml_document_ref a where exists (select b.document_id from xml_document b where b.document_id = a.document_id and b.doc_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from xml_document where doc_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from document_dlv_msg_ref where dlv_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from document_ci_ref where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from dlv_subscription where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from dlv_message where receive_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from rejected_msg_native_payload where rm_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from instance_payload where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from test_details a where exists (select b.cikey from cube_instance b where b.cikey = a.cikey and b.creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from cube_scope where modify_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from cube_instance where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      commit;

      --
      -- Purge the BPM data
      --
      delete from bpm_audit_query where create_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from bpm_measurement_actions where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from bpm_measurement_action_exceps where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from bpm_cube_auditinstance where cipartitiondate < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from bpm_cube_taskperformance where creationdate < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from bpm_cube_processperformance where creationdate < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      commit;

      --
      -- Purge the WORKFLOW data
      --
      delete from wftask_tl a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftaskhistory a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftaskhistory_tl a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfcomments a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfmessageattribute a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfattachment a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfassignee a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfreviewer a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfcollectiontarget a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfroutingslip a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfnotification a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftasktimer a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftaskerror a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfheaderprops a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfevidence a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftaskassignmentstatistic a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftaskaggregation where taskcreateddate < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from wftask where createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      commit;

      --
      -- Purge the COMPOSITE data
      --
      delete from composite_sensor_value where date_value < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from composite_instance_assoc where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from attachment c where exists (select a.key from attachment_ref a where a.key = c.key and exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi')));
      delete from attachment_ref a where exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from composite_instance_fault where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from reference_instance where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from component_instance where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from composite_instance where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');

      commit;

      --
      -- Reclaim disk space
      --
      alter table mediator_case_instance enable row movement;
      alter table mediator_case_instance shrink space;
      alter table mediator_case_instance disable row movement;
      alter table mediator_audit_document enable row movement;
      alter table mediator_audit_document shrink space;
      alter table mediator_audit_document disable row movement;
      alter table mediator_callback enable row movement;
      alter table mediator_callback shrink space;
      alter table mediator_callback disable row movement;
      alter table mediator_group_status enable row movement;
      alter table mediator_group_status shrink space;
      alter table mediator_group_status disable row movement;
      alter table mediator_payload enable row movement;
      alter table mediator_payload shrink space;
      alter table mediator_payload disable row movement;
      alter table mediator_deferred_message enable row movement;
      alter table mediator_deferred_message shrink space;
      alter table mediator_deferred_message disable row movement;
      alter table mediator_resequencer_message enable row movement;
      alter table mediator_resequencer_message shrink space;
      alter table mediator_resequencer_message disable row movement;
      alter table mediator_case_detail enable row movement;
      alter table mediator_case_detail shrink space;
      alter table mediator_case_detail disable row movement;
      alter table mediator_correlation enable row movement;
      alter table mediator_correlation shrink space;
      alter table mediator_correlation disable row movement;
      alter table mediator_instance enable row movement;
      alter table mediator_instance shrink space;
      alter table mediator_instance disable row movement;
      alter table headers_properties enable row movement;
      alter table headers_properties shrink space;
      alter table headers_properties disable row movement;
      alter table ag_instance enable row movement;
      alter table ag_instance shrink space;
      alter table ag_instance disable row movement;
      alter table audit_counter enable row movement;
      alter table audit_counter shrink space;
      alter table audit_counter disable row movement;
      alter table audit_trail enable row movement;
      alter table audit_trail shrink space;
      alter table audit_trail disable row movement;
      alter table audit_details enable row movement;
      alter table audit_details shrink space;
      alter table audit_details disable row movement;
      alter table ci_indexes enable row movement;
      alter table ci_indexes shrink space;
      alter table ci_indexes disable row movement;
      alter table work_item enable row movement;
      alter table work_item shrink space;
      alter table work_item disable row movement;
      alter table wi_fault enable row movement;
      alter table wi_fault shrink space;
      alter table wi_fault disable row movement;
      alter table xml_document_ref enable row movement;
      alter table xml_document_ref shrink space;
      alter table xml_document_ref disable row movement;
      alter table document_dlv_msg_ref enable row movement;
      alter table document_dlv_msg_ref shrink space;
      alter table document_dlv_msg_ref disable row movement;
      alter table document_ci_ref enable row movement;
      alter table document_ci_ref shrink space;
      alter table document_ci_ref disable row movement;
      alter table dlv_subscription enable row movement;
      alter table dlv_subscription shrink space;
      alter table dlv_subscription disable row movement;
      alter table dlv_message enable row movement;
      alter table dlv_message shrink space;
      alter table dlv_message disable row movement;
      alter table rejected_msg_native_payload enable row movement;
      alter table rejected_msg_native_payload shrink space;
      alter table rejected_msg_native_payload disable row movement;
      alter table instance_payload enable row movement;
      alter table instance_payload shrink space;
      alter table instance_payload disable row movement;
      alter table test_details enable row movement;
      alter table test_details shrink space;
      alter table test_details disable row movement;
      alter table cube_scope enable row movement;
      alter table cube_scope shrink space;
      alter table cube_scope disable row movement;
      alter table cube_instance enable row movement;
      alter table cube_instance shrink space;
      alter table cube_instance disable row movement;
      alter table bpm_audit_query enable row movement;
      alter table bpm_audit_query shrink space;
      alter table bpm_audit_query disable row movement;
      alter table bpm_measurement_actions enable row movement;
      alter table bpm_measurement_actions shrink space;
      alter table bpm_measurement_actions disable row movement;
      alter table bpm_measurement_action_exceps enable row movement;
      alter table bpm_measurement_action_exceps shrink space;
      alter table bpm_measurement_action_exceps disable row movement;
      alter table bpm_cube_auditinstance enable row movement;
      alter table bpm_cube_auditinstance shrink space;
      alter table bpm_cube_auditinstance disable row movement;
      alter table bpm_cube_taskperformance enable row movement;
      alter table bpm_cube_taskperformance shrink space;
      alter table bpm_cube_taskperformance disable row movement;
      alter table bpm_cube_processperformance enable row movement;
      alter table bpm_cube_processperformance shrink space;
      alter table bpm_cube_processperformance disable row movement;
      alter table wftask_tl enable row movement;
      alter table wftask_tl shrink space;
      alter table wftask_tl disable row movement;
      alter table wftaskhistory enable row movement;
      alter table wftaskhistory shrink space;
      alter table wftaskhistory disable row movement;
      alter table wftaskhistory_tl enable row movement;
      alter table wftaskhistory_tl shrink space;
      alter table wftaskhistory_tl disable row movement;
      alter table wfcomments enable row movement;
      alter table wfcomments shrink space;
      alter table wfcomments disable row movement;
      alter table wfmessageattribute enable row movement;
      alter table wfmessageattribute shrink space;
      alter table wfmessageattribute disable row movement;
      alter table wfattachment enable row movement;
      alter table wfattachment shrink space;
      alter table wfattachment disable row movement;
      alter table wfassignee enable row movement;
      alter table wfassignee shrink space;
      alter table wfassignee disable row movement;
      alter table wfreviewer enable row movement;
      alter table wfreviewer shrink space;
      alter table wfreviewer disable row movement;
      alter table wfcollectiontarget enable row movement;
      alter table wfcollectiontarget shrink space;
      alter table wfcollectiontarget disable row movement;
      alter table wfroutingslip enable row movement;
      alter table wfroutingslip shrink space;
      alter table wfroutingslip disable row movement;
      alter table wfnotification enable row movement;
      alter table wfnotification shrink space;
      alter table wfnotification disable row movement;
      alter table wftasktimer enable row movement;
      alter table wftasktimer shrink space;
      alter table wftasktimer disable row movement;
      alter table wftaskerror enable row movement;
      alter table wftaskerror shrink space;
      alter table wftaskerror disable row movement;
      alter table wfheaderprops enable row movement;
      alter table wfheaderprops shrink space;
      alter table wfheaderprops disable row movement;
      alter table wfevidence enable row movement;
      alter table wfevidence shrink space;
      alter table wfevidence disable row movement;
      alter table wftaskassignmentstatistic enable row movement;
      alter table wftaskassignmentstatistic shrink space;
      alter table wftaskassignmentstatistic disable row movement;
      alter table wftaskaggregation enable row movement;
      alter table wftaskaggregation shrink space;
      alter table wftaskaggregation disable row movement;
      alter table wftask enable row movement;
      alter table wftask shrink space;
      alter table wftask disable row movement;
      alter table composite_sensor_value enable row movement;
      alter table composite_sensor_value shrink space;
      alter table composite_sensor_value disable row movement;
      alter table composite_instance_assoc enable row movement;
      alter table composite_instance_assoc shrink space;
      alter table composite_instance_assoc disable row movement;
      alter table attachment enable row movement;
      alter table attachment shrink space;
      alter table attachment disable row movement;
      alter table attachment_ref enable row movement;
      alter table attachment_ref shrink space;
      alter table attachment_ref disable row movement;
      alter table composite_instance_fault enable row movement;
      alter table composite_instance_fault shrink space;
      alter table composite_instance_fault disable row movement;
      alter table reference_instance enable row movement;
      alter table reference_instance shrink space;
      alter table reference_instance disable row movement;
      alter table component_instance enable row movement;
      alter table component_instance shrink space;
      alter table component_instance disable row movement;
      alter table composite_instance enable row movement;
      alter table composite_instance shrink space;
      alter table composite_instance disable row movement;

      alter table audit_details modify lob (bin) (shrink space);
      alter table composite_instance_fault modify lob (error_message) (shrink space);
      alter table composite_instance_fault modify lob (stack_trace) (shrink space);
      alter table cube_scope modify lob (scope_bin) (shrink space);
      alter table reference_instance modify lob (error_message) (shrink space);
      alter table reference_instance modify lob (stack_trace) (shrink space);
      alter table test_definitions modify lob (definition) (shrink space);
      alter table wi_fault modify lob (message) (shrink space);
      alter table xml_document modify lob (document) (shrink space);

      alter index ad_pk rebuild online;
      alter index at_pk rebuild online;
      alter index ci_creation_date rebuild online;
      alter index ci_custom3 rebuild online;
      alter index ci_ecid rebuild online;
      alter index ci_name_rev_state rebuild online;
      alter index ci_pk rebuild online;
      alter index composite_instance_cidn rebuild online;
      alter index composite_instance_co_id rebuild online;
      alter index composite_instance_created rebuild online;
      alter index composite_instance_ecid rebuild online;
      alter index composite_instance_id rebuild online;
      alter index composite_instance_state rebuild online;
      alter index cs_pk rebuild online;
      alter index dm_conversation rebuild online;
      alter index dm_pk rebuild online;
      alter index doc_dlv_msg_guid_index rebuild online;
      alter index doc_store_pk rebuild online;
      alter index ds_conversation rebuild online;
      alter index ds_conv_state rebuild online;
      alter index ds_fk rebuild online;
      alter index ds_pk rebuild online;
      alter index header_properties_pk rebuild online;
      alter index instance_payload_key rebuild online;
      alter index reference_instance_cdn_state rebuild online;
      alter index reference_instance_co_id rebuild online;
      alter index reference_instance_ecid rebuild online;
      alter index reference_instance_id rebuild online;
      alter index reference_instance_state rebuild online;
      alter index reference_instance_time_cdn rebuild online;
      alter index state_type_date rebuild online;
      alter index wf_crdate_cikey rebuild online;
      alter index wf_crdate_type rebuild online;
      alter index wf_fk2 rebuild online;
      alter index wifault_pk rebuild online;
      alter index wi_expired rebuild online;
      alter index wi_key_crdate_state rebuild online;
      alter index wi_pk rebuild online;
      alter index wi_stranded rebuild online;
      alter index xml_doc_reference_pk rebuild online;

      spool off
        • 1. Re: ORA-166512: string value too long for attribute "job_action"
          L-MachineGun
          For one, job action is limited to 4000 characters:
          TYPE job_definition IS OBJECT (
            job_name                       VARCHAR2(100),
            job_class                      VARCHAR2(32),
            job_style                      VARCHAR2(11),
            program_name                   VARCHAR2(100),
            job_action                     VARCHAR2(4000),
            job_type                       VARCHAR2(20),
           ...  Etc ...
          :p
          • 2. Re: ORA-166512: string value too long for attribute "job_action"
            rp0428
            Whenever you post provide your 4 digit Oracle version.
            >
            Hello I am trying to set up a schedule to execute daily for the following purge script however when i attempted to use the oracle sql developer job wizard i got the error ORA-166512 in the thread topic.

            Here is the script i am referring to
            >
            But you didn't post the code you are actually having the problem with!

            Post the code you are using to 'set up a schedule to execute daily'. That is where the problem is.
            • 3. Re: ORA-166512: string value too long for attribute "job_action"
              963638
              Here is the code; also yes i realized job action is limited to 4000 characters; I am new to this


              BEGIN
              SYS.DBMS_SCHEDULER.CREATE_JOB (
              job_name => '"DEV_SOAINFRA"."insert"',
              job_type => 'PLSQL_BLOCK',
              job_action => 'spool soa11g_purge_script.log

              set echo on
              set verify on
              set timing on

              -- delete all instances older than 30 days
              define days=30

              alter session set current_schema=dev_soainfra;
              alter session set nls_date_format=''yyyymmdd hh24mi'';

              variable cur_datetime varchar2(13)
              exec select (sysdate - &days) into :cur_datetime from dual;

              prompt Purging data until:
              print cur_datetime
              --
              -- Purge the MEDIATOR data
              --
              delete from mediator_case_instance a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from mediator_audit_document a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from mediator_callback a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from mediator_group_status a where exists (select b.id from mediator_instance b where b.group_id = a.group_id and b.created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi''));

              delete from mediator_payload where modify_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from mediator_deferred_message where creation_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from mediator_resequencer_message where creation_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from mediator_case_detail where created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from mediator_correlation where creation_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from mediator_instance where created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              commit;

              --
              -- Purge the BPEL data
              --
              delete from headers_properties where modify_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from ag_instance where creation_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from audit_counter where ci_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from audit_trail where ci_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from audit_details where ci_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from ci_indexes where ci_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from work_item where creation_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from wi_fault where creation_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from xml_document_ref a where exists (select b.document_id from xml_document b where b.document_id = a.document_id and b.doc_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from xml_document where doc_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from document_dlv_msg_ref where dlv_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from document_ci_ref where ci_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from dlv_subscription where ci_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from dlv_message where receive_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from rejected_msg_native_payload where rm_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from instance_payload where created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from test_details a where exists (select b.cikey from cube_instance b where b.cikey = a.cikey and b.creation_date < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from cube_scope where modify_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from cube_instance where creation_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              commit;

              --
              -- Purge the BPM data
              --
              delete from bpm_audit_query where create_time < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from bpm_measurement_actions where ci_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from bpm_measurement_action_exceps where ci_partition_date < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from bpm_cube_auditinstance where cipartitiondate < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from bpm_cube_taskperformance where creationdate < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from bpm_cube_processperformance where creationdate < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              commit;

              --
              -- Purge the WORKFLOW data
              --
              delete from wftask_tl a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wftaskhistory a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wftaskhistory_tl a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wfcomments a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wfmessageattribute a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wfattachment a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wfassignee a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wfreviewer a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wfcollectiontarget a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wfroutingslip a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wfnotification a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wftasktimer a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wftaskerror a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wfheaderprops a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wfevidence a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wftaskassignmentstatistic a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from wftaskaggregation where taskcreateddate < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from wftask where createddate < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              commit;

              --
              -- Purge the COMPOSITE data
              --
              delete from composite_sensor_value where date_value < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from composite_instance_assoc where created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from attachment c where exists (select a.key from attachment_ref a where a.key = c.key and exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi'')));
              delete from attachment_ref a where exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi''));
              delete from composite_instance_fault where created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from reference_instance where created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from component_instance where created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi'');
              delete from composite_instance where created_time < to_date(:cur_datetime, ''yyyymmdd hh24mi'');

              commit;

              --
              -- Reclaim disk space
              --
              alter table mediator_case_instance enable row movement;
              alter table mediator_case_instance shrink space;
              alter table mediator_case_instance disable row movement;
              alter table mediator_audit_document enable row movement;
              alter table mediator_audit_document shrink space;
              alter table mediator_audit_document disable row movement;
              alter table mediator_callback enable row movement;
              alter table mediator_callback shrink space;
              alter table mediator_callback disable row movement;
              alter table mediator_group_status enable row movement;
              alter table mediator_group_status shrink space;
              alter table mediator_group_status disable row movement;
              alter table mediator_payload enable row movement;
              alter table mediator_payload shrink space;
              alter table mediator_payload disable row movement;
              alter table mediator_deferred_message enable row movement;
              alter table mediator_deferred_message shrink space;
              alter table mediator_deferred_message disable row movement;
              alter table mediator_resequencer_message enable row movement;
              alter table mediator_resequencer_message shrink space;
              alter table mediator_resequencer_message disable row movement;
              alter table mediator_case_detail enable row movement;
              alter table mediator_case_detail shrink space;
              alter table mediator_case_detail disable row movement;
              alter table mediator_correlation enable row movement;
              alter table mediator_correlation shrink space;
              alter table mediator_correlation disable row movement;
              alter table mediator_instance enable row movement;
              alter table mediator_instance shrink space;
              alter table mediator_instance disable row movement;
              alter table headers_properties enable row movement;
              alter table headers_properties shrink space;
              alter table headers_properties disable row movement;
              alter table ag_instance enable row movement;
              alter table ag_instance shrink space;
              alter table ag_instance disable row movement;
              alter table audit_counter enable row movement;
              alter table audit_counter shrink space;
              alter table audit_counter disable row movement;
              alter table audit_trail enable row movement;
              alter table audit_trail shrink space;
              alter table audit_trail disable row movement;
              alter table audit_details enable row movement;
              alter table audit_details shrink space;
              alter table audit_details disable row movement;
              alter table ci_indexes enable row movement;
              alter table ci_indexes shrink space;
              alter table ci_indexes disable row movement;
              alter table work_item enable row movement;
              alter table work_item shrink space;
              alter table work_item disable row movement;
              alter table wi_fault enable row movement;
              alter table wi_fault shrink space;
              alter table wi_fault disable row movement;
              alter table xml_document_ref enable row movement;
              alter table xml_document_ref shrink space;
              alter table xml_document_ref disable row movement;
              alter table document_dlv_msg_ref enable row movement;
              alter table document_dlv_msg_ref shrink space;
              alter table document_dlv_msg_ref disable row movement;
              alter table document_ci_ref enable row movement;
              alter table document_ci_ref shrink space;
              alter table document_ci_ref disable row movement;
              alter table dlv_subscription enable row movement;
              alter table dlv_subscription shrink space;
              alter table dlv_subscription disable row movement;
              alter table dlv_message enable row movement;
              alter table dlv_message shrink space;
              alter table dlv_message disable row movement;
              alter table rejected_msg_native_payload enable row movement;
              alter table rejected_msg_native_payload shrink space;
              alter table rejected_msg_native_payload disable row movement;
              alter table instance_payload enable row movement;
              alter table instance_payload shrink space;
              alter table instance_payload disable row movement;
              alter table test_details enable row movement;
              alter table test_details shrink space;
              alter table test_details disable row movement;
              alter table cube_scope enable row movement;
              alter table cube_scope shrink space;
              alter table cube_scope disable row movement;
              alter table cube_instance enable row movement;
              alter table cube_instance shrink space;
              alter table cube_instance disable row movement;
              alter table bpm_audit_query enable row movement;
              alter table bpm_audit_query shrink space;
              alter table bpm_audit_query disable row movement;
              alter table bpm_measurement_actions enable row movement;
              alter table bpm_measurement_actions shrink space;
              alter table bpm_measurement_actions disable row movement;
              alter table bpm_measurement_action_exceps enable row movement;
              alter table bpm_measurement_action_exceps shrink space;
              alter table bpm_measurement_action_exceps disable row movement;
              alter table bpm_cube_auditinstance enable row movement;
              alter table bpm_cube_auditinstance shrink space;
              alter table bpm_cube_auditinstance disable row movement;
              alter table bpm_cube_taskperformance enable row movement;
              alter table bpm_cube_taskperformance shrink space;
              alter table bpm_cube_taskperformance disable row movement;
              alter table bpm_cube_processperformance enable row movement;
              alter table bpm_cube_processperformance shrink space;
              alter table bpm_cube_processperformance disable row movement;
              alter table wftask_tl enable row movement;
              alter table wftask_tl shrink space;
              alter table wftask_tl disable row movement;
              alter table wftaskhistory enable row movement;
              alter table wftaskhistory shrink space;
              alter table wftaskhistory disable row movement;
              alter table wftaskhistory_tl enable row movement;
              alter table wftaskhistory_tl shrink space;
              alter table wftaskhistory_tl disable row movement;
              alter table wfcomments enable row movement;
              alter table wfcomments shrink space;
              alter table wfcomments disable row movement;
              alter table wfmessageattribute enable row movement;
              alter table wfmessageattribute shrink space;
              alter table wfmessageattribute disable row movement;
              alter table wfattachment enable row movement;
              alter table wfattachment shrink space;
              alter table wfattachment disable row movement;
              alter table wfassignee enable row movement;
              alter table wfassignee shrink space;
              alter table wfassignee disable row movement;
              alter table wfreviewer enable row movement;
              alter table wfreviewer shrink space;
              alter table wfreviewer disable row movement;
              alter table wfcollectiontarget enable row movement;
              alter table wfcollectiontarget shrink space;
              alter table wfcollectiontarget disable row movement;
              alter table wfroutingslip enable row movement;
              alter table wfroutingslip shrink space;
              alter table wfroutingslip disable row movement;
              alter table wfnotification enable row movement;
              alter table wfnotification shrink space;
              alter table wfnotification disable row movement;
              alter table wftasktimer enable row movement;
              alter table wftasktimer shrink space;
              alter table wftasktimer disable row movement;
              alter table wftaskerror enable row movement;
              alter table wftaskerror shrink space;
              alter table wftaskerror disable row movement;
              alter table wfheaderprops enable row movement;
              alter table wfheaderprops shrink space;
              alter table wfheaderprops disable row movement;
              alter table wfevidence enable row movement;
              alter table wfevidence shrink space;
              alter table wfevidence disable row movement;
              alter table wftaskassignmentstatistic enable row movement;
              alter table wftaskassignmentstatistic shrink space;
              alter table wftaskassignmentstatistic disable row movement;
              alter table wftaskaggregation enable row movement;
              alter table wftaskaggregation shrink space;
              alter table wftaskaggregation disable row movement;
              alter table wftask enable row movement;
              alter table wftask shrink space;
              alter table wftask disable row movement;
              alter table composite_sensor_value enable row movement;
              alter table composite_sensor_value shrink space;
              alter table composite_sensor_value disable row movement;
              alter table composite_instance_assoc enable row movement;
              alter table composite_instance_assoc shrink space;
              alter table composite_instance_assoc disable row movement;
              alter table attachment enable row movement;
              alter table attachment shrink space;
              alter table attachment disable row movement;
              alter table attachment_ref enable row movement;
              alter table attachment_ref shrink space;
              alter table attachment_ref disable row movement;
              alter table composite_instance_fault enable row movement;
              alter table composite_instance_fault shrink space;
              alter table composite_instance_fault disable row movement;
              alter table reference_instance enable row movement;
              alter table reference_instance shrink space;
              alter table reference_instance disable row movement;
              alter table component_instance enable row movement;
              alter table component_instance shrink space;
              alter table component_instance disable row movement;
              alter table composite_instance enable row movement;
              alter table composite_instance shrink space;
              alter table composite_instance disable row movement;

              alter table audit_details modify lob (bin) (shrink space);
              alter table composite_instance_fault modify lob (error_message) (shrink space);
              alter table composite_instance_fault modify lob (stack_trace) (shrink space);
              alter table cube_scope modify lob (scope_bin) (shrink space);
              alter table reference_instance modify lob (error_message) (shrink space);
              alter table reference_instance modify lob (stack_trace) (shrink space);
              alter table test_definitions modify lob (definition) (shrink space);
              alter table wi_fault modify lob (message) (shrink space);
              alter table xml_document modify lob (document) (shrink space);

              alter index ad_pk rebuild online;
              alter index at_pk rebuild online;
              alter index ci_creation_date rebuild online;
              alter index ci_custom3 rebuild online;
              alter index ci_ecid rebuild online;
              alter index ci_name_rev_state rebuild online;
              alter index ci_pk rebuild online;
              alter index composite_instance_cidn rebuild online;
              alter index composite_instance_co_id rebuild online;
              alter index composite_instance_created rebuild online;
              alter index composite_instance_ecid rebuild online;
              alter index composite_instance_id rebuild online;
              alter index composite_instance_state rebuild online;
              alter index cs_pk rebuild online;
              alter index dm_conversation rebuild online;
              alter index dm_pk rebuild online;
              alter index doc_dlv_msg_guid_index rebuild online;
              alter index doc_store_pk rebuild online;
              alter index ds_conversation rebuild online;
              alter index ds_conv_state rebuild online;
              alter index ds_fk rebuild online;
              alter index ds_pk rebuild online;
              alter index header_properties_pk rebuild online;
              alter index instance_payload_key rebuild online;
              alter index reference_instance_cdn_state rebuild online;
              alter index reference_instance_co_id rebuild online;
              alter index reference_instance_ecid rebuild online;
              alter index reference_instance_id rebuild online;
              alter index reference_instance_state rebuild online;
              alter index reference_instance_time_cdn rebuild online;
              alter index state_type_date rebuild online;
              alter index wf_crdate_cikey rebuild online;
              alter index wf_crdate_type rebuild online;
              alter index wf_fk2 rebuild online;
              alter index wifault_pk rebuild online;
              alter index wi_expired rebuild online;
              alter index wi_key_crdate_state rebuild online;
              alter index wi_pk rebuild online;
              alter index wi_stranded rebuild online;
              alter index xml_doc_reference_pk rebuild online

              spool off
              ',
              number_of_arguments => 0,
              start_date => NULL,
              repeat_interval => 'FREQ=DAILY;BYHOUR=12;BYMINUTE=0;BYSECOND=0',
              end_date => NULL,
              job_class => '"SYS"."DEFAULT_JOB_CLASS"',
              enabled => FALSE,
              auto_drop => FALSE,
              comments => 'insert table');





              SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
              name => '"DEV_SOAINFRA"."insert"',
              attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);


              SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
              name => '"DEV_SOAINFRA"."insert"',
              attribute => 'max_run_duration', value => INTERVAL '1' HOUR);
              SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
              name => '"DEV_SOAINFRA"."insert"',
              attribute => 'schedule_limit', value => INTERVAL '1' HOUR);
              SYS.DBMS_SCHEDULER.enable(
              name => '"DEV_SOAINFRA"."insert"');
              END;
              /
              • 4. Re: ORA-166512: string value too long for attribute "job_action"
                L-MachineGun
                You cannot put those statements inside an anonymous block (BEGIN/END structure).
                You should put all these statements in a sql file, code an os script to execute this sql file and then schedule the execution of the os script.
                :p
                • 5. Re: ORA-166512: string value too long for attribute "job_action"
                  963638
                  How may this be done? Do you have a sample? I am literally new to this.
                  • 6. Re: ORA-166512: string value too long for attribute "job_action"
                    rp0428
                    >
                    How may this be done? Do you have a sample? I am literally new to this.
                    >
                    How do you create a script on your server file system? Open an editor and type it in. Or use cut & paste.

                    As far as the scheduler part create a job with a job_type of EXECUTABLE and a job_action that provides the script name to Oracle.

                    Search for EXECUTABLE in the DBMS_SCHEDULER in the packages and types doc
                    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm

                    There are plenty of examples on the web of running scripts using the scheduler.