2 Replies Latest reply on Feb 26, 2019 9:35 AM by Kalaivani P-Oracle

    SQL Developer - Unit Test error

    Kalaivani P-Oracle

      When trying to create the unit test package in SQL Developer, for the functions with parameters of PLSQL table type (%ROWTYPE), error is shown.

      Has anyone got a solution for this issue yet?

       

      Source code:

      CREATE OR REPLACE FUNCTION fn_emp_det

      (

           p_emp_id  IN employee.emp_id%TYPE,

           p_emp_row OUT employee%ROWTYPE

      ) RETURN BOOLEAN IS

      BEGIN

           SELECT * INTO p_emp_row FROM employee WHERE emp_id = p_emp_id;

      RETURN TRUE;

      EXCEPTION

      WHEN OTHERS THEN

           dbms_output.put_line('Err ' || SQLERRM);

           RETURN FALSE;

      END;

       

      Error:

        • 1. Re: SQL Developer - Unit Test error
          KarstenH-dk

          I have had the same problem - it isn't possible yet to use records or arrays as parameters in the Sqldeveloper unittest.

           

          What I did was to send the parameter (the record) as a varchar2 - string and then rebuild the record again from the string.

           

          CREATE OR REPLACE FUNCTION fn_emp_det

          (

               p_emp_id  IN employee.emp_id%TYPE,

               p_emp_row_str OUT VARCHAR2

          ) RETURN BOOLEAN IS

          p_emp_row OUT employee%ROWTYPE;

          BEGIN

               SELECT * INTO p_emp_row FROM employee WHERE emp_id = p_emp_id;

               p_emp_row_str:= to_char(p_emp_row.emp_id)||','||p_emp_row.ename||','||p_emp_row.job||','||to_char(p_emp_row.number);  --etc

          RETURN TRUE;

          ....

           

          END;

           

          regards Karsten

          • 2. Re: SQL Developer - Unit Test error
            Kalaivani P-Oracle

            This issue will not be seen with the latest version of SQL Developer. But still, in latest version also, the ROWTYPE variables are not processed correctly. If anybody has successfully processed a ROWTYPE variable (IN or OUT parameter) successfully, please share a sample.