on commit PRESERVE rows as select distinct loaddate from o_t_cigsum_gen@ol_jlb where loaddate > v_maxloaddate and loaddate < p_todate; 用的什么版本?Oracle好像不支持distinct关键字吧
str:='create global temporary table tmp_table on commit PRESERVE rows as select distinct loaddate from o_t_cigsum_gen@ol_jlb where loaddate > v_maxloaddate and loaddate < p_todate;'exec immediate str;用动态语句应该可以;
用动态语句 execute immediate 'create global temporary table tmp_table on commit PRESERVE rows as select distinct loaddate from o_t_cigsum_gen@ol_jlb where loaddate > v_maxloaddate and loaddate < p_todate'
但是必須要考慮是否存在的問題.......... execute immediate 'drop table tmp_table'; execute immediate 'create global temporary table tmp_table on commit PRESERVE rows as select distinct loaddate from o_t_cigsum_gen@ol_jlb where loaddate > v_maxloaddate and loaddate < p_todate'; ..................
select distinct loaddate from o_t_cigsum_gen@ol_jlb where loaddate > v_maxloaddate and loaddate < p_todate;
用的什么版本?Oracle好像不支持distinct关键字吧
on commit PRESERVE rows as
select distinct loaddate from o_t_cigsum_gen@ol_jlb where loaddate > v_maxloaddate and loaddate < p_todate;'exec immediate str;用动态语句应该可以;
1.在设计期就设计和创建好临时表,不要在存储过程中创建,更不用在存储过程中Drop。
2.在存储过程中用Exec Immediate,指定DDL语句。
on commit PRESERVE rows as
select distinct loaddate from o_t_cigsum_gen@ol_jlb where loaddate > v_maxloaddate and loaddate < p_todate'
execute immediate 'drop table tmp_table';
execute immediate 'create global temporary table tmp_table
on commit PRESERVE rows as
select distinct loaddate from o_t_cigsum_gen@ol_jlb where loaddate > v_maxloaddate and loaddate < p_todate';
..................