Stored procedure which is valid raises ORA-06550 error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Stored procedure which is valid raises ORA-06550 error

  1. #1
    Join Date
    Apr 2009
    Location
    Saratov, Russia
    Posts
    2

    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";

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Code is Okay -or at least I trust you it compiles, problem is related to the arguments...

    PLS-00306: wrong number or types of arguments in call to 'REP_GetLegalLawResults4Period'ORA-06550: line 1, column 7
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2009
    Location
    Saratov, Russia
    Posts
    2
    I don't have any idea why it might happen. I don't belive that this problem appeared because of wrong arguments; as I said there wasn't any change in C# or PL/SQL code (according to this block) since we had released previous build.
    In any case, thank you.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by akeyev_va View Post
    I don't have any idea why it might happen. I don't belive that this problem appeared because of wrong arguments; as I said there wasn't any change in C# or PL/SQL code (according to this block) since we had released previous build.
    In any case, thank you.
    Oracle believes otherwise, just look at the message; check the parameters.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width