Updated 8 August 2023
This feature has been removed, instead you can use the utPLSQL extension for SQL Developer.
Wait, what is utPLSQL? It’s an open source library framework for writing PL/SQL unit test cases, implemented by writing PL/SQL. So it’s less GUI than SQL Developer’s feature was, but it’s MUCH MORE flexible and powerful.
If you’re on an older copy of SQL Developer with this feature still intact…
If you want to unit test your PL/SQL, then Oracle SQL Developer can help make that easier for you. I’m not going to try to convince you to test your code, but if you’re already rearing to go, then read on.
So I have a long history with unit testing tools for PL/SQL, but I won’t bore you with that. What I will do, is give you a teaser, and show you a few features that I really appreciated as a person who doesn’t like to code to test code.
SQL Developer’s unit testing features REQUIRES a repository. However, it only requires ONE repository. So you do not have to set this up for every instance you want to test – you only set it up once.
To get started, open the Unit Test panel under the ‘View’ menu.
Then on the Tools Menu, access the Unit Test flyout.
Once the repository is setup, the Unit Test panel will ‘know’ where it is going forward.
Now you are ready to build and run your tests!
The Sample Test and Some Cool Stuff
The Help has a chapter on the Unit Testing, and an entire section that steps you through building a test, using AWARD_BONUS() in the HR schema. So you’ll need HR.
To build a test, the easiest way to start is to just right-click on your program:
Now, instead of taking you through the ins and outs, I thought I would just call out a couple of cool features I ‘discovered’ while playing with the feature. Things that made me go, oh the users will LOVE that – that kind of stuff.
Startup and Teardowns
Before you run a test, you might want to do some ‘stuff.’ And after you run a test, you might want to undo some ‘stuff.’ The tool makes it pretty easy to define these, and even gives you an interface for backing up an entire table or portion of a table so you can get ‘the data back’ after you run your tests.
So to save off some data:
And then after your test is done, decide what you want to do with it:
Code Coverage Reports
Testing is good, but if you have 100 test cases for your package but only ‘exercised’ 25% of your program, then you still have a lot of ground left to cover. In SQL Developer, you only need to check a single box to collect this data:
If yes, then:
Running Tests from the Command Line
So you define your tests until you’re blue in the face, and then what? You probably want to run your tests each time your program changes. So, what you want to do is build the unit testing into your automated build process. Our CLI makes that pretty straightforward.
Test Re-Use
Kinda like code-reuse, but with testing. So, if you define a tear down, or if you define a bit of code for a test, or a query for defining a list of inputs..you can take that and add it a library. Then you can use this over and over in your tests. It’s not only convenient, it encourages more testing, and it cuts down on errors – assuming your library items aren’t buggy 🙂
There’s so, so, so much more to talk about. Like how to actually do the tests 🙂 And I’ll talk about that next.
78 Comments
Hi Jeff,
I can not find any parameters in specify parameters.
My procedure has some input parameters. But I can not find any of them in specify parameters step.
Please advise.
Thanks
Aaron
Are you able to see the parameters if you go to debug or execute the procedure in SQL Developer?
Hello Jeff,
Below is my use case. I have a stored procedure which processes data from set of 3-4 tables(SRC1, SRC2, SRC3…) and infers information and generates some derived data that is stored in a Table “TRG”.
I need to validate if the data derived is correct.
For this,
startup scripts :
Create External Tables and load data to SRC1, SRC2, SRC3..tables from a CSV. I am using EXECUTE immediate to create an External table on the fly…these external table and structure has been wrapped in a PL\SQL pkg that has been created on the Unit Test USer schema itself which we use to run the Tests.
Eg :
DECLARE
P_FILE_NAME VARCHAR2(200);
BEGIN
P_FILE_NAME := ‘ExpectedAWS.csv’;
XXT_CREATE_EXTTAB_PKG.CREATE_EXPECTED_AWS_EXTTAB(P_FILE_NAME => P_FILE_NAME);
END;
Verify : Run the stored procedure that will actually derive and store data into TRG.
Validate : The data from TRG will be compared against expected results which will be stored in an other external table
Teardowns : Will delete data that has been loaded into SRC1, SRC2…and TRG. Also it will drop the External tables that have been created.
** The above approach of startup scripts worked well locally on when repository was created on a local DB Server(Personal Oracle Database 12c Release 12.1.0.1.0 – 64bit Production)**
It does not work on a version installed on DBCS on the cloud.(Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production)
Following is the error : The Unit Test Repository does not recognise a PL\SQL object created on the user where Unit Test Repository is created.
The anonymous block runs fine when run standalone in SQL Developer
Startup LoadEpxectedAWS (1/1) failed: ORA-06550: line 6, column 3:
PLS-00201: identifier ‘XXT_CREATE_EXTTAB_PKG.CREATE_EXPECTED_AWS_EXTTAB’ must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
Hello
great tuto. I would like to ask you something about testing on PLSQL:
– which framework for plsql test (utplsql, ??)
– how to check PLSQL quality code on development step (as we do for java using SUN java rules ?)
thanks in advance
Teo
Hello,
please it is possible to export test results in some special format? Or only way how to report the test are with “Unit Tests – Reports” part?
Thank You
Are there any plans to support testing procedures and functions that have parameters with user defined object types?
Quick question, is there a specific privilege or role needed to create a repository; I try to create the repository and keep getting a message that selected connection does not have required permission, which follows a prompt to log in as sys. Have no access to sys and trying to find the role and/or privilege, so I can ask dba for it.
Thanks!
it’s probably trying to grant quota on a tablespace, try View > Log > statements and you can see what’s being executed
Thanks, that showed me that it’s not meeting the minimum requirements, since have not been granted RESOURCE role, the last sql that runs is the basic_privs_granted decode query, is there a way around not having that privilege, since have been told by dba that role can’t be granted.
just ask the DBA to install it for you, or install it locally somewhere else, get the scripts, and then manually install it in your db
Thanks for your help, ended up installing a local db and export all objects, it took me a while to get the db installed locally, but after that we are ready for TDD.
Hi, I am using sample code provided in “Help Center” to test Unit Testing. It gives me error as below.
APPS: Run On – 2016-03-08 17:13:22.711 ERROR 218.0 AWARD_BONUS failed: Startup Table or Row Copy (1/1) failed: ORA-00942: table or view does not exist
Implementation – Test Implementation 1 ERROR 218.0 Startup Table or Row Copy (1/1) failed: ORA-00942: table or view does not exist
Startups ERROR 218.0 Startup Table or Row Copy (1/1) failed: ORA-00942: table or view does not exist
Any idea of getting this error?
Hi,
I posted this over a year ago on the forums (https://community.oracle.com/thread/3635194) but haven’t got any reply.
I haven’t tested the UT since – so if things changed i’d be glad to know.
My company wants to start using SQL Developer as our DB Unit Testing tool.
However, we are not quite sure how to maintain it in our source control, and how to use the files once they are there.
I’ll explain:
1. Granularity of items kept in Source Control:
When Exporting a Suite – the generated XML file contains the Suite definition but also the definitions all of it’s linked Suites, Tests, Libraries etc.
We thought about Using a root Suite to run all the other Suites and Tests in one go and keep that in in our source control- but this is problematic since each developer would be locking the entire UT tree for his/her modifications – No working in parallel (SQL Developer doesn’t provide means for merging or comparing exported UT XMLs or UT repositories).
So now we are thinking about saving all of the pieces separately – Tests and Library definitions, and not working with Suites at all. This would allow developers to check in just what they are working on and not interfere other developers.
The problem with this approach is that it would more troublesome to import all of the separate files, let alone having to run multiple (can get to hundreds of) tests instead of a single Suite (I also assume running would take more time).
It would have been best if there was an option to export Suites with just the references to the used items but without their actual definition, or alternatively, some way to automatically generate Suites for imported tests according to some rules.
2. Repositories:
Is it better to have a single master UT repository for all developers to use for their testing – or each developer working on his own repository (importing from the master) to not interfere/override someone else’s work?
Since Repository creation can’t be done through CLI (command line interface)- I assume duplicating the UT Repository schema would work.
3. CLI
There are a bunch of things doable from the GUI, but aren’t doable through CLI.
This puts sticks in the wheels of trying to automate the unit testing process – which leads us to sometime reverse engineer what the GUI does and implement it on our own using packages on the UT repository (in example – purging the repository or the results).
Any guidance would be appreciated.
Thanks.
Mor
what do you want to do via the CLI that’s only avail via the GUI?
to deploy the repository, I would probably just copy/clone it, as you suggested
and then one or many repositories…maybe both? devs could have their own, and when they were checking code into source control, i would also check in my unit tests.
First – thanks for replying.
Second – If you re-read your reply – I do hope you see there is something problematic in the way UT is implemented (or at least the export import part) if we have to do a mixture of both just to be able to not interfere with each other’s work. I am not aware of any other tool requiring this work method just to be able to keep things in a source control…
As stated in my post above – we wanted to be able to Purge the Repository or the previous Run Result using CLI – both are achievable through the Tools->Unit Test menu but not through CLI.
-Mor
Hi Mor
First I’m interested in SQLdev Unit testing but I must admit I’ve never used it for any real project so I will just try to provide guidance.
The problem related to source control you are facing looks like those encountered with Oracle APEX for example.
From what I read, in Apex there is an utility that export all the Datas related to an application (which in UT will be your repository) then you can split this export file into “1 sql file by module” which can be versioned in the VCS (version control system). The tool is called APEXExportSplitter.
I’m talking about it because I feel like you are facing the same challenge, which should be common to all dev/test tools storing their definition in a database.
so maybe you can Export Tests and Suites as SQL scripts from each developer computer, working with XE, commit them in SVN for example. Then they will be imported in the Server dedicated to testing and run automatically.
The SQLdev UT tool seems interesting but in fact, challenges like the one you encountered really need to be adressed.
Do you have any plan to allow integration of this with any CI tools like Hudson, Jenkins, etc.
you have a command line interface, what’s preventing you from integrating it with Hudson or Jenkins?
But how to integrate with Jenkins if one must use the command line tool (sdcli) interactively to send passwords?
Is there any other way to fully automate the use of the cli?
So, I’ve found an answer to my own question… It might help someone else.
This works on my machine, a Windows 7 workstation.
I just used sqldeveloper to edit my connections and put the passwords there. Now, when I run sdcli to run the unit tests I am not asked about entering passwords.
Now I’ll try to integrate with Jenkins also on my workstation just for testing. I guess I’ll have problems when trying to use a proper CI server on other machine, perhaps it will not have sqldevelper or I might not be able to edit the connections…
Hi,
Did you find an answer for this? I am also trying to integrate it with Jenkins. I am wondering on how to do it without installing the sql developer in jenkins server.
Appreciate your help if you find any alternative option.
Something else to be aware of: I installed SQL Developer and saved the passwords for the unit testing connections. However, they didn’t become available to the command line utility until I exited the original application in which I saved the passwords. Spent several hours unsuccessfully trying to supply the passwords to sdcli via batch file and power shell scripts before I worked it out.
Correct, you only had the connections avail in the memory space of the GUI, when you closed SQLDev, it then wrote out the connection info to the files which the CLI could then access.
Hi All,
Once the Oracle programmer develops a package in development Instance and upload in PVCS. How does the code gets reflected in Testing Instance?
Should the testing team create the package again in Testing instance?
If they connect to dev instance using DB Link, the tesing will be done on DEV data , which doesnt differ from Unit testing…..
Plz clarify…..
Thanks in advance
Hi Jeff,
I started to use Unit Testing in SQL Developer recently, and I am stuck with the test where I expect exception. We have user-defined ORA codes (in the range of [20000, 20999]) that we raise with RAISE_APPLICATION_ERROR when needed. I wanted to make a test for such case. I have expected to get the error 20128, so I selected Exception from the “Expected result” drop down list, and put the value 20128 next to it. However, the test was not successful:
Execution Results
ERROR
Expected exception: [20128], Received: [20128: ORA-20128: Invalid Username/Password! Please check and try again.
ORA-06512: at “CUSTOMER.CUSTOMERLOGIN”, line 256
ORA-06512: at line 2
]
After that I tried to leave the default ANY in the field next to the Exception in order to at least verify that any exception occurred, but it was not successful as well:
Execution Results
ERROR
Expected exception: [ANY], Received: [20128: ORA-20128: Invalid Username/Password! Please check and try again.
ORA-06512: at “CUSTOMER.CUSTOMERLOGIN”, line 256
ORA-06512: at line 2
]
The version of SQL Developer I use is 4.1.1.19.
Could you, please, help me with this issue?
Thanks,
Ivan
Did you try -20128 instead of 20128? The user defined exception range is -20000 to -21000.
To be accurate, the range for user-defined exceptions is -20000 to -20999.
I’ve just tried -20128, but with no success again:
Execution Results
ERROR
Expected exception: [-20128], Received: [20128: ORA-20128: Invalid Username/Password! Please check and try again.
ORA-06512: at “CUSTOMER.CUSTOMERLOGIN”, line 256
ORA-06512: at line 2
]
In PL/SQL I raise exception like this:
RAISE_APPLICATION_ERROR (-20128, lvMsg);
Seems like those user defined exceptions are not recognized well by the SQL Developer, as even if I put ANY as the expected exception, same problem occurs.
Hi all,
It seems I found out where the bug is in the Unit Testing tool. I experimented a bit with my procedure, and I noticed that in case one of the OUT parameters of the procedure is of VARCHAR2 type, exception 20128 is not recognized.
However, in case I remove all VARCHAR2 OUT parameters, and leave only NUMBER and DATE OUT parameters in my procedure, exception 20128 is recognized either when I put 20128 as the expected exception, or when I put ANY.
Jeff, could you, please, test this scenario on your side in order to confirm whether there is a bug in the Unit Test tool?
Same problem happened to me testing a function returning a varchar2, any solutions around?
Code Coverage statistics in Unit Testing feature set seems incorrect
Need help on SQL support for Customizing the Pre-defined SQL Scripts on SQL Developer DB Unit testing Code Coverage Report .
In the attached Screen shot its not reaching the 100 % lines code coverage ,Due to highlighted lines in the following stored procedure code coverage report we are covering only 50% code coverage only.
And the following Stored procedure have 10 lines of code but its counting the 13 lines . If we avoid highlighted lines will get the 100 % of Code coverage in Stored procedures.
When I open the Unit Test menu, everything is grayed out and un-selectable. I’m using SQL Developer v. 4.0.2. Is there a step I’m missing to enable it? I’m actively logged in.
View>>Unit Test must be selected first. Got it
Thanks for helping to bring database programing into the unit testing fold!
Lance
Thanks for your reply. But that approach just adds another layer I have to install on my target database before running the tests stored in my repository and therefore does not seem very handy to me.
I would prefer a solution where I can test anonymous blocks with binds. So I could assign (in)binds and check the values of the (out)binds afterwards. I would get the flexibility to test types or whatever I want without the need to install anything on the target database. Everything belongig to the test would be stored in the repository.
Hi Jeff,
at the Moment I have no possibility to build unit tests in SQL Developer for my types. How would you test the logic of types in SQL Developer?
Are there any plans to support unit testing for types?
Thanks
Malte
You would test them via wrapper procedures – just like with the debugger, you can’t make type calls directly, so you’d have to do so with a proc or function.
Hi Jeff,
I am told by our AD Technology to create a suite of “automated” unit tests in SQL*Developer (we have 3.0.04).
I followed the tutorial and created a unit test, ran it, but I don’t understand some of the concepts.
My biggest issue is, how does SQL*Developer “validate” the results.
So, taking the award_bonus as an example.
I give the inputs as emp_id = 1 and gross_sales as 10000.
Now, emp_id 1’s comm_pct is 0.1%. So, for this sales, he should get a comm of 1000. His current salary is 5000. So, the award_bonus should increase his salary to 6000, right.
(1.) Now, my question is, when I create a unit test in SD, do I “HAVE TO KNOW” that it will increase the salary of emp_id = 1 from 5000, to 6000 for gross_sales 10000 and comm pct 0.1%? Do I have to know this?
(2.) Ok, in the SD unit test creation wizard, I give emp_id = 1 and gross_sales 10000 as inputs. So, when I go to the next screen, it is asking me how to test the result. Here, I type a query: SELECT COUNT(*) FROM emp where emp_id = 1 and salary = 6000. i.e. When this test is run, the employees salary will be increased to 6000.
I don’t give any teardown. I run the unit test. It says success. Now, I run the test again. In my table the salary has increased to 7000. And my test says SUCCESS. But, the query I gave to validate, will give count(*) = 0, since his salary is not 6000 anymore. So, how does this work? How, does SQL*Developer know it was success.
I have asked this in the Oracle Forums as well, but nobody has answered: https://community.oracle.com/thread/3623090
I would be most greatful if you could explain what is happening here..
Jeff, this blog led me to follow through the on-line tutorial, Thanks!
1) I did not see complex data types such as XMLTYPE, SDO_GEOMETRY in the list of supported datatypes for Lookups. Is there a plan to support it?
2) I recommend moving “Lookup Catagory” as a column in the Implementation grid so each parameter can be associated its own Lookup.
3) I could not figure out how to validate my proc or function that returns a CLOB. Any suggestion?
Bo
I received the following error when I try to create de unit test:
The type PL/SQL RECORD of argument MY_RECROD_VAR is not supported.
What can I do?
Hi Jeff,
I am following this article trying to figure out how unit test in SQL Developer works, but I am stuck at the very first step, which is when I go to Tools->Unit Tests-> all options under unit tests grayed out, meaning none of them is available. Apparently I can’t go anywhere from here as I can’t even create a new test repository.
I tested SQL Developer 4.0.2.15 on both Windows and Mac and received the same issue. Did I do anything wrong? Actually I barely started.
Please advise.
Thanks
Aaron
Go to View > Unit Test
And then try again…
What happened to part 2 of this tutorial? I don’t see it in your blog.
I haven’t written it yet. What would you like to see?
So, when will we see “next”?
There’s so, so, so much more to talk about. Like how to actually do the tests 🙂 And I’ll talk about that next.
Soon’ish. It’s not my favorite topic, but then I do love ALL my children.
Don’t we all! 🙂
Is it true that to install a Unit Test Repository you need the SYS password? That’s a big bummer since all of my test databases on are Amazon RDS — no SYS password 🙁
Shouldn’t need SYS – but you don’t even have to do that. Install the Unit Test Repos on your local instance. You can run the tests anywhere then – there’s no requirement to have the repository on the database you want to test.
Jeff,
I love the tool and have implemented a suite of suites for testing our database procedures.
I’d like to see some examples on setting up testing of more complex scenarios test scenarios.
For example, we have a database with a container object, which we would want to create. Then populate it with the objects that reside in the container, test operations on the container with these objects in it, and the objects in the container, then clean up the container and the contents at the end.
I would like to find a way to create a test suite that does the setup of the container (itself a test), then adds various flavors of contents to the container (itself a series of tests), and allows a series of tests on the contents and the container. After that, the contents is torn down (itself a test), and the container is torn down (also a test).
Right now, the only way I can see to do this is to create pseudo tests that do a portion of each of the above, in series. However, the setup and teardown need to be separate so the other tests can occur before the teardown. Maybe (probably) I am missing something.
Another way to look at it is that am looking for a way to have nested tests that call additional tests after they run but before they do their teardown.
An example of something like this would be VERY helpful. Right now I am considering creating some kind of test harness in a package that manages this for me, but I’m hoping there is an easier way.
Your Steven Feuerstein comment prompted me to see what he’s up to and I’m amazed to see that he’s rejoined Oracle! Looking forward to see what he brings to the table!
Unit testing? What? I do all my coding in production…
/s
Nice tip… I’ve never really had a use for it though.
Unit Testing is a great SQL Developer feature. Do you know if there are any plans to make it better?
You mean, to make it more awesome?
Yes, we’re doing active dev on it right now. And this guy Steven Feurestein is excited about unit testing for some reason, so I expect some things to happen 🙂