DECLARE
v_cmd3 VARCHAR2 (1024);
v_date1 VARCHAR2 (1024);
BEGIN
v_date1 := TO_CHAR (SYSDATE, 'yyyy-MM-dd');
v_cmd3 :=
'
PARTITION BY RANGE (REPORT_TIME)
(
PARTITION mr_data_pt02 VALUES LESS THAN (TIMESTAMP'''
|| v_date1
|| ' 02:00:00'
|| ''')
);';
DBMS_OUTPUT.put_line ( 'create table amr.aaa(REPORT_TIME TIMESTAMP(9))'
|| v_cmd3
); EXECUTE IMMEDIATE ( 'create table amr.aaa(REPORT_TIME TIMESTAMP(9))'
|| v_cmd3
);
END;
v_cmd3 VARCHAR2 (1024);
v_date1 VARCHAR2 (1024);
BEGIN
v_date1 := TO_CHAR (SYSDATE, 'yyyy-MM-dd');
v_cmd3 :=
'
PARTITION BY RANGE (REPORT_TIME)
(
PARTITION mr_data_pt02 VALUES LESS THAN (TIMESTAMP'''
|| v_date1
|| ' 02:00:00'
|| ''')
);';
DBMS_OUTPUT.put_line ( 'create table amr.aaa(REPORT_TIME TIMESTAMP(9))'
|| v_cmd3
); EXECUTE IMMEDIATE ( 'create table amr.aaa(REPORT_TIME TIMESTAMP(9))'
|| v_cmd3
);
END;
DECLARE
v_cmd3 VARCHAR2 (1024);
v_date1 VARCHAR2 (1024);
BEGIN
v_date1 := TO_CHAR (SYSDATE, 'yyyy-MM-dd');
v_cmd3 :=
'
PARTITION BY RANGE (REPORT_TIME)
(
PARTITION mr_data_pt02 VALUES LESS THAN (TIMESTAMP'''
|| v_date1
|| ' 02:00:00'
|| ''')
);'; --去掉第一个分号 改成 )'; DBMS_OUTPUT.put_line ( 'create table amr.aaa(REPORT_TIME TIMESTAMP(9))'
|| v_cmd3
); EXECUTE IMMEDIATE ( 'create table amr.aaa(REPORT_TIME TIMESTAMP(9))'
|| v_cmd3
);
END;
打印出来的是下面内容,我考出来是可以执行的。
create table amr.aaa(REPORT_TIME TIMESTAMP(9))
PARTITION BY RANGE (REPORT_TIME)
(
PARTITION mr_data_pt01 VALUES LESS THAN (TIMESTAMP'2011-08-11 01:00:00'),
PARTITION mr_data_pt02 VALUES LESS THAN (TIMESTAMP'2011-08-11 02:00:00')
);
execute immediate('
create table scott.ccc(REPORT_TIME TIMESTAMP(9))
PARTITION BY RANGE (REPORT_TIME)
(
PARTITION mr_data_pt01 VALUES LESS THAN (TIMESTAMP''2011-08-11 01:00:00''),
PARTITION mr_data_pt02 VALUES LESS THAN (TIMESTAMP''2011-08-11 02:00:00'')
)');
end;
/declare
v varchar2(4000);
rq1 TIMESTAMP:=TIMESTAMP '2011-08-11 01:00:00';
rq2 TIMESTAMP:=TIMESTAMP '2011-08-11 02:00:00';
begin
v:='create table scott.bb(REPORT_TIME TIMESTAMP(9))
PARTITION BY RANGE (REPORT_TIME)
(
PARTITION mr_data_pt01 VALUES LESS THAN ('''||rq1||'''),
PARTITION mr_data_pt02 VALUES LESS THAN ('''||rq2||''')
)';
execute immediate v ;
end;
/