ORA-20104: create_collection_from_query

Apex collections are memory structures placed directly in memory. So this is the only solutions if you want to work with data in a fastest way when you work with Apex. In all other Oracle apps, global temporary tables is one of the most used similar feature.

The problem

If you work with Apex and use collection, which is base for some other objects (i.e. view) placed in other schema then WWV_FLOW_xxx, where collections reside, you'll probably suffer this problem. Here is reproducing part.
In mine case there was a pipeline function which query data in TOOLS schema. This function is used for creating collection from query. Lately, Apex collection is used to create view which is again in TOOLS schema.

Pipelined function is inside define as:

FUNCTION grow_hist_one_owner_pr (p_owner IN VARCHAR2
           ) RETURN DB_SIZE_HIST_TBLSPC_SET_T PIPELINED;

View is defined as:

CREATE OR REPLACE FORCE VIEW TOOLS.HIST_ONE_SEGMENT_VW (
   OWNER,
   SEGMENT_NAME,
   SEGMENT_TYPE,
   PERIOD,
   DB_SHARE,
   DB_REC_SHARE,
   SIZE_MB,
   TREND_MB,
   TREND_SIZE_PERC,
   CUMUL_SIZE_PERC,
   RECORDS,
   TREND_REC,
   TREND_REC_PERC,
   CUMUL_REC_PERC,
   REC_IN_MB
)
AS
   SELECT C001 OWNER,
          C002 SEGMENT_NAME,
          C003 SEGMENT_TYPE,
          C004 PERIOD,
          TO_NUMBER (C005) DB_SHARE,
          TO_NUMBER (C006) DB_REC_SHARE,
          TO_NUMBER (C007) SIZE_MB,
          TO_NUMBER (C008) TREND_MB,
          TO_NUMBER (C009) TREND_SIZE_PERC,
          TO_NUMBER (C010) CUMUL_SIZE_PERC,
          TO_NUMBER (C011) RECORDS,
          TO_NUMBER (C012) TREND_REC,
          TO_NUMBER (C013) TREND_REC_PERC,
          TO_NUMBER (C014) CUMUL_REC_PERC,
          DECODE (TO_NUMBER (C007),
                  0, 0,
                  TO_NUMBER (C011) / TO_NUMBER (C007))
             REC_IN_MB
     FROM apex_collections
    WHERE collection_name = 'HIST_ONE_SEGMENT_COLL';

In Apex Page process, Before Header, you have code like:

IF APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name=>'HIST_ONE_SEGMENT_COLL') THEN
  APEX_COLLECTION.DELETE_COLLECTION (p_collection_name=>'HIST_ONE_SEGMENT_COLL');
END IF;

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
      p_collection_name => 'HIST_ONE_SEGMENT_COLL',
      p_query => 'SELECT * SELECT * FROM TABLE( db_size_pkg.grow_hist_one_segment_pr'
      );

whose purpose is to create Apex collection and fill data from CREATE_COLLECTION_FROM_QUERY method into it.
Later, HIST_ONE_SEGMENT_VW view has data based on Apex collection and it is used to define ordinary Apex report as:

select * from HIST_ONE_SEGMENT_VW 

So, when you firstly start Apex form that use this view, you'll get an error like:

ORA-20104: create_collection_from_query Error: ORA-20104: create_collection_from_query ExecErr:ORA-01007: variable not in select list

Even thought there are many explanations why and how this happened, all focus on some security problems when you deal with object in one schema and collections in other. Mine intention is not to cover the cause (frankly I do not have enough publicly related information for that) but to how how to make a workaround for that.

The solution

The solution is very easy-force twice same action to execute and silent exceptions. Here is the code.

BEGIN
  APEX_COLLECTION.DELETE_COLLECTION (p_collection_name=>'HIST_ONE_SEGMENT_COLL');
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

BEGIN
  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
        p_collection_name => 'HIST_ONE_SEGMENT_COLL',
        p_query => 'SELECT * FROM ... '
        );
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
            p_collection_name => 'HIST_ONE_SEGMENT_COLL',
            p_query => 'SELECT * FROM ... '
            );
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
END;

The end

I do not want to mention that global temporary tables are not usable in Apex, so collections are the only one left but to encourage all to use even more collections in Apex, because they rocks!

Cheers!