调用存储过程出现错误选项缺失或无效

[qq_29501865]

create or replace procedure tracepart2(inputtime varchar2)
is
insert_table varchar2(200);
create_table varchar2(200);
table_name varchar2(200);
intime varchar2(100);
begin
table_name := 'TRACE'||inputtime;
create_table := 'create table '||table_name||' as select * from tracestyle where 1=2';
--insert_table := 'insert into '||table_name||' select MMSI,receivetime,location from tracestyle where tracestyle.timechange = inputtime';
execute immediate create_table;
--execute immediate insert_table;
end;


call tracepart2('2017-02-02');

[verejava]

PLSQL 存储过程

http://www.verejava.com/?id=17173794602255

[学海无涯-回头是岸]

你把你的时间字符串 to_date转一下

[卖水果的net]

首先来说，你的存储过程是没有问题的。 其实再看， call tracepart2('2017-02-02');，表名称中不能包含-(减号)， 换成 call tracepart2('20170202') 就没有问题了。

[qq_29501865]

我存储的就是字符型，应该是不允许调用里出现符号-导致的错误，但是不知道怎么解决

[qq_29501865]

create or replace procedure tracepart2(inputtime varchar2)
is
insert_table varchar2(200);
create_table varchar2(200);
table_name varchar2(200);
intime date;
con1 int;
begin
table_name := 'TRACE'||inputtime;
create_table := 'create table '||table_name||' as select * from test1 where 1=2';
intime := to_date(inputtime,'yyyymmdd');
insert_table := 'insert into '||table_name||' select * from test1 where test1.time = intime';
select count(*) into con1 from user_tables where table_name = table_name; --查看该用户下是否已经建立了该表
if con1=0 then
execute immediate create_table;
execute immediate insert_table;
else
execute immediate insert_table;
end if;
end;
exec tracepart2('20170204');




我做了测试代码之后发现在insert语句里总是提示Intime标识符无效，请问大神该怎么解决啊