Re: add a new range partition in a existing table

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 17 Oct 2007 08:31:18 -0700
Message-ID: <1192635068.136123@bubbleator.drizzle.com>

sansar 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;

If you want to be successful in Oracle you need to learn to read and pay attention ... you were asked for the version number. You didn't.

Where in any Oracle doc did you ever see double quotes like this?   "dd.mm.yyyy"
And what about this requires PL/SQL or a CURSOR LOOP? Also please consider that were one needed your initial IF statement does nothing as the cursor can not possibly be open. This code is Oracle 7 ... and should not be written in any version more recent than 8.1.7.4.

A basic course in PL/SQL programming would be very useful.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Oct 17 2007 - 10:31:18 CDT