Complex Opcode Search Not Working with Limiting Fields Option (PIN_FLD_MIN_ROW & PIN_FLD_MAX_ROW) (Doc ID 2722658.1)

Last updated on APRIL 10, 2024

Applies to:

Symptoms

One user is looking for an option to fetch multiple tables results (complex search) with pagination option through Billing and Revenue Management (BRM) search opcode. This is a business requirement to fetch both event and item table records with pagination option: 1-100 records first, then 101-200 records, and so on.

For pagination, the user has referred Oracle document https://docs.oracle.com/cd/E16754_01/doc.75/e16702/prg_search.htm#BRMDR1722 which has an option of using PIN_FLD_MIN_ROW and PIN_FLD_MAX_ROW fields. However, these fields are not working with complex opcode search.

ERROR in dm_oracle.pinlog:
-----------------------------
E Mon Sep  7 03:58:46 2020  Host_IP  dm:24679  dm_subr.c(173):9119 2:Host_IP:testnap:1920:-147220736:7:1599476326:1:root.0.0.0.1:::
        ORACLE error: do_sql_mr_select: PINStmtExecute: code 918, op 0
        =ORA-00918: column ambiguously defined

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Search opcode input flist:

r << 1 1
0 PIN_FLD_POID POID [0] 0.0.0.1 /search/pin -1 0
0 PIN_FLD_FLAGS INT [0] 0
0 PIN_FLD_MAX_ROW INT [0] 3
0 PIN_FLD_MIN_ROW INT [0] 1
0 PIN_FLD_TEMPLATE STR [0] " select X from /event 1, /item 2 where 1.F1 = 2.F2 AND 2.F3 = V3 and item_t.poid_type IN ( '/item/usage', '/item/adjustment') order by event_t.created_t desc "
0 PIN_FLD_ARGS ARRAY [1] allocated 20, used 1
1     PIN_FLD_ITEM_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_BILLINFO_OBJ POID [0] 0.0.0.1 /billinfo 2358771321 0
0 PIN_FLD_RESULTS ARRAY [0] allocated 20, used 1
1     PIN_FLD_POID POID [0] NULL poid pointer
1     PIN_FLD_BAL_IMPACTS ARRAY [1] allocated 22, used 22
2         PIN_FLD_AMOUNT DECIMAL [0] 0
1     PIN_FLD_LINKED_OBJ ARRAY [2] allocated 20, used 3
2         PIN_FLD_ITEM_OBJ POID [0] NULL poid pointer
2         PIN_FLD_LINK_DIRECTION ENUM [0] -1
2         PIN_FLD_EXTRA_RESULTS ARRAY [*] allocated 20, used 1
3             PIN_FLD_ITEM_NO STR [0] NULL str ptr

xop PCM_OP_SEARCH 0 1

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.