sansar wrote:
> 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||'
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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Oct 18 2007 - 10:05:38 CDT
