Stored procedure which is valid raises ORA-06550 error
Hello!
I’m a participant of software developers group of company EPAM Systems.
We’re writing web-application using .NET 2.0 (C#), BLToolkit 2.0 and database Oracle 10g (PL/SQL). We’re using the tool “PL/SQL Developer” created by allroundautomation.com
Last time there appear errors when we call stored procedures containing the text:
"ORA-06550: line 1, column 7:PLS-00306: wrong number or types of arguments in call to 'REP_GetLegalLawResults4Period'ORA-06550: line 1, column 7:PL/SQL: Statement ignored " was raised.
It’s very unexpected because there are following points:
1) The tool we use doesn’t show that this stored procedure is in invalid state.
2) Application code doesn’t contain any errors because this code was valid and completely worked in previous builds and there wasn’t any changes in this code.
3) PL/SQL code doesn’t contain any errors too.
This error disappears when we recompile these stored procedures and restart Internet Information Service; we don’t modify any C# or PL/SQL code.
Could you say if this is a BL Toolkit mistake what should we do to solve this problem because it is a very point for production exploitation of our software.
If you need any information I would give it readily.
Thank you in advance.
PL/SQL Code:
Code:
create or replace procedure "REP_GetLegalLawResults4Period"
(
crsr out sys_refcursor
, CurrentEmployeeID in "TSK_Employee".ID%TYPE
, BeginDate in Date
, EndDate in Date
) is
no_data varchar(30) := 'не указан(о)';
dtProcessingAssignedTo "TSK_DebtEventType"."ID"%TYPE := hextoraw('99991000000000000000000000000000'); --распределена к сотруднику
dtProcessingReAssignedTo "TSK_DebtEventType"."ID"%TYPE := hextoraw('99992000000000000000000000000000'); --перераспределена к сортуднику
l_BeginDate DATE := trunc(BeginDate);
l_EndDate DATE := trunc(EndDate);
LegalSolicitorLaw "SEC_Role"."Name"%TYPE := 'LegalSolicitorLaw';
LegalDeptChiefArea "SEC_Role"."Name"%TYPE := 'LegalDeptChiefArea';
LegalDeptChiefRegion "SEC_Role"."Name"%TYPE := 'LegalDeptChiefRegion';
LegalDeptChief "SEC_Role"."Name"%TYPE := 'LegalDeptChief';
begin
open crsr for
select
nvl("GetProcessingDistrictName3"(pr."StructBlockID", sbt."Code"), no_data) as "DistrictName"
, nvl("GetProcessingAreaName2"(pr."StructBlockID", sbt."Code"), no_data) as "AreaName"
, nvl("GetProcessingRegionName3"(pr."StructBlockID", sbt."Code"), no_data) as "RegionName"
, nvl(TO_CHAR(pf."ReceiptDate", 'dd.mm.yyyy'), no_data) as "PortfolioName"
, nvl(cag_prc."Code", no_data) as "CounteragentName"
, "REP_GetEmployeeNameByIDDate"(
de."ToEmployeeID"
, pr."ID"
, null
, de."ProcessingFinishDate"
) as "EmployeeName"
, COUNT(pr.ID) as "ProcessingCount" /* количество просрочек */
, nvl(
SUM(
case
--организуем пересечение интервалов
when trunc(ccl."WritJudgementReceivedDate") between l_BeginDate and l_EndDate
and trunc(ccl."WritJudgementReceivedDate") between trunc(de."ProcessingBeginDate") and trunc(de."ProcessingFinishDate")
and nvl(ccl."WritJudgementReceivedAttr", 0) <> 0 --и указан признак получения исполнит. листа
-- если исполнит. лист получен в период нахождения просрочки на данном сотруднике
then 1
else
0
end + --складываем
case
--организуем пересечение интервалов
when trunc(ccl."ClaimActOfExecDate") between l_BeginDate and l_EndDate
and trunc(ccl."ClaimActOfExecDate") between trunc(de."ProcessingBeginDate") and trunc(de."ProcessingFinishDate")
and nvl(ccl."ClaimActOfExecAttr", 0) <> 0 --и указан признак получения исполнит. листа
-- если исполнит. лист получен в период нахождения просрочки на данном сотруднике
then 1
else
0
end
)
, 0) as "ActOfExecCount" /* количество исполнительных листов*/
from "TSK_Processing" pr
left join "CLI_Debt" dbt on dbt."ID" = pr."DebtID"
left join "CLI_CreditContract" cc on cc."ID" = dbt."CreditContractID"
inner join "CLI_CreditContractLegal" ccl on ccl."ContractID" = cc."ID"
left join "CLI_Person" pers on cc."ClientID" = pers."ID"
left join "DIC_Portfolio" pf on dbt."PortfolioID" = pf."ID"
left join "DIC_StructBlock" dsb on dsb."ID" = pr."StructBlockID"
left join "DIC_Counteragent" cag_prc on pf."CounteragentID" = cag_prc."ID"
left join "DIC_StructBlockType" sbt on dsb."StructTypeID" = sbt."ID"
inner join "TSK_DebtEvent" de on de."DebtID" = dbt."ID"
inner join "TSK_DebtEventType" det on det."ID" = de."TypeID"--притягиваем события
where
(
1 = ANY(
"IsEmployeeInRole"(
--"REP_GetEmployeeIDAssigned"(pr."ID", BeginDate, EndDate),
de."ToEmployeeID",
LegalSolicitorLaw) --юрисконсульт ГСП
, "IsEmployeeInRole"(
--"REP_GetEmployeeIDAssigned"(pr."ID", BeginDate, EndDate),
de."ToEmployeeID",
LegalDeptChiefArea) --руководитель ЮС в районе
, "IsEmployeeInRole"(
--"REP_GetEmployeeIDAssigned"(pr."ID", BeginDate, EndDate),
de."ToEmployeeID",
LegalDeptChiefRegion) --руководитель ЮС в регионе
, "IsEmployeeInRole"(
--"REP_GetEmployeeIDAssigned"(pr."ID", BeginDate, EndDate),
de."ToEmployeeID",
LegalDeptChief) --руководитель ЮС
)
) --выбираем юрисконсультов ГСП
and
(
de."TypeID" = ANY(dtProcessingAssignedTo, dtProcessingReAssignedTo)--распределение либо перераспределение к сотруднику
and (
trunc(de."ProcessingBeginDate") between l_BeginDate and l_EndDate
or trunc(de."ProcessingFinishDate") between l_BeginDate and l_EndDate
) -- пересечение временного интервала и периода нахождения на стр. блоке непусто
) --анализ по событиям
and ("IsProcessingAccessibleLegal2"(CurrentEmployeeID, pr."ID", de."ToEmployeeID") = 1)/* ролевой доступ к данным*/
--and ("IsProcessingAccessibleLegal"(CurrentEmployeeID, pr."ID") = 1)
group by "GetProcessingDistinct3"(pr."StructBlockID", sbt."Code")
, nvl("GetProcessingDistrictName3"(pr."StructBlockID", sbt."Code"), no_data)
, "GetProcessingRegion3"(pr."StructBlockID", sbt."Code")
, nvl("GetProcessingRegionName3"(pr."StructBlockID", sbt."Code"), no_data)
, "GetProcessingArea2"(pr."StructBlockID", sbt."Code")
, nvl("GetProcessingAreaName2"(pr."StructBlockID", sbt."Code"), no_data)
, cag_prc."ID"
, nvl(TO_CHAR(pf."ReceiptDate", 'dd.mm.yyyy'), no_data)
, nvl(cag_prc."Code", no_data)
, "REP_GetEmployeeNameByIDDate"(
de."ToEmployeeID"
, pr."ID"
, null
, de."ProcessingFinishDate"
)
order by --сортировка
nvl("GetProcessingDistrictName3"(pr."StructBlockID", sbt."Code"), no_data) --наим. округа
, nvl("GetProcessingRegionName3"(pr."StructBlockID", sbt."Code"), no_data) --наим. региона
, nvl("GetProcessingAreaName2"(pr."StructBlockID", sbt."Code"), no_data) --наим. района
, nvl(cag_prc."Code", no_data) -- наим. контрагента
, nvl(TO_CHAR(pf."ReceiptDate", 'dd.mm.yyyy'), no_data) --дата портфеля
, "REP_GetEmployeeNameByIDDate"(
de."ToEmployeeID"
, pr."ID"
, null
, de."ProcessingFinishDate"
) --ФИО юрисконсульта
;
end "REP_GetLegalLawResults4Period";
