dbTalk Databases Forums  

add a new range partition in a existing table

Go Back dbTalk Databases Forums Databases comp.databases.oracle.misc add a new range partition in a existing table

comp.databases.oracle.misc comp.databases.oracle.misc


Discuss add a new range partition in a existing table in the comp.databases.oracle.misc forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
sansar
 
Posts: n/a

Default add a new range partition in a existing table - 10-17-2007 , 08:49 AM





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;


Reply With Quote
  #2  
Old   
Ruud de Koter
 
Posts: n/a

Default Re: add a new range partition in a existing table - 10-17-2007 , 09:20 AM





sansar wrote:
Quote:
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;

Hi Sansar,

Let me join the chorus: Oracle version and Edition would help here, as
would the exact error message.

Regards,

Ruud de Koter.


Reply With Quote
  #3  
Old   
Carlos
 
Posts: n/a

Default Re: add a new range partition in a existing table - 10-17-2007 , 09:25 AM


On 17 oct, 15:49, sansar <uemit.uen... (AT) googlemail (DOT) com> wrote:
Quote:
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.



Reply With Quote
  #4  
Old   
sansar
 
Posts: n/a

Default Re: add a new range partition in a existing table - 10-17-2007 , 09:45 AM


On 17 Okt., 16:25, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:
Quote:
On 17 oct, 15:49, sansar <uemit.uen... (AT) googlemail (DOT) 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;



Reply With Quote
  #5  
Old   
DA Morgan
 
Posts: n/a

Default Re: add a new range partition in a existing table - 10-17-2007 , 10:31 AM


sansar wrote:
Quote:
On 17 Okt., 16:25, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:
On 17 oct, 15:49, sansar <uemit.uen... (AT) googlemail (DOT) 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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org


Reply With Quote
  #6  
Old   
fitzjarrell@cox.net
 
Posts: n/a

Default Re: add a new range partition in a existing table - 10-17-2007 , 10:39 AM


On Oct 17, 9:45 am, sansar <uemit.uen... (AT) googlemail (DOT) com> wrote:
Quote:
On 17 Okt., 16:25, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:





On 17 oct, 15:49, sansar <uemit.uen... (AT) googlemail (DOT) 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



Reply With Quote
  #7  
Old   
sansar
 
Posts: n/a

Default Re: add a new range partition in a existing table - 10-18-2007 , 02:21 AM


On 17 Okt., 17:31, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
Quote:
sansar wrote:
On 17 Okt., 16:25, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:
On 17 oct, 15:49, sansar <uemit.uen... (AT) googlemail (DOT) 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
damor...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production



Reply With Quote
  #8  
Old   
sansar
 
Posts: n/a

Default Re: add a new range partition in a existing table - 10-18-2007 , 04:53 AM


On 17 Okt., 17:39, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:
Quote:
On Oct 17, 9:45 am, sansar <uemit.uen... (AT) googlemail (DOT) com> wrote:



On 17 Okt., 16:25, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:

On 17 oct, 15:49, sansar <uemit.uen... (AT) googlemail (DOT) 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||'



Reply With Quote
  #9  
Old   
DA Morgan
 
Posts: n/a

Default Re: add a new range partition in a existing table - 10-18-2007 , 10:05 AM


sansar wrote:
Quote:
On 17 Okt., 17:39, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:
On Oct 17, 9:45 am, sansar <uemit.uen... (AT) googlemail (DOT) com> wrote:



On 17 Okt., 16:25, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:
On 17 oct, 15:49, sansar <uemit.uen... (AT) googlemail (DOT) 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||'
SQL> set serveroutput on

DECLARE
myvar VARCHAR2(20);
BEGIN
myvar := 'TEST';
dbms_output.put_line(myvar);
END;
/

Demos of this and a huge amount of other Oracle functionality can be
found here:

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


Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top