Call Support +91-85588-96644
TCYonline

Login

Sign Up

Please enter a Username or Email ID
Please enter a password
Keep me logged in
Please enter your name
Please enter your mobile number
You can't leave Captcha Code empty
By submitting this form, you agree to the Terms & Privacy Policy.
OR

Sign Up via Facebook

Sign Up via Google

Sign Up via Twitter

Download Software
Tests given

Download TCY App

App Image

Hey frnds i need ur help

I have following create table command with partitions on the table.

CREATE TABLE PARTITION_TEST
(
COUNTRY_KEY NVARCHAR2(3),
COMPANY_KEY NVARCHAR2(5),
SITE_KEY NVARCHAR2(10),
HFM_BU_KEY NVARCHAR2(30),
DAY_DATE DATE,
METRIC_KEY NVARCHAR2(13),
TRANSACTION_VALUE NUMBER
)
PARTITION BY RANGE (DAY_DATE ) (
PARTITION P_1_2013 VALUES LESS THAN(TO_DATE('1/31/2013','MM/DD/YYYY')),
PARTITION P_2_2013 VALUES LESS THAN( TO_DATE('2/28/2013','MM/DD/YYYY')),
PARTITION P_3_2013 VALUES LESS THAN( TO_DATE('3/31/2013','MM/DD/YYYY')),
PARTITION P_4_2013 VALUES LESS THAN( TO_DATE('4/30/2013','MM/DD/YYYY')),
PARTITION P_5_2013 VALUES LESS THAN( TO_DATE ('5/31/2013','MM/DD/YYYY')),
PARTITION P_6_2013 VALUES LESS THAN( TO_DATE('6/30/2013','MM/DD/YYYY')),
PARTITION P_7_2013 VALUES LESS THAN( TO_DATE('7/31/2013','MM/DD/YYYY')),
PARTITION P_8_2013 VALUES LESS THAN( TO_DATE('8/31/2013','MM/DD/YYYY')),
PARTITION P_9_2013 VALUES LESS THAN( TO_DATE('9/30/2013','MM/DD/YYYY')),
PARTITION P_10_2013 VALUES LESS THAN( TO_DATE('10/31/2013','MM/DD/YYYY')),
PARTITION P_11_2013 VALUES LESS THAN( TO_DATE('11/30/2013','MM/DD/YYYY')),
PARTITION P_12_2013 VALUES LESS THAN( TO_DATE('12/31/2013','MM/DD/YYYY'))
);

And I am creating a procedure to add partition(P_12_2014) on the table.
Can you please help.
Problem is in alter table query I am unable to pass date in a variable. This gives error.
Do you know how should I do it?


CREATE OR REPLACE procedure fact_partition_test
AS
TODAY_YEAR number(4) := 0;
TODAY_MONTH number(2) := 0;
PREVIOUS_MONTH number(2) := 0;
TODAY_DAY number(2) := 0;
V_STAT VARCHAR2(300);
V_STAT1 VARCHAR2(300);
V_STAT2 VARCHAR2(300);

PARTITION_NAME_CURRENT VARCHAR2(12);
PARTITION_NAME_PREVIOUS VARCHAR2(12);
RANGE_PARTITION_CURRENT DATE;
RANGE_PARTITION_PREVIOUS DATE;


BEGIN


PARTITION_NAME_CURRENT := ‘P_12_2014’
SELECT TRUNC(LAST_DAY(‘1/JAN/2014’)) INTO RANGE_PARTITION_CURRENT FROM DUAL

V_STAT2 := ‘ALTER TABLE PARTITION_TEST ADD PARTITION ’ || PARTITION_NAME_CURRENT || ‘ VALUES LESS THAN (TO_DATE(' || RANGE_PARTITION_CURRENT || ‘,'MM/DD/YYYY'))’;
EXECUTE IMMEDIATE V_STAT2;

END fact_partition_test;
/

Reply


Please type your answer before submitting.
Submit