7 Replies Latest reply on Mar 5, 2019 7:34 AM by 3532608

    Getting error in Unit Testing when the Unit has RECORDTYPE IN Parameter

    3532608

      I am testing a unit using UNIT TESTING framework in SQL Developer. The unit has IN parameter of RECORDTYPE. Firstly, I was trying to provide value using Dynamic value query. In this case, when I was executing the test case, I got an error "Too Many Values". So I tried to hard code the input values in the RECORDTYPE parameter. While executing this case, the unit testing framework is always returning null values. I have attached the screen shot depicting the error. Please help me to come out of this issue.

       

        • 1. Re: Getting error in Unit Testing when the Unit has RECORDTYPE IN Parameter
          User10299702-Oracle

          Can anyone please revert to this query, as I'm also facing the same issue

          • 2. Re: Getting error in Unit Testing when the Unit has RECORDTYPE IN Parameter
            KarstenH-dk

            testing with a very simple record as input parameter:

             

            --have this record declaration in a package

               

            create or replace

            package delete_me2 authid definer as

             

               subtype BLA_ID_t is NUMBER(15);

               subtype LPT_STARTDATO_t is DATE;

             

               type Row_type is record (

                    BLA_ID     BLA_ID_t,

                    LPT_STARTDATO  LPT_STARTDATO_t);

            end delete_me2;   

             

            --create this function using the record as in parameter

             

            create or replace procedure delete_me3

            (

              rec_in in delete_me2.row_type

            ) authid definer is

            begin

                insert into sletmig (bla_id,

                                    testdate)

               values (rec_in.BLA_ID,rec_in.LPT_STARTDATO);

            end delete_me3;

             

            --and a test testing procedure delete_me3

             

             

            press the pencil to apply parameters

             

            when applying date parameters use the build in calendar - press pencil again

             

             

            in order to apply the right format for the date the unit test is a little sensitive about the date format given

             

            works for me

             

            regards Karsten

            • 3. Re: Getting error in Unit Testing when the Unit has RECORDTYPE IN Parameter
              3532608

              I am always getting an exception, whenever I run any case having RECORD TYPE as IN parameter. My record type do not have any parameter having type as DATE. The screen shot of the data source is attached:

              The exception that is been raised is:

              Please help me to come out of this issue.

               

              Regards,

              Subhradip Aon.

              • 4. Re: Getting error in Unit Testing when the Unit has RECORDTYPE IN Parameter
                KarstenH-dk

                You provide too little information !

                 

                How does the test-function/procedure looks ?

                 

                How does the input parameters looks in the detail section in the unit test ?

                 

                I cannot recognize your "screen shot of the data source" from where did you get it ?

                 

                regards Karsten

                • 5. Re: Getting error in Unit Testing when the Unit has RECORDTYPE IN Parameter
                  3532608

                  Let us take a small Example :

                   

                  The unit  that I want to test is :

                  CREATE OR REPLACE PACKAGE BODY EMPLOYEE_CALC IS

                   

                   

                  PROCEDURE Pr_Process_validation(p_rec   IN employee%ROWTYPE,

                  p_Err_Code OUT varchar2) IS

                  BEGIN

                      if(p_rec.name = 'Subhradip') then

                        p_Err_Code   := 'ERROR';

                      end if;

                  END Pr_Process_validation;

                   

                   

                  End EMPLOYEE_CALC;

                   

                  The value provided to the parameters in unit tesing :

                   

                  When I am running the test case, I am getting the below exception.

                   

                  Please help me to overcome this exception.

                   

                  Regards,

                  Subhradip.

                  • 6. Re: Getting error in Unit Testing when the Unit has RECORDTYPE IN Parameter
                    KarstenH-dk

                    tried this:

                    1 - created package

                     

                    create or replace

                    package employee_calc authid definer is

                     

                      subtype ID_t is NUMBER(15);

                      subtype name_t is VARCHAR2(10 CHAR);

                      type Row_type is record (

                           ID                  ID_t,

                           name                name_t

                          );

                         

                       PROCEDURE Pr_Process_validation(p_rec IN employee_calc.Row_type,

                       p_Err_Code OUT varchar2);      

                    end employee_calc;

                     

                    create or replace

                    package body employee_calc as

                     

                      procedure pr_process_validation(p_rec in employee_calc.row_type,

                       p_err_code out varchar2) as

                    BEGIN

                        if(p_rec.name = 'Subhradip') then

                          p_Err_Code   := 'ERROR';

                        end if;

                    END Pr_Process_validation;

                     

                    end employee_calc;

                     

                    2 -tested succesfully

                     

                     

                     

                    3 guess you are giving some kind of erroneus parameter - try to run the procedure Pr_Process_validation from a worksheet like this:

                     

                    set SERVEROUTPUT ON size 20000

                    declare

                      myrow  employee_calc.Row_type;

                      my_errorcode varchar2(10 char);

                    begin

                      myrow.id:=44566;

                      myrow.name:='Subhradip';

                     

                      EMPLOYEE_CALC.PR_PROCESS_VALIDATION(P_REC => myrow,

                                                         P_ERR_CODE => my_errorcode);

                      DBMS_OUTPUT.PUT_LINE('my_errorcode='||my_errorcode);                                    

                    end;                                    

                     

                    PL/SQL procedure successfully completed.

                     

                    my_errorcode=ERROR

                     

                    regards Karsten

                    • 7. Re: Getting error in Unit Testing when the Unit has RECORDTYPE IN Parameter
                      3532608

                      Thank You for your help. I have simulated the same way you explained. It worked perfectly.

                      But when the IN param is a record type of any database table, the same error as given in my previous reply is coming.

                       

                      In SQL Developer UNIT TESTING, if  the datatype in specify parameters is PL/SQL RECORD, then the exception as said previous is coming. I have attached the screen shots.

                       

                       

                      The exception thrown is :

                       

                      Please help by showing an example where the IN parameter is a row type for a table existing in DB.