Call Support +91-85588-96644
TCY online

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

OR
App Image
Discussion

Library and Information science

Posted on 11 Dec, 2013 1:24 PM

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