CODEWITHSUNDEEP
sqloracle-database
Roger
Roger

Reputation: 95

Insert into table using for loop index

trying to insert data into table using for loop, for table customer i have data into 3 partition.

here is the code

begin
FOR var_1 in 1 .. 3
LOOP
INSERT into tmp_table
(
        SELECT cust_no FROM customer PARTITION (customer_PR$var_1)
        WHERE city='ba' AND first_name='john'
        AND salary=1000);
commit;
END LOOP;
END;
/

I get result as

Error report -
ORA-02149: Specified partition does not exist
ORA-06512: at line 4
02149. 00000 -  "Specified partition does not exist"
*Cause:    Partition not found for the object.
*Action:   Retry with correct partition name.

but when I run normally(for partition 1),

SELECT cust_no FROM customer PARTITION (customer_PR1);

I get expected output.

Upvotes: 0

Views: 1494

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

You don't need 3 different queries, just use PARTITION() clause properly - specify all the partition needed:

    INSERT into tmp_table
        SELECT cust_no 
        FROM customer PARTITION (customer_PR1,customer_PR3,customer_PR3)
        WHERE city='ba' AND first_name='john'
        AND salary=1000;

And if you really need dynamic sql, you can concatenate your query:

begin
FOR var_1 in 1 .. 3
LOOP
  execute immediate q'[
    INSERT into tmp_table
        SELECT cust_no FROM customer PARTITION (customer_PR]'||var_1||q'[
        WHERE city='ba' AND first_name='john'
        AND salary=1000)
        ]';
  commit;
END LOOP;
END;
/

or depending on your partitioning rules, you can use partition for clause:

begin
FOR partitioning_key in {your_partitioning keys}
LOOP
INSERT into tmp_table
(
        SELECT cust_no FROM customer PARTITION FOR (partitioning_key)
        WHERE city='ba' AND first_name='john'
        AND salary=1000);
commit;
END LOOP;
END;
/

https://www.oracle.com/technical-resources/articles/database/sql-11g-partitioning.html

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

I think you need dynamic SQL here to achieve your goal -

Declare
       sql_text varchar2(500);
begin
     FOR var_1 in 1 .. 3
     LOOP
         sql_text := 'INSERT into tmp_table
                      (SELECT cust_no FROM customer PARTITION (customer_PR' || var_1 || ')
                        WHERE city=''ba'' AND first_name=''john''
                          AND salary=1000)';
         EXECUTE IMMEDIATE sql_text;
         COMMIT;
     END LOOP;
END;
/

Upvotes: 2

Related Questions