On 17 Okt., 17:39, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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
Thank you !
i'm learning pl/sql and don't know that i get the value of a variable,
when i write '||variable||'
Received on Thu Oct 18 2007 - 04:53:41 CDT
