This discussion is archived
2 Replies Latest reply: Nov 20, 2012 7:52 AM by KeithJamieson RSS

Dynamic partition use

a.stoyanov Newbie
Currently Being Moderated
Hi all,
In my 11g Oracle i have the following issue:
create table TEST
partition by range (COL_DATE)
  partition TEST2012 values less than (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace NRA_DWH_DATA
    pctfree 10
    initrans 1
    maxtrans 255
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
insert into  test values (sysdate);
.... and ...

a varchar2(20);
b date;

  a := 'TEST2012';
  select col_date into b from test partition(a) ;

end ;
when i execute this i receive the following error:
ORA-02149: Specified partition does not exist
Any ideas?

Thanks in advance,

Edited by: a.stoyanov on 2012-11-20 7:42
  • 1. Re: Dynamic partition use
    Dom Brooks Guru
    Currently Being Moderated
    You can't bind in the partition name.
    If you really need to do this then you'd have to use dynamic sql.

     a varchar2(20);
     b date;
      a := 'TEST2012';
      execute immediate 'select col_date from test partition('||a||')' into b;
    end ;
  • 2. Re: Dynamic partition use
    KeithJamieson Expert
    Currently Being Moderated
    Yes, you can not do that.

    You should just use the following:
    select col_date into b from test
    However this will cycle through all partitions because you have not given the optimiser any help whatsoever.

    If you said
    --explain plan for
    select col_date from test where col_date >=to_date('01-01-2012','DD-MM-YYYY') 
    and col_date < to_date('01-01-2013','DD-MM-YYYY') 
    then the optimiser will use the partition.

    To see this just remove '--' from before explain plan for '
    and then
    select * from table(dbms_xplan.display());
    You should see the partition in your output.

    Your previous query will show it is iterating through all the partitions.

    Edited by: Keith Jamieson on Nov 20, 2012 3:52 PM
    removed into


  • Correct Answers - 10 points
  • Helpful Answers - 5 points