CODEWITHSUNDEEP
sqloracle-database
user6519393
user6519393

Reputation:

changing partition name dynamically

I have this query:

INSERT INTO table1 
SELECT * FROM table partition partition1

I would like to make a for loop or something that would increment to the next partition automatically after the end of each iteration.

Upvotes: 0

Views: 557

Answers (1)

TenG
TenG

Reputation: 4004

Using dynamic SQL, query user_tab_partitions to get the partition names, then loop to construct the query with the returned partition names, and execute the SQL. I'm a little rusty, but something like:

DECLARE
   CURSOR cPart IS
      SELECT partition_name
      FROM user_tab_partitions
      WHERE table_name = 'THE_TABLE';
   vSql VARCHAR2(200);
BEGIN
   FOR rPart in cPart LOOP
      vSQL := 'INSERT INTO table1 SELECT * FROM the_table partition ' || rPart.partition_name;
      EXECUTE IMMEDIATE ( vSQL );
   END LOOP;
END;
/

Upvotes: 1

Related Questions