CODEWITHSUNDEEP
sqloracle-databaseplsqlpartition
pthfndr2007
pthfndr2007

Reputation: 103

Oracle rename partition if exists

I need to create a statement that can be run to rename a partition only if specific partition name exists and if not then continue on to execute other code.

basic command  = ALTER TABLE TEST RENAME PARTITION P1 TO P2:

I have looked at the following but have not come up with a solution

Upvotes: 0

Views: 1252

Answers (3)

Robert Kock
Robert Kock

Reputation: 6028

You could check whether the partition exists within the table USER_TAB_PARTITIONS:

DECLARE
  v_p1_exists AS NUMBER;
  v_p2_exists AS NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO v_p1_exists
    FROM user_tab_partitions
   WHERE table_name = 'TEST'
     AND partition_name = 'P1';
  SELECT COUNT(*)
    INTO v_p2_exists
    FROM user_tab_partitions
   WHERE table_name = 'TEST'
     AND partition_name = 'P2';
   IF (v_p1_exists <> 0) AND (v_p2_exists = 0) THEN
     EXECUTE IMMEDIATE 'ALTER TABLE TEST RENAME PARTITION P1 TO P2';
   END;
END;

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59513

I depends on your requirements but a basic procedure would be this one:

DECLARE
    PARTITION_DOES_NOT_EXIST EXCEPTION;
    PRAGMA EXCEPTION_INIT(PARTITION_DOES_NOT_EXIST, -2149); 
BEGIN

    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE TEST RENAME PARTITION P1 TO P2';
    EXCEPTION 
        WHEN PARTITION_DOES_NOT_EXIST THEN NULL;
    END;

    ... ohter commands
END;

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142958

One option is to enclose ALTER TABLE into its own BEGIN-END block, with appropriate exception handling section. Mine is kind of stupid (WHEN OTHERS, eh?); feel free to rewrite it to be meaningful - it is just to show how to do it.

So: if ALTER TABLE fails, it'll raise some error; it'll be captured, handled, and code will continue its execution.

begin
  begin
    execute immediate 'alter table test rename partition p1 to p2';
  exception
    when others then 
      -- ignore errors
      null;
  end;

  -- continue to execute other code
  ...
end;

Upvotes: 0

Related Questions