Discoverer Migration Rules Copy link

Pipeline

1. Export (DIS Workbook Export Script report) generates dis51adm.exe /export ... /xmlworkbook commands per workbook (if not exist re-run safety, ping stagger). Set Executable Path, DB Service Name, EUL + password. Ignore Access History/Workbook Id list workbooks without statistics.
2. Upload (Setup>Tools>Import>XML Upload, Win10-style zip — fix-zip-compatibility skill) → xxen_discoverer_workbook_xmls keyed (eul, doc_id), EUL from profile XXEN_REPORT_DISCOVERER_DEFAULT_EUL (set it BEFORE upload when >1 EUL). Workbooks whose owner is unknown on the target are rejected.
3. Select (DIS Workbook Conversion Selection upload, XXEN_DIS_UPLOAD) → convert_flag NULL / Y selected / C converted; Submit Import=Yes submits and waits for the import and reports per workbook; Report Name Prefix param.
4. Import (Blitz Report Discoverer Import = xxen_xdo.import_discoverer_folders_cp; params End User Layer, Report Name Prefix, Object Use Key (;-list), Existing Reports (table value set XXEN_DISCOVERER_EXISTING_REPORTS over a union all from dual, codes UPDATE/SKIP/REPLACE; Skip is offered only without an Object Use Key and Delete and Recreate only with one, through :$FLEX$.XXEN_DISCOVERER_OBJECT_USE_KEY:NULL; folder sets without a report are always imported)).
5. Validate with the Blitz validation reports (Parameter Uniqueness, LOV SQL, SQL, Bind Variable, Table Alias, DIS Migration identify missing EulConditions, DIS Workbook Import Validation) and the discoverer_*_fix.sql scripts (static date defaults, All/% sentinels, output file name token, pivot/having, range parameter order) after every round.

Profiles

Staging tables

object_use_key = dot-separated list of the Discoverer folder obj_ids a worksheet draws from, derived from the flattened .eex by xxen_xdo.sheet_object_use_key. It is the grouping key: one Blitz Report is built per distinct object_use_key. The created report records its identity in the description as Object IDs: <object_use_key> + EUL: <eul> — this marker drives the "already imported" exclusion, the targeted Object Use Key reload, and the delta refresh's report lookup. It is NOT stored in a column, so re-deriving it is the only way to find an existing report for a folder set.

Condition handling — and the "hardcoded conditions ≠ defaulted parameters" rule

Purge per EUL

Per-EUL purge (from the report description) — deletes the 5 staging tables for l_eul, then all Discoverer-category reports + their orphaned (all-uppercase-description) LOVs:

declare l_eul varchar2(30):='eul_us';
begin
  delete xxen_discoverer_workbook_xmls xdwx where xdwx.eul=l_eul;
  delete xxen_discoverer_fnd_user xdfu where xdfu.eul=l_eul;
  delete xxen_discoverer_pivot_fields xdpf where xdpf.eul=l_eul;
  delete xxen_discoverer_sheets xds where xds.eul=l_eul;
  delete xxen_discoverer_workbooks xdw where xdw.eul=l_eul;
  for c in (select xrca.report_id from xxen_report_categories_v xrcv, xxen_report_category_assigns xrca where xrcv.category='Discoverer' and xrcv.category_id=xrca.category_id) loop
    xxen_api.delete_report(c.report_id,'Y');
  end loop;
  for c in (select xrplv.lov_id from xxen_report_parameter_lovs_v xrplv where xrplv.description=upper(xrplv.description) and xrplv.lov_id not in (select xrp.lov_id from xxen_report_parameters xrp where xrp.parameter_type='LOV' and xrp.lov_id is not null)) loop
    xxen_api.delete_lov(c.lov_id);
  end loop;
  commit;
end;

For a single folder set, prefer the conc program's Object Use Key parameter (deletes+recreates just that report) over a full purge.

Phase D — Post-import validation (the real cleanup)

