Re: add a new range partition in a existing table

On Oct 17, 9:45 am, sansar <uemit.uen..._at_googlemail.com> wrote:
> 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;- Hide quoted text -
>
> - Show quoted text -

The 'execute immediate' text is not going to produce what you expect it to the way you have it written. I believe it should be this:

   execute immediate 'alter table LOG_ANALYSE.FRONTEND_TAB add  partition '||v_part||' values less than 1+to_date('''||v_date||''', "dd.mm.yyyy");';

You want the partition name to be the assigned value of the v_part variable, not V_PART. Also I think you want the value of v_date to be converted.

These changes MAY fix your problem, they may not. They will ensure you get a partition named the way you intended, covering the range you expect.

David Fitzjarrell Received on Wed Oct 17 2007 - 10:39:41 CDT