Errors In Dm_oracle When Running Search With Self-Join (Doc ID 743280.1)

Last updated on SEPTEMBER 02, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.2.1.0.0 and later
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms


While running a PCM_OP_SEARCH with an input flist that joins:

/account(child).PIN_FLD_POID_ID0 to/billinfo(child).PIN_FLD_ACCOUNT_OBJ_ID0
/billinfo(child).PIN_FLD_AR_BILLINFO_OBJ_ID0 to /billinfo(parent).PIN_FLD_POID_ID0
/billinfo(parent).PIN_FLD_ACCOUNT_OBJ_ID0 to /account(parent).PIN_FLD_POID_ID0
/account(parent).PIN_FLD_POID_ID0 to /profile/subprofile(parent).PIN_FLD_ACCOUNT_OBJ_ID0.

The custom /profile/subprofile references a substruct tied to a table with a name that is 30
chars long.

Because the query contains a self-join and more than one instance of the same table, the
dm_oracle generates SQL with the table names aliased, like the following (I've tidied it up and
simplified it for ease of legibility):

SELECT DISTINCT account_t1.poid_db, account_t1.poid_id0, account_t1.poid_type,
account_t1.poid_rev FROM account_t account_t1,account_products_t
account_products_t1, account_cycle_fees_t account_cycle_fees_t1, billinfo_t
billinfo_t2, account_t account_t3, billinfo_t billinfo_t4, profile_t profile_t5, *
profile_thus_payment_profile_t profile_thus_payment_profile_t5 *
WHERE billinfo_t2.account_obj_id0 = account_t1.poid_id0
AND billinfo_t2.ar_billinfo_obj_id0 = billinfo_t4.poid_id0
AND billinfo_t4.account_obj_id0 = account_t3.poid_id0
AND profile_t5.account_obj_id0 = account_t3.poid_id0
AND account_cycle_fees_t1.charged_to_t <= :1
* AND profile_thus_payment_profile_t5.custom_field = :2 *
[...]
* AND profile_thus_payment_profile_t5.custom_field = 1 *
AND account_t1.poid_id0 = account_products_t1.obj_id0
AND account_t1.poid_id0 = account_cycle_fees_t1.obj_id0
** AND account_products_t.rec_id = account_cycle_fees_t.rec_id2 **
AND profile_t5.poid_id0 = profile_thus_payment_profile_t5.obj_id0;

There are two problems:
1) The custom profile /profile/thus_payment_profile has a PIN_FLD_PAYMENT substruct
referencing a table called profile_thus_payment_profile_t, which is 30 chars long. dm_oracle
appends a 5 to this, because the query contains > 1 of the same object type. This makes the
alias profile_thus_payment_profile_t5, which is 31 chars long and causes an Oracle ORA-00972 error.

2) Because the query needs to look at /account.PIN_FLD_CYCLE_FEES, it needs to reference
account_cycle_fees_t.
dm_oracle aliases account_t, account_products_t and account_cycle_fees_t in the query for the
instance of /account on which we're querying the PIN_FLD_CYCLE_FEES array. It also correctly
uses these aliases when joining account_t to each of the subtables. However, it doesn't use the
aliases in the join between the two subtables themselves, account_cycle_fees_t and
account_products_t. This results in an ORA-00904. The problem line is highlighted with a double
asterisk (**) on either side.

-- Steps To Reproduce:
1. Create a subclass on /profile with a PIN_FLD_PAYMENT substruct/array and an ENUM field on that
called THUS_FLD_CUSTOM_FLAG.
2. Give the new table for PIN_FLD_PAYMENT a name that is exactly 30 chars long, the maximum
the Oracle RDBMS allows.
3. Deploy the new class and restart.
4. Create a search flist similar to the following:
0 PIN_FLD_POID POID [0] 0.0.0.1 /search/pin 1 0
0 PIN_FLD_FLAGS INT [0] 256
0 PIN_FLD_ARGS ARRAY [1] allocated 20, used 1
1 PIN_FLD_ACCOUNT_OBJ POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [2] allocated 20, used 1
1 PIN_FLD_POID POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [3] allocated 20, used 1
1 PIN_FLD_AR_BILLINFO_OBJ POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [4] allocated 20, used 1
1 PIN_FLD_POID POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [5] allocated 20, used 1
1 PIN_FLD_ACCOUNT_OBJ POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [6] allocated 20, used 1
1 PIN_FLD_POID POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [7] allocated 20, used 1
1 PIN_FLD_ACCOUNT_OBJ POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [8] allocated 20, used 1
1 PIN_FLD_POID POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [9] allocated 20, used 1
1 PIN_FLD_PAY_TYPE ENUM [0] 10007
0 PIN_FLD_ARGS ARRAY [10] allocated 20, used 1
1 PIN_FLD_PRODUCTS ARRAY [0] allocated 20, used 1
2 PIN_FLD_CYCLE_FEES ARRAY [0] allocated 20, used 1
3 PIN_FLD_CHARGED_TO_T TSTAMP [0] (1385856000) Fri Nov 01 00:00:00 2013
0 PIN_FLD_ARGS ARRAY [11] allocated 20, used 1
1 PIN_FLD_PAYMENT SUBSTRUCT [0] allocated 20, used 1
2 THUS_FLD_CUSTOM_FLAG ENUM [0] 1
0 PIN_FLD_TEMPLATE STR [0] "select 'X' from /account 1, /billinfo 2, /account 3,
/billinfo
4, /profile/thus_payment_profile 5 where 2.F1 = 1.F2 and 2.F3 = 4.F4 and 4.F5 = 3.F6 and 5.F7 =
3.F8 and 2.F9 = V9 and 1.F10 <= V10 and 5.F11 = V11 "
0 PIN_FLD_RESULTS ARRAY [0] allocated 20, used 1
1 PIN_FLD_POID POID [0] NULL poid pointer

5. Run this search in testnap.

This should show an ORA-00972 in the dm_oracle.pinlog, error 1) in the description above.

Amend the custom subprofile so the table used by PIN_FLD_PAYMENT has less than 30 chars in its
name. Bounce the system and retry the search. This should now generate no ORA-00972, but the
dm_oracle.pinlog should now show an ORA-00904. This is error 2) from the description above.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms