返回信息流如下是存储过程：
create or replace procedure PRO_WL_I_EndToNOAHARK(BeginTime date,EndTime date,SupType varchar2,cEntityCode varchar2)
as
e_year VARCHAR2(6);
e_month VARCHAR2(4);
k_pk_corp VARCHAR2(20);
k_pk_glorgbook VARCHAR2(100);
k_pk_glbook VARCHAR2(100);
begin
e_year := SUBSTR(EndTime,1,4);
e_month := SUBSTR(EndTime,6,2);
select corp.pk_corp,gb.pk_glorgbook,gb.pk_glbook into k_pk_corp,k_pk_glorgbook,k_pk_glbook
from bd_corp corp,bd_glorg gl, bd_glorgbook gb
where corp.unitcode =cEntityCode and NVL (gl.dr, 0) = 0 AND NVL (gb.dr, 0) = 0
AND corp.pk_corp = gl.pk_entityorg AND gl.pk_glorg = gb.pk_glorg and gb.type = 0;
delete from TBL_Trans_inter_data_ict where Entity=cEntityCode;
if SupType='0000' then
insert into TBL_Trans_inter_data_ict
select 1 as datatype,'' as vouchid,'' as vouno ,lrzxbm as profit,ywfbbm as bustype,
'' as sap3,'' as TR_FenPei,'' as TR_ShortText,'' as TR_LongText, case when balanorient=1 then '借' else '贷' end as Direct,
'0' as SAPFJZ,'' as VouDate,''
as IsReturned ,'' as BookDate ,ksbm as SupCode,corp.unitcode as Entity,'' expensesAcc,code as AcctCode,
currtypecode as TR_CUR,'' as TR_AMT,
'' as TR_LAMT, amount_yb as TR_UnOffAMT,amount_bb as TR_LUnOffAMT,
'' as TR_RATE,'' as AgingDate,'' as OffGroup,'' as OffDate,'' as IsDelete,'' as isInvalid,'' as IsOffed, '' as VouUser,'NC' as FinType,'' as UpdateTime
from (select substr(b.subjcode, 1, 6) as code,
tz.valuecode as ksbm,
tz.valuename as ksmc,
sum(debitamount) - sum(creditamount)
as amount_yb,
sum(localdebitamount) - sum(localcreditamount)
as amount_bb,
tz1.valuecode as ywfbbm,
tz1.valuename as ywfbmc,
tz2.valuecode as lrzxbm,
tz2.valuename as lrzxmc,
b.balanorient,
curr.currtypecode,
a.pk_corp
from (SELECT debitamount, creditamount, localdebitamount, localcreditamount, assid, pk_accsubj,pk_currtype,pk_corp
from gl_balance
where period = '00'
and year = e_year
and nvl(dr, 0) = 0
and pk_corp =k_pk_corp
and pk_glorgbook=k_pk_glorgbook
union all
select
b.debitamount,
b.creditamount,
b.localdebitamount,
b.localcreditamount,
b.assid,
pk_accsubj,
b.pk_currtype,a.pk_corp
from gl_voucher a, GL_DETAIL b
where a.PK_VOUCHER = b.PK_VOUCHER
and nvl(a.dr, 0) = 0
and nvl(b.dr, 0) = 0
and a.period > '00'
and a.pk_corp = b.pk_corp
and a.pk_corp = k_pk_corp
and a.year = e_year
and a.
period <= e_month
and a.pk_glorgbook=k_pk_glorgbook
and a.PREPAREDDATE <= EndTime
and (a.errmessage is null and (a.discardflag <> 'Y') or
a.errmessage is not null or (a.discardflag = 'Y'))
and a.voucherkind <> 2
and a.voucherkind <> 255) a,
bd_accsubj b,
bd_currtype curr,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '00010000000000000073'
and nvl(dr, 0) = 0) tz,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '0001V510000000000XWS'
and nvl(dr, 0) = 0) tz1,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '0001V510000000000Y8E'
and nvl(dr, 0) = 0) tz2
where tz.freevalueid = a.assid
and tz1.freevalueid = a.assid
and b.pk_accsubj = a.pk_accsubj
and substr(b.subjcode, 1, 4) in ('1131', '2121', '1151')
and curr.pk_currtype=a.pk_currtype
group by tz1.valuecode,
tz1.valuename,
tz.valuecode,
tz2.valuename,
tz2.valuecode,
tz.valuename,
b.balanorient,
curr.currtypecode,
substr(b.subjcode, 1, 6),
a.pk_corp) m,bd_corp corp
where m.amount_bb <> 0 and corp.pk_corp=m.pk_corp and substr(m.ksbm,1,4)<>'9999';
else
insert into TBL_Trans_inter_data_ict
select 1 as datatype,'' as vouchid,'' as vouno ,lrzxbm as profit,ywfbbm as bustype,
'' as sap3,'' as TR_FenPei,'' as TR_ShortText,'' as TR_LongText, case when balanorient=1 then '借' else '贷' end as Direct,
'0' as SAPFJZ,'' as VouDate,''
as IsReturned ,'' as BookDate ,ksbm as SupCode,corp.unitcode as Entity,'' expensesAcc,code as AcctCode,
currtypecode as TR_CUR,'' as TR_AMT,
'' as TR_LAMT, amount_yb as TR_UnOffAMT,amount_bb as TR_LUnOffAMT,
'' as TR_RATE,'' as AgingDate,'' as OffGroup,'' as OffDate,'' as IsDelete,'' as isInvalid,'' as IsOffed, '' as VouUser,'NC' as FinType,'' as UpdateTime
from (select substr(b.subjcode, 1, 6) as code,
tz.valuecode as ksbm,
tz.valuename as ksmc,
sum(debitamount) - sum(creditamount)
as amount_yb,
sum(localdebitamount) - sum(localcreditamount)
as amount_bb,
tz1.valuecode as ywfbbm,
tz1.valuename as ywfbmc,
tz2.valuecode as lrzxbm,
tz2.valuename as lrzxmc,
b.balanorient,
curr.currtypecode,
a.pk_corp
from (SELECT debitamount, creditamount, localdebitamount, localcreditamount, assid, pk_accsubj,pk_currtype,pk_corp
from gl_balance
where period = '00'
and year = e_year
and nvl(dr, 0) = 0
and pk_corp =k_pk_corp
and pk_glorgbook=k_pk_glorgbook
union all
select
b.debitamount,
b.creditamount,
b.localdebitamount,
b.localcreditamount,
b.assid,
pk_accsubj,
b.pk_currtype,a.pk_corp
from gl_voucher a, GL_DETAIL b
where a.PK_VOUCHER = b.PK_VOUCHER
and nvl(a.dr, 0) = 0
and nvl(b.dr, 0) = 0
and a.period > '00'
and a.pk_corp = b.pk_corp
and a.pk_corp = k_pk_corp
and a.year = e_year
and a.
period <= e_month
and a.pk_glorgbook=k_pk_glorgbook
and a.PREPAREDDATE <= EndTime
and (a.errmessage is null and (a.discardflag <> 'Y') or
a.errmessage is not null or (a.discardflag = 'Y'))
and a.voucherkind <> 2
and a.voucherkind <> 255) a,
bd_accsubj b,
bd_currtype curr,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '00010000000000000073'
and nvl(dr, 0) = 0) tz,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '0001V510000000000XWS'
and nvl(dr, 0) = 0) tz1,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '0001V510000000000Y8E'
and nvl(dr, 0) = 0) tz2
where tz.freevalueid = a.assid
and tz1.freevalueid = a.assid
and b.pk_accsubj = a.pk_accsubj
and substr(b.subjcode, 1, 4) in ('1131', '2121', '1151')
and curr.pk_currtype=a.pk_currtype
group by tz1.valuecode,
tz1.valuename,
tz.valuecode,
tz2.valuename,
tz2.valuecode,
tz.valuename,
b.balanorient,
curr.currtypecode,
substr(b.subjcode, 1, 6),
a.pk_corp) m,bd_corp corp
where m.amount_bb <> 0 and corp.pk_corp=m.pk_corp and substr(m.ksbm,1,4)='9999';
end if;
END;
运行编译没问题
但是执行如下语句：
execute PRO_WL_I_EndToNOAHARK('2009-01-10','2009-01-10','1014','241500');
却是无效SQL语句
使用了调试功能，在里面输入参数，但是却显示00306错误，网上查了好多资料，但是无法解决这个问题，请数据库高手来帮小弟解决下，在下感激不尽
这是一条镜像帖。来源：北邮人论坛 / database / #4994同步于 2010/11/9
该镜像源已超过 30 天没有更新，可能在源站已被删除。
Database机器人发帖
求大牛关于存储过程的问题
dhytfzz2009
2010/11/9镜像同步2 回复
订阅后，新回复会通过你的通知中心匿名送达。