Re: add a new range partition in a existing table

From: sansar <uemit.uenlue_at_googlemail.com>
Date: Wed, 17 Oct 2007 07:45:50 -0700
Message-ID: <1192632350.246052.105570@e34g2000pro.googlegroups.com>

On 17 Okt., 16:25, Carlos <miotromailcar..._at_netscape.net> wrote:
> On 17 oct, 15:49, sansar <uemit.uen..._at_googlemail.com> wrote:
>
>
>
> > Hallo !
> > My PL/SQL -Script send me a Error-Message.
> > I don't know how i find the error.
> > i want add a new range partition in a existing table ?
>
> > eclare
> > v_date date;
> > v_part varchar2(20);
>
> > CURSOR context_date_cur IS
> > Select distinct ext_frontend.context_date
> > from ext_frontend;
>
> > begin
> > -- Test statements here
> > IF NOT context_date_cur%ISOPEN
> > THEN
> > OPEN context_date_cur;
> > END IF;
>
> > LOOP
>
> > FETCH context_date_cur into v_date;
> > exit when context_date_cur%NOTFOUND;
> > DBMS_OUTPUT.put_line(to_char(v_date));
> > v_part :='part_'||(to_char(v_date));
> > DBMS_OUTPUT.put_line(v_part);
> > execute immediate 'alter table LOG_ANALYSE.FRONTEND_TAB
> > add partition v_part
> > values less than 1+to_date(v_date, DD.MM.YYYY); ';
>
> > end loop;
> > CLOSE context_date_cur;
>
> > end;
>
> Whithout error message, a shoot in the dark but...
>
> Escape single ' characters for the date_format, perhaps?
>
> values less than 1+to_date(v_date, ''DD.MM.YYYY'') ';
>
> HTH
>
> Cheers.
>
> Carlos.

i have change the script, but i get this error message: ORA-00906: missing left parenthesis
ORA-06512: at line 25

i execute the changing script:

• Created on 17.10.2007 by B018469 declare v_date varchar2(20); v_part varchar2(20);

   CURSOR context_date_cur IS
   Select distinct ext_frontend.context_date    from ext_frontend;

begin

• Test statements here IF NOT context_date_cur%ISOPEN THEN OPEN context_date_cur; END IF;

  LOOP   FETCH context_date_cur into v_date;
  exit when context_date_cur%NOTFOUND;
  DBMS_OUTPUT.put_line(to_char(v_date));   v_part :='part_'||(to_char(v_date));
  DBMS_OUTPUT.put_line(v_part);
  execute immediate 'alter table LOG_ANALYSE.FRONTEND_TAB add partition v_part values less than 1+to_date(v_date, "dd.mm.yyyy");';   end loop;
  CLOSE context_date_cur;

end; Received on Wed Oct 17 2007 - 09:45:50 CDT