Run these after import (from the export script description); each catches a class of Discoverer-ism that doesn't translate cleanly:

DIS Workbook Import Validation was reworked to the stats-free methodology: worksheet spine from the flattened staged xmls (eul5_documents FULL-joined to xxen_discoverer_workbook_xmls, so both EUL-only and staged-after-deletion workbooks appear), Object Use Key derived per sheet via xxen_xdo.sheet_object_use_key, Selected For Conversion/Converted from the flag, report matched by the derived object use key (OUK) marker, template by the Doc Id:+Sheet: markers; usage statistics OUTER-joined as informational columns bounded by Accessed after. The Include Inactive parameter (the old &full stats join toggle) was removed and Workbook Owner retargeted from the stats rows to the workbook owner. A helper sheet ([9,n]-referenced) shows its folder set's report but no template — the Not yet imported filter (template_name is null) therefore lists helper sheets too; expected, not a gap. An underivable workbook (foreign-EUL folders) shows uploaded+selected with a blank OUK and nothing matched — exactly the gap the report exists to surface.

Post-import DATA fixes — six scripts, run after EVERY import round

The validation reports above find what does not parse. These repair what parses and is still wrong — so nothing errors and only a human notices. Each takes PREVIEW / APPLY (no commit) and lists rather than guesses; re-run them after every round, because the conversion keeps emitting what they repair.

The parameter ceiling — a report can carry only as many parameters as the concurrent program has formals

xxen_report_ebs.report_host passes parameters positionally through XXEN_REPORT: it numbers them row_number() over (order by display_sequence, parameter_id) over the rows where display_sequence is not null (or the whole same-name group is invisible), and dispatches with a literal elsif c.parameter_number=<n> and parameter<n> is not null chain. There is no branch past the last formal, so a parameter beyond it is SILENTLY DROPPED at submit — the report runs with one filter not applied. A wrong answer, not an error.

• A migration hits this because grafting merges parameter sets. Each Discoverer worksheet brings its own prompts onto the shared report, and the count only grows.
• Hiding does not free a slot. The test is display_sequence IS NOT NULL: a per-template PARAMETER_EXCLUSION leaves the sequence intact, and a negative sequence is not null either. Only a row with no DISPLAY_SEQUENCE at all (a sub-row) is free — and a name whose rows are all sub-rows still takes one slot. A template parameter default frees nothing.
• Remedies: merge duplicate prompts that mean the same thing; demote a pure anchor-injection parameter to a sub-row; or split the report with discoverer_split_template_to_copy.sql so each copy carries only its own templates' parameters (then re-export the baseline and regenerate). Never delete a live parameter to make room — that is destructive and needs a ruling.

A converted report with MORE THAN 85 PARAMETERS cannot be submitted — consolidate the hidden worksheet-condition toggles into matching-value parameters

