Cut-and-Paste-Driven-Development – Using utPLSQL to build a test harness in Orac...
source link: https://mikesmithers.wordpress.com/2023/10/16/cut-and-paste-driven-development-using-utplsql-to-build-a-test-harness-in-oracle/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Cut-and-Paste-Driven-Development – Using utPLSQL to build a test harness in Oracle.
If you’re working on a Decision Support System (DSS) then sooner or later you’re likely to need to create or change a package which runs as part of a lengthy batch process.
In such cirumstances, it would be useful to have a good old-fashioned test harness to run your code so you can test it’s functionality without having to kick off the entire batch.
Fortunately, utPLSQL is not just useful when it comes to TDD, CI/CD pipelines and the like. It can easily be used for the purpose of creating a simple stand-alone unit test for a single package.
Having used utPLSQL for this purpose quite regularly, I’ve found that my test harnesses tend to follow the same basic pattern.
What follows is a walkthrough of a typical utPLSQL package I might construct for this purpose.
Obviously, it won’t be to everyone’s taste but hopefully, you’ll find something of use in it.
If you’re interested, you can find the code in this Github Repo.
Testing Approach
The main purpose of these tests is to check that the program being tested produces a set result for a set input.
Each individual test is structured in four sequential phases :
- Setup – setup the input data to use in the test
- Execute – run the test and retrieve the output
- Validate – check that the output matches what we expect
- Teardown – reset the everything back to how it was before we ran the test.
Test Data
Whilst tables in DSS applications can contain a large number of columns, our test records only need to contain values that are :
- mandatory in the table ( or possibly part of an RI constraint or Unique Key)
- relevant to the test we’re conducting
In terms of the values themselves, they are not usually constrained other than by the definition of the column into which they are being inserted.
Therefore, we may be able to use negative numbers for integer identifiers to avoid clashes with any pre-existing records.
Also there may be nothing to stop us using random strings for VARCHAR2 values. Alternatively, if you’re easily amused, you may craft your test values to follow a theme. Not that I’d ever do anything so frivolous…
Environment
The database I’m running on is Oracle 19c on Oracle Cloud (Free Tier).
The version of utPLSQL used here is 3.1.13, downloaded from the Project’s Github Repo
It was installed without the DDL trigger by connecting to my OCI instance as the Admin user and following the appropriate instructions here.
The Code we’ll be Testing
The package we want to test performs updates on this table :
drop table employees_core; create table employees_core ( -- Columns populated by initial load load_id number not null , employee_id number not null , first_name varchar2(20), last_name varchar2(25), email varchar2(25), phone_number varchar2(20), hire_date date , job_id varchar2(10), salary number, commission_pct number, manager_id number, department_id number, -- Additional columns populated as part of enrichment process -- Job details job_title varchar2(50), -- Department Details department_name varchar2(50), -- Enrichment status record_status varchar2(25), constraint employees_core_pk primary key (load_id, employee_id) ) / |
To do so, it queries these tables :
create table departments ( department_id number not null , department_name varchar2(30) not null , manager_id number, location_id number, constraint departments_pk primary key ( department_id) ) / create table jobs ( JOB_ID varchar2(10) not null , JOB_TITLE varchar2(35) not null , MIN_SALARY number, MAX_SALARY number, constraint jobs_pk primary key ( job_id) ) / |
The package itself is :
create or replace package enrich_employees is procedure department_details( i_load_id in number); procedure job_details( i_load_id in number); end ; / create or replace package body enrich_employees is procedure department_details( i_load_id in number) is -- Populate the Department Name. -- Suspend the record if we don't get a match. begin merge into employees_core emp using departments dept on ( emp.department_id = dept.department_id and emp.load_id = i_load_id and emp.record_status = 'PROCESS ' ) when matched then update set emp.department_name = dept.department_name; update employees_core set record_status = ' DEPARTMENT_ID_NOT_FOUND ' where record_status = ' PROCESS ' and department_name is null; commit; end department_details; procedure job_details( i_load_id in number) is -- Don' t suspend if we don 't get a match, just leave the job_title empty. begin merge into employees_core emp using jobs j on ( emp.job_id = j.job_id and emp.record_status = ' PROCESS' and emp.load_id = i_load_id ) when matched then update set emp.job_title = j.job_title; commit ; end job_details; end ; / |
Note that, as is common in such routines, commits are done immediately after potentially large DML statements in order to minimise the length of time that Undo space is in use.
This is significant as we’ll need to account for it explicitly in our tests.
Deciding what tests we need
For the DEPARTMENT_DETAILS procedure, we want to check what happens when :
- we find a match in the DEPARTMENTS table
- we don’t find a match
- the DEPARTMENT_ID on the core record is null
For JOB_DETAILS, the conditions to test are similar :
- RECORD_STATUS is not ‘PROCESS’
- we find a match in the JOBS table
- we don’t find a match
- the JOB_ID is null
From this list of scenarios, we can construct our test package specification :
create or replace package enrich_employees_ut as --%suite(enrich_employees_ut) --%rollback(Manual) --%test( Department Lookup Succeeds) procedure department_is_found; --%test( Department does not exist) procedure department_not_found; --%test( Department ID is null) procedure department_is_null; --%test( Status is not PROCESS) procedure status_not_process; --%test( Job Lookup Succeeds) procedure job_is_found; --%test( Job does not exist) procedure job_not_found; --%test( Job ID is null) procedure job_is_null; end enrich_employees_ut; |
The annotations we’re using here are :
--%suite(enrich_employees_ut)
…which allows us to the option to group multiple test packages into the same suite should we want to.
--%rollback(Manual)
…which prevents the Framework attempting it’s default rollback behaviour ( rollback to a savepoint) which won’t work here due to the commits in the code we’re testing.
--%test
…which identifies and describes the tests themselves.
Creating a Stub for the test Package Body
We may well want to write tests and run them one-at-a-time so that we can adjust them ( or, indeed the code we’re testing) as we go.
In such circumstances, a script such as this, which uses the already created test package specification to generate a file containing the ddl for the package body, might come in handy :
clear screen set heading off set lines 130 set pages 500 set feedback off set verify off column pkg new_value v_package_name noprint select '&1' as pkg from dual; spool '&v_package_name..pkb' with package_skeleton as ( select 1 as line, 'create or replace package body &v_package_name' as text from dual union select line, case when line < ( select max(line) from user_source where name = upper('&v_package_name') and type = 'PACKAGE') then replace ( replace( replace(text, '%test('), ')') ,';', q'[ is begin ut.fail('Not yet written'); end;]') else text end as text from user_source where name = upper('&v_package_name') and type = 'PACKAGE' and line > 1 and ( regexp_replace(text, '[[:space:]]') not like '--\%%' escape '\' or regexp_replace(text, '[[:space:]]') like '--\%test%' escape '\' ) ) select text from package_skeleton order by line / |
In this case, the generated package body looks like this, and compiles straight away :
create or replace package body enrich_employees_ut as -- Department Lookup Succeeds procedure department_is_found is begin ut.fail('Not yet written'); end; -- Department does not exist procedure department_not_found is begin ut.fail('Not yet written'); end; -- Department ID is null procedure department_is_null is begin ut.fail('Not yet written'); end; -- Status is not PROCESS procedure status_not_process is begin ut.fail('Not yet written'); end; -- Job Lookup Succeeds procedure job_is_found is begin ut.fail('Not yet written'); end; -- Job does not exist procedure job_not_found is begin ut.fail('Not yet written'); end; -- Job ID is null procedure job_is_null is begin ut.fail('Not yet written'); end; end enrich_employees_ut; |
Data Setup and Helper Code
I prefer to create global variables to hold the test data.
This is because each test is likely to use a similar data set so, this way, the variables only need to be declared once.
As we’re dealing with table rows here, I’m just declaring a single record variable for each table.
This makes it very simple to add columns that we want to populate as they’re pre-declared as part of the record type.
For this test, my globals are :
g_emp employees_core%rowtype; g_job jobs%rowtype; g_dept departments%rowtype; g_result employees_core%rowtype;
Next, we need a procedure to initialise these globals :
procedure set_globals is begin -- start by setting the globals to the values required for the first test, which -- I usually make the test for the most commonly expected behaviour -- -- Values for Employees Core record -- -- Making numeric values negative means that they are less likely to clash -- with an existing sequence generated value g_emp.load_id := -1; g_emp.employee_id := -8; -- However wide the table, we only have to populate mandatory columns, and -- any columns we want for the tests... g_emp.department_id := -64; g_emp.record_status := 'PROCESS' ; -- Job Id is a Varchar - no constraints on it other than length... g_emp.job_id := 'WIZZARD' ; -- -- Values for the Department Lookup ( starting with ones that we expect to find) -- -- Values set independently of the EMPLOYEES_CORE values as we'll want to see what happens -- if they DON't match, as well as if they do. g_dept.department_id := -64; g_dept.department_name := 'Cruel and Unusual Geography' ; -- -- Values for the Job lookup -- g_job.job_id := 'WIZZARD' ; g_job.job_title := 'Professor' ; end set_globals; |
Then there is a procedure to create the test records using the global variable values.
This separation between initialising the globals and creating the records is needed so that we can “tweak” the values we use for each test as appropriate :
procedure setup_data is -- Populate the tables with our test data begin insert into employees_core values g_emp; insert into departments values g_dept; insert into jobs values g_job; commit ; end setup_data; |
This is followed by a procedure to retrieve the actual results of the program execution :
procedure fetch_results is cursor c_result is select * from employees_core where load_id = g_emp.load_id and employee_id = g_emp.employee_id; begin open c_result; fetch c_result into g_result; close c_result; end fetch_results; |
Finally, as we need to tidy up after we’ve finished, there’s a teardown procedure to remove any test records we’ve set up :
procedure teardown_data is -- Tidy up by removing the test data using unique values where possible. begin delete from employees_core where employee_id = g_emp.employee_id and load_id = g_emp.load_id; delete from departments where department_id = g_dept.department_id; delete from jobs where job_id = g_job.job_id; commit ; end teardown_data; |
Cut-n-Paste Driven Development
You might think we’ve done quite a bit of typing without writing any tests. The payoff for this up-front effort becomes apparent when you start on the first test, as you’re pretty much just calling everything you’ve already written and only need to add a couple of expectations, and an exception handler :
-- Department Lookup Succeeds procedure department_is_found is begin -- Setup set_globals; setup_data; -- Execute enrich_employees.department_details(g_emp.load_id); -- Get the actual results fetch_results; -- Validate ut.expect( g_result.department_name).to_(equal(g_dept.department_name)); ut.expect( g_result.record_status).to_(equal( 'PROCESS' )); -- Teardown teardown_data; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); ut.fail( 'Unexpected Error' ); teardown_data; end ; |
The exception handler is there to ensure that the teardown runs even if we hit an unexpected error.
This is more likely than usual as you’re developing the test code and (potentially) the code your testing iteratively when running these tests.
We can execute our test standalone :
set serverout on exec ut.run('enrich_employees_ut.department_is_found'); enrich_employees_ut Department Lookup Succeeds [.005 sec] Finished in .007019 seconds 1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s) PL/SQL procedure successfully completed.
The code for our second test is almost identical, apart from the change in one variable value and the expected results :
-- Department does not exist procedure department_not_found is begin -- Setup set_globals; -- Almost exactly the same as the first test excep... g_emp.department_id := -4096; setup_data; -- Execute enrich_employees.department_details(g_emp.load_id); fetch_results; -- Validate ut.expect( g_result.department_name).to_(be_null()); ut.expect( g_result.record_status).to_(equal( 'DEPARTMENT_ID_NOT_FOUND' )); -- Teardown teardown_data; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); ut.fail( 'Unexpected Error' ); teardown_data; end ; |
The rest of the test follow a broadly similar pattern.
The finished test package body looks like this :
create or replace package body enrich_employees_ut as -- Global variables for use in tests g_emp employees_core%rowtype; g_job jobs%rowtype; g_dept departments%rowtype; g_result employees_core%rowtype; procedure set_globals is begin g_emp.load_id := -1; g_emp.employee_id := -8; g_emp.department_id := -64; g_emp.record_status := 'PROCESS' ; g_emp.job_id := 'WIZZARD' ; g_dept.department_id := -64; g_dept.department_name := 'Cruel and Unusual Geography' ; g_job.job_id := 'WIZZARD' ; g_job.job_title := 'Professor' ; end set_globals; procedure setup_data is begin insert into employees_core values g_emp; insert into departments values g_dept; insert into jobs values g_job; commit ; end setup_data; procedure fetch_results is cursor c_result is select * from employees_core where load_id = g_emp.load_id and employee_id = g_emp.employee_id; begin open c_result; fetch c_result into g_result; close c_result; end fetch_results; procedure teardown_data is -- Tidy up by removing the test data using unique values where possible. begin delete from employees_core where employee_id = g_emp.employee_id and load_id = g_emp.load_id; delete from departments where department_id = g_dept.department_id; delete from jobs where job_id = g_job.job_id; commit ; end teardown_data; -- Department Lookup Succeeds procedure department_is_found is begin -- Setup set_globals; setup_data; -- Execute enrich_employees.department_details(g_emp.load_id); fetch_results; -- Validate ut.expect( g_result.department_name).to_(equal(g_dept.department_name)); ut.expect( g_result.record_status).to_(equal( 'PROCESS' )); -- Teardown teardown_data; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); ut.fail( 'Unexpected Error' ); teardown_data; end ; -- Department does not exist procedure department_not_found is begin -- Setup set_globals; -- Almost exactly the same as the first test excep... g_emp.department_id := -4096; setup_data; -- Execute enrich_employees.department_details(g_emp.load_id); fetch_results; -- Validate ut.expect( g_result.department_name).to_(be_null()); ut.expect( g_result.record_status).to_(equal( 'DEPARTMENT_ID_NOT_FOUND' )); -- Teardown teardown_data; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); ut.fail( 'Unexpected Error' ); teardown_data; end ; -- Department ID is null procedure department_is_null is begin -- Setup set_globals; -- Again, just a single change required : g_emp.department_id := null ; setup_data; -- Execute enrich_employees.department_details(g_emp.load_id); fetch_results; -- Validate ut.expect( g_result.department_name).to_(be_null()); ut.expect( g_result.record_status).to_(equal( 'DEPARTMENT_ID_NOT_FOUND' )); -- Teardown teardown_data; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); ut.fail( 'Unexpected Error' ); teardown_data; end ; -- Status is not PROCESS procedure status_not_process is begin -- Setup set_globals; -- This time set the status to prevent processing g_emp.record_status := 'SUSPENDED' ; setup_data; -- Execute enrich_employees.department_details(g_emp.load_id); fetch_results; -- Validate ut.expect( g_result.department_name).to_(be_null()); ut.expect( g_result.record_status).to_(equal( 'SUSPENDED' )); -- Teardown teardown_data; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); ut.fail( 'Unexpected Error' ); teardown_data; end ; -- JOB Lookup Succeeds procedure job_is_found is begin -- Setup -- We can use the default values here set_globals; setup_data; -- Execute enrich_employees.job_details(g_emp.load_id); fetch_results; -- Validate ut.expect( g_result.job_title).to_(equal(g_job.job_title)); -- Teardown teardown_data; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); ut.fail( 'Unexpected Error' ); teardown_data; end ; -- Job does not exist procedure job_not_found is begin -- Setup set_globals; g_emp.job_id := -32768; setup_data; -- Execute enrich_employees.job_details(g_emp.load_id); -- Get the actual results fetch_results; -- Validate ut.expect( g_result.job_title).to_(be_null()); ut.expect( g_result.record_status).to_(equal( 'PROCESS' )); -- Teardown teardown_data; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); ut.fail( 'Unexpected Error' ); teardown_data; end ; -- Job ID is null procedure job_is_null is begin -- Setup set_globals; g_emp.job_id := null ; setup_data; -- Execute enrich_employees.job_details(g_emp.load_id); fetch_results; -- Validate ut.expect( g_result.job_title).to_(be_null()); ut.expect( g_result.record_status).to_(equal( 'PROCESS' )); -- Teardown teardown_data; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); ut.fail( 'Unexpected Error' ); teardown_data; end ; end enrich_employees_ut; |
To run all of the tests in a suite, I usually use a script like this, which handles recompilation of either the test package or the code unit being tested without raising all those pesky ORA-4068 errors :
exec dbms_session.modify_package_state(dbms_session.reinitialize); clear screen set serveroutput on size unlimited exec ut.run('enrich_employees_ut'); enrich_employees_ut Department Lookup Succeeds [.004 sec] Department does not exist [.003 sec] Department ID is null [.003 sec] Status is not PROCESS [.003 sec] Job Lookup Succeeds [.003 sec] Job does not exist [.003 sec] Job ID is null [.003 sec] Finished in .026568 seconds 7 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s) PL/SQL procedure successfully completed.
Of course I will probably still need to run a batch at some point if I want to perform a full System Test and (optionally) a load test. For now however, I can be confident that my code is at least functionally correct.
Author: mikesmithers
Back in 1993, I discovered that I could get paid money for doing fun stuff with computers. Over the years, I've specialised in Oracle Databses as a developer, a DBA and sometimes, an architect. It's my evil alter-ego - The Antikyte - who writes a blog about my various technical adventures. Yes, that is his Death Star parked in the Disabled Bay. I currently live in the South-West of England with Deb, my long-suffering wife. View all posts by mikesmithers
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK