create table deneme (part varchar2(4000))

/* Formatted on 19/03/2013 14:03:41 (QP5 v5.240.12305.39446) */
DECLARE
v_dyntask   LONG;
var         VARCHAR2 (4000);
BEGIN
FOR cur1 IN (SELECT ’01’ prt FROM DUAL
UNION ALL
SELECT ’02’ prt FROM DUAL
UNION ALL
SELECT ’03’ prt FROM DUAL
UNION ALL
SELECT ’04’ prt FROM DUAL
UNION ALL
SELECT ’11’ prt FROM DUAL
UNION ALL
SELECT ’30’ prt FROM DUAL
UNION ALL
SELECT ’31’ prt FROM DUAL
UNION ALL
SELECT ’32’ prt FROM DUAL
UNION ALL
SELECT ’50’ prt FROM DUAL
UNION ALL
SELECT ’51’ prt FROM DUAL
UNION ALL
SELECT ’52’ prt FROM DUAL
UNION ALL
SELECT ’53’ prt FROM DUAL)
LOOP
FOR CUR2
IN (SELECT prt, (CASE
WHEN prt = ‘9’ THEN ‘Z’
ELSE TO_CHAR (TO_NUMBER (prt) + 1)
END) var
FROM (SELECT ‘0’ prt FROM DUAL
UNION ALL
SELECT ‘1’ prt FROM DUAL
UNION ALL
SELECT ‘2’ prt FROM DUAL
UNION ALL
SELECT ‘3’ prt FROM DUAL
UNION ALL
SELECT ‘4’ prt FROM DUAL
UNION ALL
SELECT ‘5’ prt FROM DUAL
UNION ALL
SELECT ‘6’ prt FROM DUAL
UNION ALL
SELECT ‘7’ prt FROM DUAL
UNION ALL
SELECT ‘8’ prt FROM DUAL
UNION ALL
SELECT ‘9’ prt FROM DUAL))
LOOP
FOR cur3 IN(
SELECT ‘0’ prt FROM DUAL
UNION ALL
SELECT ‘1’ prt FROM DUAL
UNION ALL
SELECT ‘2’ prt FROM DUAL
UNION ALL
SELECT ‘3’ prt FROM DUAL
UNION ALL
SELECT ‘4’ prt FROM DUAL
UNION ALL
SELECT ‘5’ prt FROM DUAL
UNION ALL
SELECT ‘6’ prt FROM DUAL
UNION ALL
SELECT ‘7’ prt FROM DUAL
UNION ALL
SELECT ‘8’ prt FROM DUAL
UNION ALL
SELECT ‘9’ prt FROM DUAL

)

LOOP
— dbms_output.put_line(cur1.prt||’_’||cur2.var||’_’||cur3.prt);
v_dyntask := ‘ALTER TABLE  MY_TABLE ADD
PARTITION INVOICEPRINT_CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’
VALUES LESS THAN (‘||cur1.prt||’, ‘||cur2.var||’, ‘||cur3.prt||’)
NOLOGGING
NOCOMPRESS
TABLESPACE SAMPLE
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
BUFFER_POOL      DEFAULT
FLASH_CACHE      DEFAULT
CELL_FLASH_CACHE DEFAULT
)
( SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_00_09 VALUES (”00”, ”01”, ”02”, ”03”, ”04”, ”05”, ”06”, ”07”, ”08”, ”09”)      TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_10_19 VALUES (”10”, ”11”, ”12”, ”13”, ”14”, ”15”, ”16”, ”17”, ”18”, ”19”)      TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_20_29 VALUES (”20”, ”21”, ”22”, ”23”, ”24”, ”25”, ”26”, ”27”, ”28”, ”29”)      TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_30_39 VALUES (”30”, ”31”, ”32”, ”33”, ”34”, ”35”, ”36”, ”37”, ”38”, ”39”)      TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_40_49 VALUES (”40”, ”41”, ”42”, ”43”, ”44”, ”45”, ”46”, ”47”, ”48”, ”49”)      TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_50_59 VALUES (”50”, ”51”, ”52”, ”53”, ”54”, ”55”, ”56”, ”57”, ”58”, ”59”)      TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_60_69 VALUES (”60”, ”61”, ”62”, ”63”, ”64”, ”65”, ”66”, ”67”, ”68”, ”69”)      TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_70_79 VALUES (”70”, ”71”, ”72”, ”73”, ”74”, ”75”, ”76”, ”77”, ”78”, ”79”)      TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_80_89 VALUES (”80”, ”81”, ”82”, ”83”, ”84”, ”85”, ”86”, ”87”, ”88”, ”89”)      TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_90_99 VALUES (”90”, ”91”, ”92”, ”93”, ”94”, ”95”, ”96”, ”97”, ”98”, ”99”)      TABLESPACE SAMPLE ) ‘;

–dbms_output.put_line(v_dyntask);
–EXECUTE IMMEDIATE v_dyntask;
insert into deneme values (v_dyntask);
commit;

END LOOP;

END LOOP;

end loop;

end;

Reklamlar