The hard limit: bin/XXEN_REPORT.prog / xxen_report_ebs.report_host declare parameter1..parameter85, so a report whose parameter count exceeds 85 cannot run at all (heavy Discoverer workbooks reach this — one worksheet condition = one hidden Yes-toggle parameter each). Only DISPLAY rows count: the report_host argument-mapping cursor filters display_sequence is not null (a name's sub-rows are excluded via the min(display_sequence) over (partition by parameter_name) test) — so matching-value sub-rows sharing one parameter name consume NO argument slot, which is what makes consolidation possible without touching the run screen.

The recipe (worked example: a report with 93 parameters and 150 templates, reduced to 71 parameters): the conversion's hidden Yes-toggle condition parameters (negative display_sequence, shared Yes LOV, literal bind-free SQL_TEXT, activated ONLY by a per-template default 'Y', never excluded by any template) merge into a few hidden matching-value parameters:

• One merged parameter Template Condition <n> per group: primary row carries DISPLAY_SEQUENCE (negative), LOV custom listing the codes (select '<CODE>' value, '<original condition name>' description from dual union all …), VALIDATE_FROM_LIST_DSP=Y; each condition becomes a row with MATCHING_VALUE=<code> + its original SQL_TEXT; each template's default row changes from (toggle,'Y') to (merged param, <code>).
• Grouping constraint: two conditions defaulted by the SAME template must land in DIFFERENT merged parameters (one parameter holds one value). Build the co-occurrence conflict graph over the template defaults and color it — the max co-occurrence dictates the merged-parameter count (e.g. max 3 co-occurring conditions → 3 parameters absorb 24 conditions).
• When the max co-occurrence is too high for coloring, use ONE merged parameter with one code per DISTINCT per-template condition SET instead — each matching row's SQL_TEXT is that set's member conditions each wrapped in parentheses and AND-joined. Worked example: a report (98 → 75 parameters) had a template defaulting 8 conditions at once (per-condition coloring would only reach 82), but only 20 distinct condition sets across 174 templates → one parameter, 20 matching rows, each template defaults its set code. The trade-off is SQL duplication across combo rows (a condition edited later must be edited in every set containing it) — acceptable because these are frozen Discoverer worksheet conditions, and it is actually truer to the original (one code = one worksheet's condition set).
• A toggle defaulted by NO template is dead (hidden + no report default = never injects) — delete it outright.
• Template defaults resolve by NAME to the display row only (import join xrp.display_sequence is not null), so the sub-rows can never receive a default. Exclusions were never written for the toggles by the conversion, so no exclusion rewiring is needed — and the visible parameters (with their thousands of per-template exclusion rows) stay byte-identical.
• Import-on-top is safe when kept rows keep their exact SORT_ORDER (the upsert key) and the new merged rows use fresh sort orders past the old max; the removed toggles' rows are seeded and get deleted by the parameter sync, cascading their old default rows.

Verify three ways: (a) offline — per template, the set of injected condition SQLs before vs after must be identical; (b) post-import DB — display-param count, defaults land on exactly the merged display rows, no dangling exclusion/default rows; (c) runtime — xxen_report.initialize_parameter_values(report_id, null, template_id) puts the CODE into the tmp values, xxen_api.create_run_id harvests it, and the executed SQL (v$sql) contains exactly the defaulted conditions and none of the sibling matching rows. A missing customer view is stood in per the established stand-in pattern (typed null columns from the referenced-column list), then dropped. When asserting injection via v$sql, filter on length(sql_fulltext) and probe sql_fulltext with dbms_lob.instr — a bare sql_text like '%<literal>%' check matches your OWN monitoring query (self-observation), and sql_text holds only the first 1000 chars, so the real run cursor (whose FROM clause sits ~75k chars in) is missed while the false self-match "passes".

Converter design (statistics-independent)

• One Blitz Report per object_use_key; worksheets become templates (template_name = Workbook[: Sheet], Doc Id:/Sheet: markers). Category Discoverer. Anchor 2=2.
• Update (Existing Reports=UPDATE): builds a new report and transplants it (refresh_report); parameters matched by normalised sql_text (else anchor), unmatched target parameters kept; templates matched by worksheet name, hand-made ones untouched; template defaults/exclusions inserted only where absent. Always reconciles parameters (no report-only option). Old stats-path Object IDs: markers may not match derived keys — compare before re-running.
• Post-import corrections done by the converter: duplicate names (lower(trim()) comparison + unique_parameter_name), range-pair ordering (adjacent and grouped merges keyed on the direction-word-stripped range name), prompt order from .eex document order (display_sequence captured at flatten), % defaults dropped, degenerate/oversized (>40 row fields) pivots removed, operator suffixes in names (from/to unbracketed, others (equals) etc.), ORA-00936 items skipped. No post-build parse (use Blitz Report SQL Validation).
• Hardcoded conditions (parameter_value null, complex_condition='N') become per-template EXCLUDED parameters carrying a template default that the engine still applies (initialize_parameter_values change); dates stored canonical YYYY/MM/DD; prompted date defaults are skipped (last-run values). Identification is by origin only — name/operator heuristics over-matched twice.
• Reports over 85 parameters cannot be submitted (report_host formals; hidden and negative sequences still count): consolidate hidden condition toggles into matching-value parameters (one per co-occurrence colour, or one code per distinct condition set); verify via v$sql sql_fulltext.
• Never render the literal token [5,1,"NULL"] as null: Discoverer itself generates it as the string 'NULL' (its saved worksheet SQL in ams_discoverer_sql shows TO_NUMBER(DECODE(...,'NULL')), ELSE 'NULL', NVL(x,'NULL') IN (...,'NULL')), and xml_expression_to_rpn/dis_formula already reproduce that. The NULL keyword is a different token, the function [1,115](), rendered null. A quoted 'NULL' in a numeric position therefore raises ORA-01722 in Discoverer too — a latent error in the source worksheet, fixed per report, not in the converter.
• The SQL Discoverer generated for a worksheet is in ams_discoverer_sql (chunks by sequence_order, assembled by xxen_util.dis_worksheet_sql), written by the EUL function eul_trigger$post_save_document on every save; workbook_owner_name is the EUL user name (#<user_id>) and there is no doc id. Use it to settle what Discoverer did with an expression before changing the converter.
• Query@IsDistinct → distinct_flag → select distinct wrap, which collapses identical detail rows; a Discoverer output showing duplicates ran without the flag.
• A <multiple_values> parameter on a converted report over a complex customer view (a DISTINCT view with per-row lookups or scalar subqueries) runs the view UNFILTERED. The engine rewrites col=:bind to col in (select dbms_lob.substr(xrrpv.value) ... from xxen_report_run_param_values xrrpv ...); a subquery predicate is not pushed into the view the way Discoverer's literal in ('…') is — Oracle unnests it to a semi-join ABOVE the view, so the view executes for every row before the item list applies. On 12c+ the CLOB reference is what excludes the subquery from complex view merging (a merge hint cannot force it; the identical subquery over a varchar2 column merges); on 11.2 neither form merges. Plan signature: the VIEW as the outer side of a NESTED LOOPS SEMI probing XXEN_REPORT_RUN_PARAM_VALUES last, with a normal-looking cost. Single-value runs are unaffected (=:bind pushes like a literal) and are the immediate workaround; push_pred on the view is refused (not legal into a DISTINCT view). Per-report fix: a hidden same-name sub-row on an &item_filter lexical anchor whose SQL_TEXT reads the run's values from xxen_report_run_param_values by :run_id (always bound in dynamic_sql_text) and parameter name, splits them with xxen_util.rowgen into a quote-escaped literal in list, capped (case when count(*) between 1 and 200) so oversize selections fall back to the bind subquery; the visible parameter and its bind predicate stay untouched. Two lexical-evaluation traps: other binds inside a lexical resolve to ONE value even for multi-select (insert_run_bind_values keeps min keep dense_rank first per bind — never reference the parameter's own bind), and lexicals evaluate BEFORE build_sql expands the stored <multiple_values>a;b;c row into N rows, so the lexical must handle both storage forms. Engine-level literal inlining is the general fix (product decision pending). Never test view mergeability with a no_merge harness (it suppresses the transformation in question) and never extrapolate merge behaviour across database versions.
• "Columns missing": diff the Discoverer SQL against xxen_reports.sql_text; negative template display_sequence = column absent from the SQL; fix the SQL, never the template; <name>2 suffixed columns select the source column twice.
• Class-A ORA-00904 "<ALIAS>"."<COL>" on old-engine reports = orphan FROM folder; re-convert on the current engine.
• Purge per EUL: delete the five staging tables, delete_report for Discoverer category, orphan LOVs; prefer the Object Use Key parameter for one folder set.