Home » SQL & PL/SQL » SQL & PL/SQL » Error while deleting partition of a table (Oracle 11g, Window 2003)
Error while deleting partition of a table [message #570978] Mon, 19 November 2012 08:21 Go to next message
jra2007
Messages: 5
Registered: November 2012
Junior Member
create or replace
Procedure          ReadingsPurge

As
   v_sql             varchar2(500);
   v_date            date;
   p_count NUMBER; 
   v_partition_name  user_tab_partitions.partition_name%TYPE;
   v_high_value      user_tab_partitions.high_value%TYPE;
   Cursor C1 Is Select Partition_Name, High_Value
               from user_tab_partitions where table_name='READING' and PARTITION_NAME!='P0'; --- P0 is the partition for the historical data
Begin
-- Code below drops partitions that are older than the NoOfDays Parameter
   OPEN c1;
  LOOP
    FETCH c1 INTO v_partition_name, v_high_value;
    EXIT WHEN c1%NOTFOUND;          
           
    v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
    
    IF v_date < (sysdate - 5) then
         dbms_output.put_line(to_char(v_date)||'==== v_date ====');
         v_Sql := 'SELECT COUNT(*)  FROM READING PARTITION(' || v_partition_name || ')';     
             
          execute immediate v_sql INTO p_count;      
          dbms_output.put_line(to_char(p_count)||'==== p_count ===='); 
       
       If ( p_count = 0) then 
           [color=red]  v_Sql := ' DELETE FROM READING PARTITION  ' || v_partition_name;
           execute immediate v_sql;                     
            Commit;[/color]       
        End if;
    END IF;
  End Loop;
  Close C1;
End ReadingsPurge;



-----------------------------------------------

Above code is compiling successfully.
After I added the lines makred in the red font, when I tried to execute the stored procedure, I got an error


Error starting at line 1 in command:
execute ReadingsPurge
Error report:
ORA-00933: SQL command not properly ended
ORA-06512: at "CDC_USER.READINGSPURGE", line 30
ORA-06512: at line 1
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

Re: Error while deleting partition of a table [message #570980 is a reply to message #570978] Mon, 19 November 2012 08:23 Go to previous messageGo to next message
jra2007
Messages: 5
Registered: November 2012
Junior Member
After I added following lines, I got an error:

v_Sql := ' DELETE FROM READING PARTITION ' || v_partition_name;
execute immediate v_sql;
Commit;
Re: Error while deleting partition of a table [message #570982 is a reply to message #570980] Mon, 19 November 2012 08:28 Go to previous message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
Proper syntax for deleting from a partition is {table_name} partition (partition_name), if that's what you are trying to do.
Instead of execute immediate, why don't you do a dbms_output to see the actual command that is running?

[edit] fixed typo in syntax.

[Updated on: Mon, 19 November 2012 08:29]

Report message to a moderator

Previous Topic: Using nvl within a sum
Next Topic: calculated column in 10g r2
Goto Forum:
  


Current Time: Fri Apr 18 17:46:22 CDT 2014

Total time taken to generate the page: 0.09472 seconds