Snippets and Thoughts of One Oracle Developer

W Welcome to the last part of the unit testing serial, the gamification part. If you are still reading this then congratulations, you have the most boring parts behind you. How to keep writing tests fun? Let's think about it. As a leader/manager you (should) want your code covered by meaningful tests which won't costs you a lot of money. You don't want useless tests. As a developer you don't want to create too many tests but you want to cover at least the most risky/fragile parts of your code. Gamification is the answer. I created some packages for a tower defence game. Then I want to make sure I wont break my code in the future or at least I would be notified when I do before it reaches users. My procedures are usually short but there are some exceptions. Also they usually do more than one thing so they require more than one test. I wrote some tests and realised it is like being half blind. That led me to the code coverage feature ( DBMS_PLSQL_CODE_COVERAGE ) intro

H Here is a link to previous article about  Test patterns . Today you should learn how you actually run the tests and how to keep them organized. How to run your tests If you didn't figured this out last time, this is how you run your tests: BEGIN ut.run(); --ut.run('PLAYER_UT'); -- run just specific package COMMIT; END; / And here is how the test package specification may looks like: CREATE OR REPLACE PACKAGE player_ut AS -- %suite -- %rollback(manual) -- %beforeeach(before_each) -- %aftereach(after_each) -- %beforeall(before_all) -- %afterall(after_all) PROCEDURE before_all; PROCEDURE after_all; PROCEDURE before_each; PROCEDURE after_each; -- %test PROCEDURE test_create_player; -- %test -- %throws(PROGRAM_ERROR) PROCEDURE test_create_player#duplicite_name; -- %test PROCEDURE test_notifications; END; / Organization tips You should have all tests independent on each

T This part will be about test patterns and it is a follow up of previous part about Challenges . You can also use unit_tests tag/label to explore all related articles. Prerequisites You need to install utPLSQL  package. It is a great project which will make all of this much easier. There is an  Install guide , a lot of Examples  and of course a Documentation  on projects GitHub. On my DEV environment I use headless installation to "UT3" schema and I suggest you to start with that. Test patterns There is just few patterns you need. test single value (including NULLs) test multiple values (whole data sets - multiple rows and columns) test if exception was raised Single values Let's go thru first example: CREATE OR REPLACE PACKAGE BODY player_ut AS test_player_name players.player_name%TYPE := 'PLAYER_NAME'; PROCEDURE test_create_player AS curr_player_name players.player_name%TYPE; BEGIN -- delete player

T This part will be about challenges of unit testing in database world. Next parts will be about: Test patterns Organization tips Gamification Challenges Database unit testing is in very similar and sad position as database (application) security - often overlooked and unappreciated. From my experience developers just don't know where to start, they don't believe in the value of tests in database, they don't know how to make their spaghetti code testable and/or they fear about the amount of time required for proper testing. They get interested only when their issues on project are recurring but they give up quickly anyway. Here is my approach and recommendation. Why even bother with tests? You can find a lot of theory elsewhere, but the main benefit I get from tests is an assurance (more like visibility) that if you change something you don't brake anything else. You can and you will brake something else eventually but if you have the safety net of unit tests you will k

T Today Oracle announced free OCI training + free certifications if you manage to get them till the end of this year. Autonomous database specialist is included. You can check the announcement here  or you can explore all available certifications . For more info about 2021 certifications you can explore this article . Happy learning. My plans for October Oracle Cloud Infrastructure Foundations 2021 Associate ( 1Z0-1085-21 ) Oracle Autonomous Database Cloud 2021 Specialist ( 1Z0-931-21 ) November Oracle Cloud Infrastructure 2021 Architect Associate ( 1Z0-1072-21 ) Oracle Cloud Infrastructure 2021 Architect Professional ( 1Z0-997-21 ) December Just one on these, I wish I could squeeze in more: Oracle Cloud Platform Enterprise Analytics 2021 Specialist ( 1Z0-1041-21 ) Oracle Cloud Platform Identity and Security Management 2021 Associate ( 1Z0-1070-21 ) Oracle Cloud Infrastructure 2021 Cloud Operations Associate ( 1Z0-1067-21 )