CODEWITHSUNDEEP
sqloracle-databasestored-procedurespartitioningdatabase-partitioning
user6575380
user6575380

Reputation: 7

Stored procedure input as partition name not working in oracle

This is my stored procedure and input is the partition name to be exchanged. However, the query cannot recognize my input name ('DATA_EXCHANGE_PAYLOAD_20160630') but this partition did exist in the database. 

    CREATE
        OR REPLACE PROCEDURE exchange_partitions (partition_name IN VARCHAR2) AS

    BEGIN
        EXECUTE IMMEDIATE 
    '

    ALTER TABLE BUCLM_ADAPTERDB.DATA_EXCHANGE_PAYLOAD EXCHANGE PARTITION partition_name
        WITH TABLE BUCLM_ADAPTERDB.DATA_EXCHANGE_PAYLOAD_TEMP
    ';
    END;

    exec exchange_partitions('DATA_EXCHANGE_PAYLOAD_20160630');

Upvotes: 0

Views: 601

Answers (2)

OldProgrammer
OldProgrammer

Reputation: 12169

EXECUTE IMMEDIATE  'ALTER TABLE BUCLM_ADAPTERDB.DATA_EXCHANGE_PAYLOAD 
                    EXCHANGE PARTITION :1  WITH TABLE 
                    BUCLM_ADAPTERDB.DATA_EXCHANGE_PAYLOAD_TEMP'
                    USING partition_name;

Upvotes: -1

sstan
sstan

Reputation: 36483

Your partition_name parameter is not being used. The alter table statement is literally looking for the string partition_name. This is not what you want.

To fix it, concatenate the value of partition_name into the dynamic SQL using the concatenation operator ||:

CREATE OR REPLACE PROCEDURE exchange_partitions (partition_name IN VARCHAR2) AS
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE BUCLM_ADAPTERDB.DATA_EXCHANGE_PAYLOAD
                       EXCHANGE PARTITION ' || partition_name || ' WITH
                       TABLE BUCLM_ADAPTERDB.DATA_EXCHANGE_PAYLOAD_TEMP';
END;

Upvotes: 2

Related Questions