create or replace procedure sp_dw_launchdate(
i_startday in number,
i_endday in number,
o_msg out varchar2
) is
v_partname varchar2(30);
v_fornum number;
v_str varchar2(300);
date_error exception;
begin
if i_startday<i_endday then
for v_fornum in i_startday..i_endday loop
v_partname:='part_'||to_char(v_fornum);
v_str:='alter table tb_dw_launchdate add partition '||v_partname|| ' values('''||v_fornum||''')';
dbms_output.put_line(v_str);
execute immediate v_str;
o_msg:='执行成功！';
end loop;
end if;
if i_startday>=i_endday then
raise date_error;
end if;
--向表tb_dw_ticketdate插入数据
create table tb_dw_launchtemp as select * from tb_dw_newlaunchdata where 1<>1;
execute immediate 'insert into tb_dw_launchtemp select * from tb_dw_newlaunchdata n where n.insertdate='||i_endday;
alter table tb_dw_launchtemp drop column ticketdate;
insert into tb_dw_launchdate select * from tb_dw_launchtemp;
drop table tb_dw_launchtemp;
commit;
--异常处理
exception
when date_error then
o_msg := '时间参数输入错误！结束时间必须大于开始时间！！';
when others then
--记录异常信息
o_msg := '执行失败！ ';
rollback;
end sp_dw_launchdate;
PROCEDURE SCOTT.SP_DW_LAUNCHDATE 编译错误
错误：PLS-00103: 出现符号 "CREATE"在需要下列之一时：
( begin case declare end
exception exit for goto if loop mod null pragma raise return
select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
行：28
文本：create table tb_dw_launchtemp as select * from tb_dw_newlaunchdata where 1<>1;
------解决方案--------------------
把DDL放进字符串
使用execute immediate来执行
------解决方案--------------------
create table tb_dw_launchtemp as select * from tb_dw_newlaunchdata where 1<>1;
alter table tb_dw_launchtemp drop column ticketdate;
drop table tb_dw_launchtemp;
ddl语句都要放到动态sql中的
execute immediate 'create table tb_dw_launchtemp as select * from tb_dw_newlaunchdata where 1<>1';
execute immediate 'alter table tb_dw_launchtemp drop column ticketdate';
execute immediate 'drop table tb_dw_launchtemp';