RSS Feed

Method R Discussion Group

we're on Oracle 9.2.0.6, UNIX AIX. Thinking I hit bug 1702058 I changed do_v9.sql to use CAST. Neither work. Can
anyone help?
SQL>
SQL> insert into  hscenario_plans
  2  select &htst_scenario_id , hseq.nextval , a.*
  3    from table(dbms_xplan.display('PLAN_TABLE','&htst_stmt_id','ALL')) a;
  from table(dbms_xplan.display('PLAN_TABLE','dand:first','ALL')) a
                *
ERROR at line 3:
ORA-22905: cannot access rows from a non-nested table item

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    RESULTs using CAST
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> --insert into  hscenario_plans
SQL> --select &htst_scenario_id , hseq.nextval , a.*
SQL> --  from table(dbms_xplan.display('PLAN_TABLE','&htst_stmt_id','ALL')) a;
SQL>
SQL> -- If hit v9 bug 1702058, need to change to add a cast call
SQL> -- Comment previous insert and uncomment this one
SQL> insert into  hscenario_plans
  2  select &htst_scenario_id , hseq.nextval , a.*
  3  from table(cast(dbms_xplan.display('PLAN_TABLE','&htst_stmt_id','ALL') AS dbms_xplan_type_table)) a;
 from table(cast(dbms_xplan.display('PLAN_TABLE','dand:first','ALL') AS dbms_xplan_type_table)) a
                                                                                  *
ERROR at line 3:
ORA-00902: invalid datatype

I believe the issue is related to the setting for your CURSOR_SHARING parameter.  This is part of the bug and the workaround is that you have to make sure CURSOR_SHARING = EXACT. 

You can edit the file named binds.sql (which you would create in the default directory where you keep the .sql files you'll be testing using the harness) and add the proper ALTER SESSION command to SET CURSOR_SHARING = EXACT.

Try that and see if that corrects the problem.

Thanks. I think that worked. Doing more testing