2 Replies Latest reply on Nov 20, 2012 3:52 PM by Keith Jamieson

    Dynamic partition use

      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
          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 ;
          1 person found this helpful
          • 2. Re: Dynamic partition use
            Keith Jamieson
            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