APEX

APEX 24.2.15

I have the following piece of PL/SQL:

      l_query := 'SELECT gtpm.mepo_id as n001
                      ,address_confidence_score as n002
                      ,to_number(lukcat_beds) as n003
                      ,0 as n004
                      ,0 as n005
                      ,sysdate as d001
                      ,sysdate as d002
                      ,sysdate as d003
                      ,sysdate as d004
                      ,sysdate as d005
                      ,lukcat_housetype5 as c001
                FROM   gtt_pma gtpm
                JOIN   ia_address_requests iaar
                ON     iaar.rowid = gtpm.iaar_rowid
                JOIN   caci_addresses caci
                ON     caci.uprn_key = iaar.uprn
                WHERE  gtpm.status = :status
                AND    iaar.candidate = :candidate';
    
      apex_collection.create_collection_from_queryb2(p_collection_name    => $$PLSQL_UNIT ||
                                                                             '$CACI'
       ,p_query              => l_query
       ,p_names              => apex_string.string_to_table('status:candidate')
       ,p_values             => apex_string.string_to_table(gc_am_success_status || ':' ||
                                                           sys_context('ADDR_MATCH_CTX',CANDIDATE'))                                                                                      
       ,p_truncate_if_exists => 'YES');

This produces a runtime error:

ORA-20104: create_collection_from_queryb2 Error:ORA-20104: create_collection_from_query ParseErr:ORA-00904: "LUKCAT_HOUSETYPE5": invalid identifier

Table gtt_pma contains:

Name                     Type               Nullable Default Comments 
------------------------ ------------------ -------- ------- -------- 
UPRN                     NUMBER                                       
...
IAAR_ROWID               ROWID                                        
...                         
ADDRESS_CONFIDENCE_SCORE NUMBER             Y                         
STATUS                   VARCHAR2(30)       Y                         
...

Table IA_ADDRESS_REQUESTS contains:

Name             Type          Nullable Default Comments 
---------------- ------------- -------- ------- -------- 
...
UPRN             NUMBER        Y                         
...                       
CANDIDATE        VARCHAR2(20)  Y   

Table CACI_ADDRESSES contains:

Name              Type         Nullable Default                    Comments 
----------------- ------------ -------- -------------------------- -------- 
UPRN_KEY          NUMBER(12)                                                
...
LUKCAT_BEDS       VARCHAR2(1)  Y                                            
LUKCAT_HOUSETYPE5 VARCHAR2(1)  Y                                            
...

If I change the query to work with create_collection_from_query I get the same error.

There are no errors if I run the same query outside of create_collection_queryb2 and populate the collection with add_member.

I'd prefer to use create_collection_queryb2 for performance reasons.

I use the same create_collection_queryb2 mechanism in another part of the code that joins GTT_PMA and IA_ADDRESS_REQUESTS with a different ‘addresses’ table and this runs without error.

Any ideas?