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.
解决方案 »
- 重复记录保留一条的初级问题
- Oracle联结查询多表
- 向各位大侠请问一段procedure code 里的几个code的意思!
- 在线等,求帮助!!在虚拟机中安装oracle10g时出现了小问题
- 请问下面2个查询语句有什么区别,为什么获取到的记录集不一样?
- 菜鸟问题,求助!
- 高手急救oracle 9i 如何禁掉8080端口[在线]
- oracle中不能直接使用如下的语句吗?(条件中不能直接使用pl/sql语句?)
- oracle中怎么对创建的表结构修改?
- Select count(*) from USER 与Select count(ID) from USER不同之处
- 求救!求救!求救!主键问题啊!!
- 大家帮我看看这个问题,困扰我一天了,很奇怪的现象
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'';