user6575380 user6575380 - 4 months ago 14
SQL Question

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');

Answer

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;
Comments