create or replace procedure createtable_procedureAuthid Current_Userastable_name varchar2(200);
str_sql varchar2(1000);
beginselect 'BSS_EPON_INTERFACE' || to_char(sysdate-1, 'yyyymmdd') into table_name from dual;execute immediate 'create table ' ||table_name ||' as select * from bss_epon_interface_explain where 1 != 1';
commit; insert into bss_epon_interface_explain select e.area_fathername,e.area_name,a.orderid,c.prod_name,b.ybt_name,a.accnbr
,d.error_explain,d.b_department,d.error_solution,a.lcode,a.d_ipaddress,a.p_lcode,a.gw_name,
a.gw_ip,
a.createtime,
a.error_code, a.exec_log,' ' as query_log
from bss_epon_interface a ,s_yzfbilltype b,b_c_producttype c,d_e_relation d,yzf_area e
where a.sn_no=d.sn_no and a.ordertypeid=b.ybt_id and a.prodspecid=c.prod_id and
a.areaid=e.area_id and a.sn_no is not null;commit;str_sql:='insert into' ||table_name ||'select * from bss_epon_interface_explain;';
execute immediate str_sql;
commit;end;
标识符过长
str_sql varchar2(1000);
beginselect 'BSS_EPON_INTERFACE' || to_char(sysdate-1, 'yyyymmdd') into table_name from dual;execute immediate 'create table ' ||table_name ||' as select * from bss_epon_interface_explain where 1 != 1';
commit; insert into bss_epon_interface_explain select e.area_fathername,e.area_name,a.orderid,c.prod_name,b.ybt_name,a.accnbr
,d.error_explain,d.b_department,d.error_solution,a.lcode,a.d_ipaddress,a.p_lcode,a.gw_name,
a.gw_ip,
a.createtime,
a.error_code, a.exec_log,' ' as query_log
from bss_epon_interface a ,s_yzfbilltype b,b_c_producttype c,d_e_relation d,yzf_area e
where a.sn_no=d.sn_no and a.ordertypeid=b.ybt_id and a.prodspecid=c.prod_id and
a.areaid=e.area_id and a.sn_no is not null;commit;str_sql:='insert into' ||table_name ||'select * from bss_epon_interface_explain;';
execute immediate str_sql;
commit;end;
标识符过长
----这个我去掉了,还是不行;
2.过长的原因可能是insert后面的那个表字段要比select查询出来的字段少。或者某个字段长度不够。
-----我用原来的表来创建的动态表,在把原来的表数据插入动态表,这个不存在这些问题哦;
str_sql varchar2(1000);
beginselect 'BSS_EPON_INTERFACE_' || to_char(sysdate-1, 'yyyymmdd') into table_name from dual;execute immediate 'create table ' ||table_name ||' as select * from bss_epon_interface_explain where 1 != 1';
insert into bss_epon_interface_explain select e.area_fathername,e.area_name,a.orderid,c.prod_name,b.ybt_name,a.accnbr
,d.error_explain,d.b_department,d.error_solution,a.lcode,a.d_ipaddress,a.p_lcode,a.gw_name,
a.gw_ip,
a.createtime,
a.error_code, a.exec_log,' ' as query_log
from bss_epon_interface a ,s_yzfbilltype b,b_c_producttype c,d_e_relation d,yzf_area e
where a.sn_no=d.sn_no and a.ordertypeid=b.ybt_id and a.prodspecid=c.prod_id and
a.areaid=e.area_id and a.sn_no is not null;commit;str_sql:='insert into' ||table_name ||'
(area_fathername, area_name, orderid, prod_name, ybt_name, accnbr, error_explain, b_department,error_solution,
lcode, _ipaddress, p_lcode, gw_name, gw_ip, createtime, error_code, exec_log, query_log )
select area_fathername, area_name, orderid, prod_name, ybt_name, accnbr, error_explain, b_department,error_solution,
lcode, _ipaddress, p_lcode, gw_name, gw_ip, createtime, error_code, exec_log, query_log from bss_epon_interface_explain;';
execute immediate str_sql;commit;end;--------------我改成这样,还是报错
你查看一下你创建的这个表。看下字段名字的大小写,然后和select * from bss_epon_interface_explain;字段名比较一下,大小写是否一致
create table ' ||table_name ||' as select * from bss_epon_interface_explain where 1 != 1
是这样创建的,不存在大小写不一致的问题吧??
(AREA_FATHERNAME, AREA_NAME, ORDERID, PROD_NAME, YBT_NAME, ACCNBR, ERROR_EXPLAIN, B_DEPARTMENT,ERROR_SOLUTION, LCODE, _IPADDRESS, P_LCODE, GW_NAME, GW_IP, CREATETIME, ERROR_CODE, EXEC_LOG, QUERY_LOG )
select area_fathername, area_name, orderid, prod_name, ybt_name, accnbr, error_explain, b_department,error_solution,
lcode, _ipaddress, p_lcode, gw_name, gw_ip, createtime, error_code, exec_log, query_log from bss_epon_interface_explain;';
execute immediate str_sql;
----我把字段改成大写了,还是报同样的错
你用select AREA_FATHERNAME, AREA_NAME, ORDERID, PROD_NAME, YBT_NAME, ACCNBR, ERROR_EXPLAIN, B_DEPARTMENT,ERROR_SOLUTION, LCODE, _IPADDRESS, P_LCODE, GW_NAME, GW_IP, CREATETIME, ERROR_CODE, EXEC_LOG, QUERY_LOG from 前面创建的那个表名,你执行以下这个,如果还是报错的话,你看一下你创建的这个表字段名称那些是小写,然后用select "字段名" from 表名; 比如:select "Area_FatherName" from 表名;
(AREA_FATHERNAME, AREA_NAME, ORDERID, PROD_NAME, YBT_NAME, ACCNBR, ERROR_EXPLAIN, B_DEPARTMENT,ERROR_SOLUTION, LCODE, D_IPADDRESS, P_LCODE, GW_NAME, GW_IP, CREATETIME, ERROR_CODE, EXEC_LOG, QUERY_LOG )
select AREA_FATHERNAME, AREA_NAME, ORDERID, PROD_NAME, YBT_NAME, ACCNBR, ERROR_EXPLAIN, B_DEPARTMENT,ERROR_SOLUTION, LCODE, D_IPADDRESS, P_LCODE, GW_NAME, GW_IP, CREATETIME, ERROR_CODE, EXEC_LOG, QUERY_LOG from bss_epon_interface_explain;';
commit;--我把语句改成这样了,还是要报错;把execute immediate 后面跟的sql 直接拷出来,运行,都没有问题
begin
dbms_output.enable;
str_sql:='insert into BSS_EPON_INTERFACE_20130124
(AREA_FATHERNAME, AREA_NAME, ORDERID, PROD_NAME, YBT_NAME, ACCNBR, ERROR_EXPLAIN, B_DEPARTMENT,ERROR_SOLUTION, LCODE, D_IPADDRESS, P_LCODE, GW_NAME, GW_IP, CREATETIME, ERROR_CODE, EXEC_LOG, QUERY_LOG )
select AREA_FATHERNAME, AREA_NAME, ORDERID, PROD_NAME, YBT_NAME, ACCNBR, ERROR_EXPLAIN, B_DEPARTMENT,ERROR_SOLUTION, LCODE, D_IPADDRESS, P_LCODE, GW_NAME, GW_IP, CREATETIME, ERROR_CODE, EXEC_LOG, QUERY_LOG from bss_epon_interface_explain;';
dbms_output.put_line(str_sql);end ;
----这是写的打印语句打印出来的语句如下:insert into BSS_EPON_INTERFACE_20130124
(AREA_FATHERNAME, AREA_NAME, ORDERID, PROD_NAME, YBT_NAME, ACCNBR, ERROR_EXPLAIN, B_DEPARTMENT,ERROR_SOLUTION, LCODE, D_IPADDRESS, P_LCODE, GW_NAME, GW_IP, CREATETIME, ERROR_CODE, EXEC_LOG, QUERY_LOG )
select AREA_FATHERNAME, AREA_NAME, ORDERID, PROD_NAME, YBT_NAME, ACCNBR, ERROR_EXPLAIN, B_DEPARTMENT,ERROR_SOLUTION, LCODE, D_IPADDRESS, P_LCODE, GW_NAME, GW_IP, CREATETIME, ERROR_CODE, EXEC_LOG, QUERY_LOG from bss_epon_interface_explain;
----执行不报错啊
没有双引号,表字段名都是大写,我全部换成大写了,动态sql我也打出来了,也可以的。