When I migrated to the 6.4.1 Noetix Views, I noticed that my custom view joins get blown away after a Noetix EUL regeneration.
What follows are my cursory annotations associated with how to re-create these joins via a script.
Please do not laugh that someone is using Oracle Discoverer in 2018.
Many salient aspects of custom Discoverer joins can be queried as follow:
SELECT
ekc.key_id,
eexp.exp_id,
to_number(regexp_substr(eexp.exp_formula1,'\[\d,(\d+)\]',1,2,NULL,1) ) pk_key_id,
to_number(regexp_substr(eexp.exp_formula1,'\[\d,(\d+)\]',1,3,NULL,1) ) fk_key_id
FROM
eul5_key_cons ekc
JOIN eul5_expressions eexp ON ekc.key_id = eexp.jp_key_id
AND eexp.exp_type = 'JP'
WHERE
1 = 1
AND ekc.key_type = 'FK'
AND ekc.key_developer_key = 'ZARCS_CS_COMMISSION_RATES_JOIN_TO_ARX0_TRANSACTION_LINE_DTLS';
I just make sure I use the developer key to find them.
In re-creating a Discoverer join, I just go to an instance where these joins still exist.
The steps for single column, rowid, driven joins is pretty straightforward. These notes are not comprehensive and are oriented towards this scenario which is prevalent with the Noetix Views.
Join Steps
-----------
1. create an eul5_key_cons record. This contains the naming the folder being joined and the nature of join.
2. create an eul5_expressions record. This has vital information about the columns being joined.
3. create two records in the table, eul5_exp_deps, associated with identifying primary and foreign key identification for the join.
For existing records, I just navigate to a pre-eul generated environment and query the key_developer_key like above to get the eul5_key_cons record. With SQL Developer, one can easily export the details of the record as an insert statement. This is what I did and then I modified some of the information (like the key_id). I thought of doing a "create table as a select statement", but I am biased against creating additional objects in the database for this purpose (it would probably make things simpler).
Here is an example of an insert statement for a join:
INSERT INTO eul5_key_cons (
key_id,
key_type,
key_name,
key_developer_key,
key_description,
key_ext_key,
key_obj_id,
uk_primary,
fk_key_id_remote,
fk_obj_id_remote,
fk_one_to_one,
fk_mstr_no_detail,
fk_dtl_no_master,
fk_mandatory,
key_user_prop2,
key_user_prop1,
key_element_state,
key_created_by,
key_created_date,
key_updated_by,
key_updated_date,
notm
) VALUES (
eul5_id_seq.nextval, --key_id
'FK', --key_type
'Z$ARCS_CS_Commission_Rates join to ARX0_Transaction_Line_Dtls', --key_name
'ZARCS_CS_COMMISSION_RATES_JOIN_TO_ARX0_TRANSACTION_LINE_DTLS', --key_developer_key
'Z$ARCS_CS_Commission_Rates join to ARX0_Transaction_Line_Dtls', --key_description
NULL, --key_ext_key
130292, --key_obj_id
NULL, --uk_primary
NULL, --fk_key_id_remote
135346, --fk_obj_id_remote
0, --fk_one_to_one
0, --fk_mstr_no_detail
1, --fk_dtl_no_master
0, --fk_mandatory
NULL, --key_user_prop2
NULL, --key_user_prop1
0, --key_element_state
'EUL5_US', --key_created_by
TO_DATE('20-APR-17','DD-MON-RR'),
--key_created_date
'EUL5_US', --key_updated_by
TO_DATE('20-APR-17','DD-MON-RR'),
--key_updated_date
0 --notm
);
--VALUES
Next, I obtained information for eul5_expressions record.
DECLARE
v_eul5_key_cons_key_id
eul5_us.eul5_key_cons.key_id%TYPE;
BEGIN
SELECT
ekc.key_id
INTO
v_eul5_key_cons_key_id
FROM
eul5_key_cons ekc
WHERE
1 = 1
AND
ekc.key_type = 'FK'
AND
ekc.key_developer_key =
'ZARCS_CS_COMMISSION_RATES_JOIN_TO_ARX0_TRANSACTION_LINE_DTLS';
INSERT INTO eul5_expressions (
exp_id,
exp_type,
exp_name,
exp_developer_key,
exp_description,
exp_formula1,
exp_data_type,
exp_sequence,
it_dom_id,
it_obj_id,
it_doc_id,
it_format_mask,
it_max_data_width,
it_max_disp_width,
it_alignment,
it_word_wrap,
it_disp_null_val,
it_fun_id,
it_heading,
it_hidden,
it_placement,
it_user_def_fmt,
it_case_storage,
it_case_display,
it_ext_column,
it_descriptor_id,
ci_it_id,
ci_runtime_item,
par_multiple_vals,
co_nullable,
p_case_sensitive,
jp_key_id,
fil_obj_id,
fil_doc_id,
fil_runtime_filter,
fil_app_type,
fil_ext_filter,
par_optional,
par_lov_desc_ids,
exp_user_prop2,
exp_user_prop1,
exp_element_state,
exp_created_by,
exp_created_date,
exp_updated_by,
exp_updated_date
) VALUES (
eul5_id_seq.NEXTVAL,
'JP',
'Join Predicate',
'JOIN_PREDICATE',
NULL,
'[1,81]([6,135432],[6,130303])',
10,
1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
1,
v_eul5_key_cons_key_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
'EUL5_US',
TO_DATE('20-APR-17','DD-MON-RR'),
'EUL5_US',
TO_DATE('20-APR-17','DD-MON-RR')
);
The exp_formula1 value keeps track of the columns being joined. With an EUL Generation, these column values are preserved during a regeneration.
For the eul5_exp_deps records, I just create quick annonymous block a preceding select into statement which grabs crucial information to populate in this table.
-- 'ZARCS_CS_COMMISSION_RATES_JOIN_TO_ARX0_TRANSACTION_LINE_DTLS'
BEGIN
v_eul5_exp_deps_ed_id1 := eul5_id_seq.nextval;
v_eul5_exp_deps_ed_id2 := eul5_id_seq.nextval;
SELECT
ekc.key_id,
eexp.exp_id,
to_number(regexp_substr(eexp.exp_formula1,'\[\d,(\d+)\]',1,2,NULL,1) ) pk_key_id,
to_number(regexp_substr(eexp.exp_formula1,'\[\d,(\d+)\]',1,3,NULL,1) ) fk_key_id
INTO
v_eul5_key_cons_key_id,v_eul5_expressions_jp_exp_id,v_eul5_exp_exp_pk,v_eul5_exp_exp_fk
FROM
eul5_key_cons ekc
JOIN eul5_expressions eexp ON ekc.key_id = eexp.jp_key_id
AND eexp.exp_type = 'JP'
WHERE
1 = 1
AND ekc.key_type = 'FK'
AND ekc.key_developer_key = 'ZARCS_CS_COMMISSION_RATES_JOIN_TO_ARX0_TRANSACTION_LINE_DTLS';
dbms_output.put_line('v_eul5_exp_exp_pk => ' || v_eul5_exp_exp_pk);
dbms_output.put_line('v_eul5_exp_exp_fk => ' || v_eul5_exp_exp_fk);
dbms_output.put_line('v_eul5_expressions_jp_exp_id => ' || v_eul5_expressions_jp_exp_id);
v_eul5_exp_deps_ed_id1 := eul5_id_seq.nextval;
v_eul5_exp_deps_ed_id2 := eul5_id_seq.nextval;
INSERT INTO eul5_exp_deps (
ed_id,
ed_type,
pd_p_id,
ped_exp_id,
pfd_fun_id,
psd_sq_id,
cd_exp_id,
cfd_fun_id,
cid_exp_id,
ed_element_state,
ed_created_by,
ed_created_date,
ed_updated_by,
ed_updated_date,
notm
) VALUES (
v_eul5_exp_deps_ed_id1,
'PED',
v_eul5_expressions_jp_exp_id,
v_eul5_exp_exp_pk,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
'EUL5_US',
v_date,
'EUL5_US',
v_date,
0
);
INSERT INTO eul5_exp_deps (
ed_id,
ed_type,
pd_p_id,
ped_exp_id,
pfd_fun_id,
psd_sq_id,
cd_exp_id,
cfd_fun_id,
cid_exp_id,
ed_element_state,
ed_created_by,
ed_created_date,
ed_updated_by,
ed_updated_date,
notm
) VALUES (
v_eul5_exp_deps_ed_id2,
'PED',
v_eul5_expressions_jp_exp_id,
v_eul5_exp_exp_fk,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
'EUL5_US',
v_date,
'EUL5_US',
v_date,
0
);
EXCEPTION
WHEN OTHERS THEN
v_error_code := sqlcode;
v_error_message := sqlerrm;
dbms_output.put_line(TO_CHAR(v_error_code)
|| '- '
|| v_error_message);
END;
----------------------------------------------------------
My error handling is not very remarkable (I will probably make this better at some point).