create or replace
procedure TempTest(archiveDate in Date,CIF_APLOG in varchar2,CIF_APLOGFIELD in varchar2)
as
sqlstr varchar2(255);
begin
sqlstr := 'insert into CIF_ARCHIVEDATE(ARCHIVEDATE) values(''' ||archiveDate|| ''')';
execute immediate sqlstr;
sqlstr := 'create table '|| CIF_APLOG ||' as select * from CIF_APLOG where LOGDATETIME<'||archiveDate;
--sqlstr := 'create table CIF_20080229 as select * from CIF_APLOG where to_char(LOGDATETIME,''yyyy/MM/dd'')<'|| to_char(archiveDate,'yyyy/MM/dd');
execute immediate sqlstr;
end;錯誤:
Connecting to the database CIF.
ORA-00933: SQL command not properly ended
ORA-06512: at "NETEXPRESS.SA.TEMPTEST", line 9
ORA-06512: at line 10
Process exited.
Disconnecting from the database CIF.
procedure TempTest(archiveDate in Date,CIF_APLOG in varchar2,CIF_APLOGFIELD in varchar2)
as
sqlstr varchar2(255);
begin
sqlstr := 'insert into CIF_ARCHIVEDATE(ARCHIVEDATE) values(''' ||archiveDate|| ''')';
execute immediate sqlstr;
sqlstr := 'create table '|| CIF_APLOG ||' as select * from CIF_APLOG where LOGDATETIME<'||archiveDate;
--sqlstr := 'create table CIF_20080229 as select * from CIF_APLOG where to_char(LOGDATETIME,''yyyy/MM/dd'')<'|| to_char(archiveDate,'yyyy/MM/dd');
execute immediate sqlstr;
end;錯誤:
Connecting to the database CIF.
ORA-00933: SQL command not properly ended
ORA-06512: at "NETEXPRESS.SA.TEMPTEST", line 9
ORA-06512: at line 10
Process exited.
Disconnecting from the database CIF.
commit;create table cif_20080229 as select * from CIF_APLOG where logdatetime<archivddate;
commit;如果把变量做为要执行的sql语句,对于日期型的转换了。例如定义一个v_date varchar(20);
v_date:=to_char(archivedate,'yyyy-mm-dd');
sqlstr:='insert into CIF_ARCHIVEDATE(ARCHIVEDATE) values(to_date''' ||v_date||''',''yyyy-mm-dd'')';
execute sqlstr;
commit;这样做虽然麻烦,但是可以根据断点监测出sql语句的正确性,便于调试
archiveDate应该有这样一个过程的处理:to_date(to_char(archiveDate,‘yyyy-mm-dd’),‘yyyy-mm-dd’)即:v_archiveDate:=to_char(archiveDate,‘yyyy-mm-dd’);
sqlstr='create table ' ¦ ¦ CIF_APLOG ¦ ¦' as select * from CIF_APLOG where LOGDATETIME <to_date('||'''||v_archiveDate||'','||''yyyy-mm-dd'';