CODEWITHSUNDEEP
oracle11gpartitioning
user2570205
user2570205

Reputation: 137

Creating monthly partition on varchar field

Experts, I have the following code to create monthly partition on VARCHAR field.

  SELECT COUNT(*) INTO FCNT FROM USER_TABLES WHERE TABLE_NAME = 'WORK_RECON_T';

    IF ( FCNT = 1 ) THEN
        DBMS_OUTPUT.PUT_LINE('DROPPING TABLE WORK_RECON_T');
        EXECUTE IMMEDIATE 'DROP TABLE WORK_RECON_T';
        DBMS_OUTPUT.PUT_LINE('DROPPED TABLE WORK_RECON_T');
    END IF;

    EXECUTE IMMEDIATE 'CREATE TABLE WORK_RECON_T (
            ANI VARCHAR (255),
            COMPANYID   VARCHAR (255),
            DIVISION    VARCHAR (255),
            TIMESTAMP   TIMESTAMP,
            OMCPROCESSTIME  VARCHAR (255),
vRange number GENERATED ALWAYS AS
      (to_number(OMCPROCESSTIME)))
partition by range(vRange)
INTERVAL(100)
(partition empty values less than (20160101) )';

The data in OMCPROCESSTIME looks like this 20160718094020. I wasn't able to create partition with this command. Could you please throw some light , if this is right way to do ?

Upvotes: 0

Views: 191

Answers (1)

XING
XING

Reputation: 9886

Please try the below mentioned.

declare

v_sql varchar2(2000);

begin

 IF ( FCNT = 1 ) THEN
    DBMS_OUTPUT.PUT_LINE('DROPPING TABLE WORK_RECON_T');
    EXECUTE IMMEDIATE 'DROP TABLE WORK_RECON_T';
    DBMS_OUTPUT.PUT_LINE('DROPPED TABLE WORK_RECON_T');
END IF; 

v_sql:='CREATE TABLE WORK_RECON_T (
            ANI VARCHAR (255),
            COMPANYID   VARCHAR (255),
            DIVISION    VARCHAR (255),
            TIMESTAMP   TIMESTAMP,
            OMCPROCESSTIME  VARCHAR (255),
            vRange number GENERATED ALWAYS AS (to_number(OMCPROCESSTIME)))
partition by range(vRange)
INTERVAL(100)
(partition empty values less than (20160101) )';

execute immediate v_sql;

end;

Upvotes: 1

Related Questions