ORA-01008: not all variables bound after database upgrade

Question

I've got an Oracle Error (ORA-01008): the thing is that this error appears for the first time, with no modification of table/Schema (but there was a database upgrade). I tried execute immediate, but I still get the same error.

SET serveroutput ON;
set define off;
--alter session set "_subquery_pruning_enabled"=false; 
DECLARE
  z_value NUMBER      := -1;
  CURSOR listedocument
  IS
  select to_number(2310458) as coco from dual;
BEGIN
  FOR l IN listedocument
  LOOP
    BEGIN
          dbms_output.put_line (' l.coco= ' ||  l.coco );
         SELECT oa.myvalue
           INTO z_value
           FROM    schema2.table2 oa
                INNER JOIN
                   schema1.table1 ed
                ON ed.coco = oa.coco
          WHERE ed.coco =  l.coco
         ;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line ('NO_DATA_FOUND');
    WHEN OTHERS THEN
      dbms_output.put_line ('Exception de traitement ' || SQLERRM);
    END;
  END LOOP;
END; 

The problem seems to be here WHERE ed.coco = l.coco I get the error : Exception de traitement ORA-01008: not all variables bound [==>modification post message]

I get the error : ORA-01008: not all variables bound

When I use alter session set "_subquery_pruning_enabled"=false; everything works, but performance is bad.

I'm using Oracle database 11g (11.2.0.4.0).

Answer 1

It looks like you might have hit a bug. Take a look: https://community.oracle.com/thread/3894065?start=30&tstart=0

There are cases where the optimizer calls kkpap to do partition
pruning at compile-time. Sometimes partition pruning is done by
running subqueries against the table. If bind variable values are
required to run these subqueries then we cannot do the pruning at
compile-time. The fix for bug 14458214 fixed this issue for the case where the subquery was used to prune at the partition-level. However, it is
possible that we use some other method at the partition level then use subquery pruning at the subpartition level; this case was not
addressed by the fix for 14458214.