This discussion is archived
8 Replies Latest reply: May 9, 2013 12:44 PM by BillArms_GS RSS

unit tests - changing function call parameters

BillArms_GS Newbie
Currently Being Moderated
This is a questions about the capability / work around for SQL DEVELOPER unit testing. We are running Version 3.2.20.09.

We developeed a function with about 19 parameters. The parameter values are fetched from a table of test data by using a dynamic query. We had to change our parameters that had a datatype of DATE to a datatype of VARCHAR2. The unit tests which were 'written' against the old API now fail. What is the procedure to update the API for units tests? So far, all I can see doing is dropping the unit tests and 'rewriting' them by creating them against the new function call parameters. Is there an easier way?
Regards,
Bill
  • 1. Re: unit tests - changing function call parameters
    rp0428 Guru
    Currently Being Moderated
    >
    We developeed a function with about 19 parameters. The parameter values are fetched from a table of test data by using a dynamic query. We had to change our parameters that had a datatype of DATE to a datatype of VARCHAR2.
    >
    You 'had to' change the parameters? Really? Who made you? I'd report them to your IT manager.

    Or perhaps you meant you 'chose to' change the parameters from DATE to VARCHAR2.

    Date and datetime data should be stored in the proper datatype. For Oracle that would typically be DATE or TIMSTAMP.

    If you use VARCHAR2 for date/datetime data you will have issues with almost everything you do. The problem you now face is pretty mild compared to the problems that you will need to deal with later.

    In over 25 years I have never seen a valid reason to use VARCHAR2 to store date/datetime data instead of using DATE or TIMESTAMP. Your need could be an exception but I'm rather skeptical.

    The solution is to do the correct thing and change the datatype back to DATE.
  • 2. Re: unit tests - changing function call parameters
    BillArms_GS Newbie
    Currently Being Moderated
    The data is stored in the database as the DATE datatype. The formal parameter in the function has been changed to the VARCHAR2. The change was due to the GUI framework returning strings vice dates. We have handled all the data transfromation and it is not a problem.

    My questions was how to update the unit tests to reflect a change in the function call parameters. Forgive me if I did not make that clear. I can see where the unit test arguments data is stored in the UT_TEST_ARGUMENTS table, but I am hesitant to change the data there, since I do not know all the possible implications.

    Regards,
    Bill
  • 3. Re: unit tests - changing function call parameters
    riedelme Expert
    Currently Being Moderated
    BillArms_GS wrote:
    This is a questions about the capability / work around for SQL DEVELOPER unit testing. We are running Version 3.2.20.09.

    We developeed a function with about 19 parameters. The parameter values are fetched from a table of test data by using a dynamic query. We had to change our parameters that had a datatype of DATE to a datatype of VARCHAR2. The unit tests which were 'written' against the old API now fail. What is the procedure to update the API for units tests? So far, all I can see doing is dropping the unit tests and 'rewriting' them by creating them against the new function call parameters. Is there an easier way?
    Regards,
    Bill
    If you changed the datatype and the application no longer works you will have to either change the datatype back or modify the unit tests. If changing the datatype was really a good idea and needed to happen you will have to modify the unit test scripts.
  • 4. Re: unit tests - changing function call parameters
    BillArms_GS Newbie
    Currently Being Moderated
    Yes, I want to know how to modify the units test in SQL DEVELOPER.
  • 5. Re: unit tests - changing function call parameters
    riedelme Expert
    Currently Being Moderated
    BillArms_GS wrote:
    Yes, I want to know how to modify the units test in SQL DEVELOPER.
    What are they written in? If they are PL/SQL procedures, functions, or packages you can use the code editor in SQL*Developer. If they are offline scirpts you can you any editor you want, like notepad.

    You will probably have to change the API calls to make them work properly with the new datatypes. If you want help figuring out how you will have to carefully post the API definitions, the calls, the errors you are getting, and whether the errors are run-time or compile-time
  • 6. Re: unit tests - changing function call parameters
    BillArms_GS Newbie
    Currently Being Moderated
    All the function are in PL/SQL packages. The unit tests for the functions were created in the SQL DEVLEOPER unit test wizard. The function that was changed , search.db_search_tbl_ds, has multiple unit tests which have multiple implementations. The unit test level in focused on specific parameters and the implementations focusedon variations of the parameter (has a value, has a null, successful search, failed search, etc).

    I see two ways to 'modify' the unit tests.
    1:) to drop and recreate each unit test and its implemetation.
    2:) to change the data in the unit tests repository tables. Tables which I could see to be targets for the data change are the UT_TEST and UT_TEST_ARGUMENTS. The DATA_TYPE column in the UT_TEST_ARGUMENTS table appears to be where the unit tests parameter data types are stored.

    What I am hoping is a set of directions, script templates or functions on how to modify the data in the tables. I can not find these directions in the documentation.

    This may be beyond the current capabilty of the tool. Given that package functions and procedures do have their public signatures change during the development process, it would be a good idea to be a able to modify the unit tests. This would lower the amount of rework and make the tool friendlier to use ealrier in the developement process.

    Regards,
    Bill

    Edited by: BillArms_GS on Apr 15, 2013 2:21 PM

    Edited by: BillArms_GS on Apr 15, 2013 2:22 PM
  • 7. Re: unit tests - changing function call parameters
    BillArms_GS Newbie
    Currently Being Moderated
    Since I have not received an answer on how to change the parameter dataype in the generated unit tests, I will assume, my question is unclear or the answer does not exist. My plan is now to back up the UT database and start trying to manipulate the data in the database. I will post what I find.

    Bill
  • 8. Re: unit tests - changing function call parameters
    BillArms_GS Newbie
    Currently Being Moderated
    Making minor data changes to the Unit Test database, allowed me to change the datatype for the function call parmeters. Nothing appeared to break.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points