I was contacted by the application developers to check a query which resulted in an ORA-56904 in a 12.1.0.2 Oracle Database.  The query used Pivot to select data from a partitioned table which was stored in memory (INMEMORY clause). Without the INMEMORY clause the query was running fine.

To recreate the scenario I used the following simplified test-case:

I created a 12.1.0.2 database and increased the InMemory Area of the SGA to 7GB then created a partitioned table as follows:

CREATE TABLE TESTTABLE1
( order_id integer NOT NULL,
 customer_ref varchar2(50) NOT NULL,
 order_date date,
 product_id integer,
 quantity integer,
 CONSTRAINT orders_pk PRIMARY KEY (order_id)
)
PARTITION BY RANGE (order_date)
(PARTITION orders_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
 PARTITION orders_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
 PARTITION orders_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
 PARTITION orders_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')));

I executed the select statement with Pivot:

SELECT * FROM
(
 SELECT customer_ref, product_id
 FROM testtable1
)
PIVOT
(
 COUNT(product_id)
 FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

As expected the statement was executed without any error.  Then I altered a partition of the table for In-Memory:

SQL> alter table TESTTABLE1 modify partition orders_q1 inmemory;

Table altered.

After executing the same SELECT again it failed with the following error message:

ERROR at line 1:
ORA-56904: pivot value must have datatype that is convertible to pivot column

So with this test case we proved that when using Pivot on partitioned table with INMEMORY clause we indeed get an ORA-56904 error. Now there has to be an Oracle Bug Note for this somewhere on the Metalink. The only Bug Note I found which can be related to this case is Bug 18516620 : ORA-56904 EVEN WITH PATCH FOR BUG 18449278 INSTALLED
According to this note this issue should be fixed in 12.1.0.2 but the Note did not say anything about the In-Memory feature.  One of the workarounds offered there was setting the hidden parameter: “_pivot_implementation_method” to pivot2 instead of choose. I tried this on the session level and re-executed the query:

SQL> alter session set "_pivot_implementation_method"=pivot2;

Session altered.

SQL> SELECT * FROM
(
 SELECT customer_ref, product_id
 FROM testtable1
)
PIVOT
(
 COUNT(product_id)
 FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

no rows selected

So that worked.
Another possible workaround mentioned in the note is using the SQL Hint NO_MERGE.  To test it I changed “_pivot_implementation_method” back to its original value and executed the query with the NO_MERGE SQL Hint:

SQL> alter session set "_pivot_implementation_method"=choose;

 Session altered.

SQL> SELECT /*+ NO_MERGE(TESTSEL) */ * FROM
 (
 SELECT customer_ref, product_id
 FROM testtable1
 ) TESTSEL
 PIVOT
 (
 COUNT(product_id)
 FOR product_id IN (10, 20, 30)
 )
 ORDER BY customer_ref;

no rows selected

That worked as well. To make sure that the Partition is accessed from the In-Memory part of SGA let us check the Execution Plan:

---------------------------------------------------+-----------------------------------+---------------+
| Id | Operation                  | Name      |Rows| Bytes | Cost | Time               | Pstart| Pstop |
---------------------------------------------------+-----------------------------------+---------------+
| 0  | SELECT STATEMENT           |           |    |       |    3 |                    |       |       |
| 1  | SORT GROUP BY PIVOT        |           |  1 |    40 |    3 |           00:00:01 |       |       |
| 2  | PARTITION RANGE ALL        |           |  1 |    40 |    2 |           00:00:01 |     1 |     4 |
| 3  | VIEW                       |           |  1 |    40 |    2 |           00:00:01 |       |       |
| 4  | TABLE ACCESS INMEMORY FULL | TESTTABLE1|  1 |    40 |    2 |           00:00:01 |     1 |     4 |
---------------------------------------------------+-----------------------------------+---------------+

So we can conclude that when using Pivot on a partitioned table with INMEMORY clause on any partitions we still encounter a bug. As for the workarounds setting a hidden parameter (should be avoided in most cases) or using an SQL Hint seems to work currently for Oracle 12.1.0.2.

To have more information on the issue and to ask for a patch I have opened a case with Oracle Support.

Update: 

Oracle has opened bug 23230945 for the case.