- 论坛徽章:
- 0
|
应用在调用SP_Atpop_VesvoyMonitor这个存储过程的时候,当时带了这个3个参数值: V_MDEPT := '1070'; V_MDATE := '2006-08-23'; V_MTYPE := '0';oracle报:ORA-06502: PL/SQL: 数字或值错误 ORA-06512: 在line 1,但是过一会用同样的参数又自动好了这个是怎么回事请哪位大哥可以帮忙谢谢!过程如下:
CREATE OR REPLACE PROCEDURE SP_Atpop_VesvoyMonitor
(v_mdept in Varchar2,v_mdate in Varchar2,v_mtype in Varchar2,
pRecCur out P_ReturnDataSet1.ReturnDataSet1)
as
mdept Varchar2(20);
BEGIN
if v_mdept = '0' then
mdept := '%';
else
mdept:=v_mdept;
end if;
open pRecCur for
select total.vesselid, tbattemper.DEPARTMENTCODE ,tbattemper.BERTHTIME, atpop_vesvoy.VESSELENAME "船名", atpop_vesvoy.VOYAGE "航次", atpop_vesvoy.IMPORTSEXPORTSFLAG "进出口",
round(nw *0.001,3)"预计吨位", round(rw *0.001,0) "实际吨位",round(restw *0.001,0) "剩余吨位",nc "预计箱数/件数",rc "实际箱数/件数",restc "剩余箱数/件数",
--atpop_vesvoy.LOCALETALLYEND || atpop_vesvoy.VESSELTALLYEND vstate
decode(atpop_vesdock.STATE,0,0,NULL,0,1) vstate
--total.nw ,total,rw,total.restw
from
(
select N.vesselid,Nw,decode(Rw,null,0,rw) rw, (nw-rw) restw, nc,rc,(nc-rc) restc
from
(SELECT ctnidf_ctncheck.vesselid, SUM (ctnidf_ctncheck.grossweight) nw,COUNT (DISTINCT ctnidf_ctncheck.ctnno) nc
FROM ctnidf_ctncheck, atpop_vesvoy,tbAttemper
WHERE ( (atpop_vesvoy.vesselid = ctnidf_ctncheck.vesselid)
and (tbAttemper.vesselid = ctnidf_ctncheck.vesselid)
and (tbAttemper.DEPARTMENTCODE like v_mdept) and (to_char(tbAttemper.BERTHTIME,'yyyy-mm-dd') = v_mdate)
AND (((atpop_vesvoy.importsexportsflag = 'E')AND (ctnidf_ctncheck.loadport = 'CNSHA'))
or ((atpop_vesvoy.importsexportsflag = 'I')AND (ctnidf_ctncheck.DISCHARGEPORT = 'CNSHA')))
)
GROUP BY ctnidf_ctncheck.vesselid) N,
(SELECT ctnop_ctntal.vesselid, SUM (ctnop_ctntal.grossweight) rw,COUNT (DISTINCT ctnop_ctntal.ctnno) rc
FROM ctnop_ctntal,tbAttemper
where (tbAttemper.vesselid = ctnop_ctntal.vesselid)
and (tbAttemper.DEPARTMENTCODE like mdept) and (to_char(tbAttemper.BERTHTIME,'yyyy-mm-dd') = v_mdate)
GROUP BY ctnop_ctntal.vesselid) r
where n.vesselid = r.vesselid(+)
) total,tbattemper,atpop_vesvoy,atpop_vesdock
where (tbattemper.vesselid = total.vesselid and atpop_vesvoy.vesselid = total.vesselid)
and (to_char(tbAttemper.BERTHTIME,'yyyy-mm-dd') = v_mdate)
and atpop_vesvoy.TRADETYPE = v_mtype
and (tbattemper.VESSELID = atpop_vesdock.VESSELID(+) and tbattemper.ATTEMPERTIME = atpop_vesdock.PLANDOCKTIME(+) and tbattemper.PORTSECTIONCODE = atpop_vesdock.PORTSECTIONCODE(+))
order by departmentcode, total.vesselid;
END sp_Atpop_VesvoyMonitor;
/
[ 本帖最后由 seanyu 于 2006-8-23 11:16 编辑 ] |
|