动态SQL能执行，非动态SQL执行不了??

[我叫P民]

create or replace procedure test(years in varchar2,corps in varchar2,ctrlprjcodes in varchar2,jukong in varchar2)
is
begin
insert into bdgt_formation(tagfrom,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,
 projectname,pk_formation,def7)
 select biaoji,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,projectname
 ,generatepk(pk_corp)pk_formation,def7 from (
 select distinct 10 as biaoji,f.pk_downsend_b,to_char(to_number(f.m_year)+1) m_year,f.m_dept,f.pk_corp,f.dr,f.projectcode,f.projectname
,f.pk_formation,f.pk_formation def7
 from bdgt_formation_b b,bdgt_formation f,bd_deptdoc bdd 
where b.dr='0' and f.dr='0' and b.childprjname is not null and b.pk_formation=f.pk_formation
and f.m_dept=bdd.pk_deptdoc(+) and b.def4 is not null and nvl(b.closeflag,'N')!='Y' and f.pk_corp=corps and f.m_year
=years and f.projectcode=ctrlprjcodes and b.def4=jukong)base;
end;

不用动态SQL

create or replace procedure test(years in varchar2,corps in varchar2,ctrlprjcodes in varchar2,jukong in varchar2)
is
sqls varchar2(2000):='';
begin
sqls:='insert into bdgt_formation(tagfrom,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode, projectname,pk_formation,def7) 
select biaoji,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,projectname 
,generatepk(pk_corp)pk_formation,def7 from (select distinct 10 as biaoji,f.pk_downsend_b,
to_char(to_number(f.m_year)+1) m_year,f.m_dept,f.pk_corp,f.dr,f.projectcode,f.projectname,
f.pk_formation,f.pk_formation def7 from bdgt_formation_b b,bdgt_formation f,bd_deptdoc bdd 
where b.dr=''0'' and f.dr=''0'' and b.childprjname is not null 
and b.pk_formation=f.pk_formation and f.m_dept=bdd.pk_deptdoc(+) 
and b.def4 is not null and nvl(b.closeflag,''N'')!=''Y'' and f.pk_corp='||corps||' 
and f.m_year='||years||' and f.projectcode='||ctrlprjcodes||' and b.def4='||jukong||')base';
execute  immediate sqls;
end;

用动态SQL，两个语句要达到的目的都一样，可我在测试时发现不用动态SQL的过程并没有执行插入操作，动态SQL那个则是正确的，我把非动态SQL的存储过程给拷出来自己赋值也能插入，实在郁闷了，求解

[心中的彩虹]

[Quote=引用楼主 zhaoguo1987 的回复:]
SQL code
create or replace procedure test(years in varchar2,corps in varchar2,ctrlprjcodes in varchar2,jukong in varchar2)
is
begin
insert into bdgt_formation(tagfrom,pk_downsend_b,m_year,m_dept,p……
[/Quote]

语法没问题的话
刚看了下 有些地方的注意下 在试试看

create or replace procedure test(years in varchar2,corps in varchar2,ctrlprjcodes in varchar2,jukong in varchar2)
is
begin
insert into bdgt_formation(tagfrom,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,projectname,pk_formation,def7)
select biaoji,pk_downsend_b,m_year,m_dept,pk_corp,dr,projectcode,projectname,generatepk(pk_corp) pk_formation,def7
from ( select distinct 10 as biaoji,f.pk_downsend_b,to_char(to_number(f.m_year)+1) m_year,f.m_dept,f.pk_corp,f.dr,f.projectcode,f.projectname,f.pk_formation,f.pk_formation def7
from bdgt_formation_b b,bdgt_formation f,bd_deptdoc bdd
where b.dr='0' and f.dr='0' and b.childprjname is not null and b.pk_formation=f.pk_formation
and f.m_dept=bdd.pk_deptdoc(+) and b.def4 is not null and b.closeflag='Y' and f.pk_corp=corps and f.m_year=years and f.projectcode=ctrlprjcodes and b.def4=jukong ) base;
commit;
end;

[我叫P民]

你说是加commit吧，我是方便自己测试才没加，可以回滚，不用删数据，我实在找不出来第一个SQL为什么就执行不了...郁闷啊

[Phoenix_99]

怎么查看的，虽然是同一用户，但是登录不同，也不可以显示，
例如：

打开一个cmd
SQL> create table t(id number);

表已创建。

SQL> insert into t(1);
insert into t(1)
*
第 1 行出现错误:
ORA-00928: 缺失 SELECT 关键字


SQL> insert into t values(1);

已创建 1 行。

SQL>

打开另一个cmd，用同样的用户名、密码登录
C:\Documents and Settings\Administrator>sqlplus bms/bms

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 9月 28 14:03:37 2010

Copyright (c) 1982, 2004, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from t;

未选定行

SQL>

查询结果是不一样的

[minitoy]

检查下sql语句吧.

[我叫P民]

郁闷做表的人设计字段成char型的，N多空格，截取了后正常了，谢谢各位了