ORA-00911的错误

[mr_foxsand]

捣鼓了一下午，没检查出错误，各位大大帮俺看看是不是变量长度的问题？
DECLARE
V_SQL VARCHAR2(4000);
V_WRH VARCHAR2(4000);
V_SOR VARCHAR2(4000);
V_OTH VARCHAR2(4000);
VBDATE DATE;
VEDATE DATE;
V_ID INT;
V_ID1 INT;
V_CKSEQ VARCHAR2(100);
V_STORE VARCHAR2(100);
----V_ACODE VARCHAR2(100);
BEGIN
VBDATE := TO_DATE('2014-11-01','YYYY.MM.DD');
VEDATE := TO_DATE('2014-11-05','YYYY.MM.DD');
DELETE HD_REPORT.HDTMP_DXD_PHMX;
COMMIT;
----获取有效盘点序号，FILDATE:门店计划盘点日期，FILDATE1：盘入日期
INSERT INTO HD_REPORT.HDTMP_DXD_PHMX
(NUM,CLS,FILDATE,FILDATE1,GDCODE,STCODE,NOTE)
SELECT DISTINCT ROWNUM ID,CKSEQ,FILDATE,FILDATE1,'-',STORE,'0'
FROM (
SELECT DISTINCT A.CKSEQ,TRUNC(B.CKDATE) FILDATE,TRUNC(A.CKTIME) FILDATE1,A.STORE
FROM CKDATAS A,CKDIR B
WHERE A.CKSEQ=B.CKSEQ AND A.STAT='3'
AND A.CKTIME >= VBDATE AND A.CKTIME < VEDATE
) A;
COMMIT;
------------------
V_ID := 1;
----V_ACODE := '0000';
SELECT MAX(NUM) INTO V_ID1 FROM HD_REPORT.HDTMP_DXD_PHMX WHERE NOTE='0';
WHILE V_ID <= V_ID1
LOOP
SELECT DISTINCT CLS INTO V_CKSEQ ---盘点序号
FROM HD_REPORT.HDTMP_DXD_PHMX
WHERE NUM=V_ID;
SELECT STCODE INTO V_STORE ---门店GID
FROM HD_REPORT.HDTMP_DXD_PHMX
WHERE NUM=V_ID;
----------获取仓位范围------
SELECT DISTINCT NVL(B.WRHRANGESQL,'0=0') INTO V_WRH
FROM CKDIR A,CKRANGEDTL B,CKDATAS C,STORE S
WHERE C.CKSEQ=B.NO AND C.STAT='3' AND C.STORE=S.GID AND C.CKSEQ=A.CKSEQ
AND C.CKSEQ=V_CKSEQ AND S.GID = V_STORE;
---------------
---------获取品类范围
SELECT DISTINCT NVL(SORTRANGESQL,'0=0') INTO V_SOR
FROM CKDIR A,CKRANGEDTL B,CKDATAS C,STORE S
WHERE C.CKSEQ=B.NO AND C.STAT='3' AND C.STORE=S.GID AND C.CKSEQ=A.CKSEQ
AND C.CKSEQ=V_CKSEQ AND S.GID=V_STORE
--AND A.CKDATE >= '2014-11-01' AND A.CKDATE < '2014-11-05'
;
-------获取其他限制条件
SELECT DISTINCT NVL(OTHERSRANGESQL,'0=0') INTO V_OTH
FROM CKDIR A,CKRANGEDTL B,CKDATAS C,STORE S
WHERE C.CKSEQ=B.NO AND C.STAT='3' AND C.STORE=S.GID AND C.CKSEQ=A.CKSEQ
AND C.CKSEQ=V_CKSEQ AND S.GID = V_STORE;
-----------------------
V_SQL := 'INSERT INTO HD_REPORT.HDTMP_DXD_PHMX
(NUM,CLS,FILDATE,GDCODE,STCODE)
SELECT DISTINCT 1,V_CKSEQ,TRUNC(SYSDATE),nvl(GOODS.CODE,''-''),D.CODE
FROM BUSINVS A,WAREHOUSE WAREHOUSE,SORTNAME SORTNAME,STORE D,GOODS GOODS
WHERE A.GDGID=GOODS.GID AND A.WRH=WAREHOUSE.GID AND A.STORE=D.GID AND GOODS.SORT=SORTNAME.SCODE'
||' AND ('||V_WRH||
') AND ('||V_SOR||
') AND ('||V_OTH||
')AND (D.GID='||V_STORE||');';
EXECUTE IMMEDIATE V_SQL;
COMMIT;
V_ID := V_ID+1;
END LOOP;
COMMIT;
END;

[ffshao]

给出具体的错误信息啊

[江南小鱼]

这么长，不结合数据库pl/sql执行，肉眼找错误，何难。 有一点建议

V_SQL VARCHAR2(4000);
 V_WRH VARCHAR2(4000);
 V_SOR VARCHAR2(4000);
 V_OTH VARCHAR2(4000);

你把上面的几个变量都定义成4000，存储过程最后你又把这个几个拼接在一起赋值给V_SQL，超过4000了肿么办呢？ VARCHAR2作为变量使用的时候，支持的长度是32767个字符，你把V_SQL定义成VARCHAR2(32767)好了。

[bw555]

拼接的v_sql，最后面分号去掉

V_SQL := 'INSERT INTO HD_REPORT.HDTMP_DXD_PHMX
(NUM,CLS,FILDATE,GDCODE,STCODE)
SELECT DISTINCT 1,V_CKSEQ,TRUNC(SYSDATE),nvl(GOODS.CODE,''-''),D.CODE
FROM BUSINVS A,WAREHOUSE WAREHOUSE,SORTNAME SORTNAME,STORE D,GOODS GOODS
WHERE A.GDGID=GOODS.GID AND A.WRH=WAREHOUSE.GID AND A.STORE=D.GID AND GOODS.SORT=SORTNAME.SCODE'
||' AND ('||V_WRH||
') AND ('||V_SOR||
') AND ('||V_OTH||
')AND (D.GID='||V_STORE||')';
EXECUTE IMMEDIATE V_SQL;

[yuyeyi]

打印最后执行的sql或者改成存储过程，调用debug

[一颗向往数据的心丶]

引用 3 楼 bw555 的回复:

拼接的v_sql，最后面分号去掉

++ PS：楼主这种动态SQL要注意把拼装的SQL输出出来手动执行，查错。

[binsweet]

引用 3 楼 bw555 的回复:

拼接的v_sql，最后面分号去掉

V_SQL := 'INSERT INTO HD_REPORT.HDTMP_DXD_PHMX
(NUM,CLS,FILDATE,GDCODE,STCODE)
SELECT DISTINCT 1,V_CKSEQ,TRUNC(SYSDATE),nvl(GOODS.CODE,''-''),D.CODE
FROM BUSINVS A,WAREHOUSE WAREHOUSE,SORTNAME SORTNAME,STORE D,GOODS GOODS
WHERE A.GDGID=GOODS.GID AND A.WRH=WAREHOUSE.GID AND A.STORE=D.GID AND GOODS.SORT=SORTNAME.SCODE'
||' AND ('||V_WRH||
') AND ('||V_SOR||
') AND ('||V_OTH||
')AND (D.GID='||V_STORE||')';
EXECUTE IMMEDIATE V_SQL;

正解，如果还有问题，把你v_sql最终的文本你自己执行一下，就知道错在哪了。

[卖水果的net]

东西有点长，最好贴出错误信息，查着好查。