[This set up has been tested on SQL Developer 18.3 and Oracle RDBMS 11G. I strongly suggest you not to use any version of SQL Developer older than 18.3 because there are bugs that prevent testing exceptions in functions]
It happened a few days ago. I woke up and decided “Enough! Now I really want to set up a proper database unit testing environment for SQL Developer, fixing all the obstacles by myself!”. The point is that by following Oracle instructions you can quickly get your unit test repository up and running for your small local project but for sure not in a corporate environment where more requirements and restrictions apply.
What I mean is: how likely is to have SYS database access, which is required (and lightly overlooked by Oracle as a requirement) in order to create the UT repository with SQL Developer? Even if you do have SYS access, I strongly encourage you to go through the article because there are some tweaks that might be useful to make your environment work better.
This post wants to be a full set up guide to help who wants to get serious about unit testing with SQL Developer and set up a proper repository to use in a corporate production environment. I’ve spent quite some time to solve different issues with SQL Developer, find a way to make it work in a real multi-project environment, understand what’s under the hood and overcome some product limitations. This post is the result of all my findings in this matter. Obviously, I’ve also taken some decisions you can agree with or not, feel free to change anything according to your needs.
Obviously, if you have SYS access and/or want to set up the unit testing framework for just one project, you can skip the initial steps and fly directly to the Unix configuration section.
I really hope this can help you save time and be up and running quickly with a big smile on your face.
Go grab a coffee and let’s start.
As we know Oracle provides several nice tools to debug our PlSql code and even a profiler to get all the details about code execution. But what if you want to use your own debug code and be able to switch it on for specific sessions whenever you want? And I mean, obviously, without changing the code! I’ve seen around some examples in which people use a configuration table to do that: the debug code keeps querying the table and logs the output only if the flag is Y. This way of doing it is absolutely fine and works well but I was looking for something more elegant that does not involve a table. My idea takes advantage of the Oracle application contexts and it’s really simple.
In my personal experience, quite often I came across the need to test and optimize queries used by REF CURSORS. Sometimes it’s a really painless operation, other times it can really be a very long and complex operation that can make you waste a lot of time. Let’s see what we would normally do in a simple case: consider a procedure GET_DATA which returns a SYS_REFCURSOR
create or replace procedure get_data(pi_table_name in VARCHAR2,
po_resultset out SYS_REFCURSOR) is
open po_resultset for
select atc.OWNER, atc.COLUMN_NAME, atc.DATA_TYPE
from all_tab_columns atc
where table_name = pi_table_name
order by column_id;
The query used for the ref cursor is quite simple, it extracts 3 columns and uses just one parameter as a filter. In such case, if I wanted to test the query, I would probably take it outside and run it manually to perform my test.
INTERVAL partitioning has been introduced by Oracle as an extension of RANGE partitioning. There are few limitations like the fact that’s not supported at subpartition level and the partitioning key has to be a DATE or NUMBER but also some interesting advantages. When a table is partitioned by INTERVAL, in fact, partitions are created automatically as data is loaded into the table so we don’t have to bother creating anything in advance. We might, anyway, prefer to maintain our historical data in tables that are partitioned by RANGE. This because it’s common to keep inside the system a “rolling window” (e.g. last 90 days) of historical data meaning oldest partitions are dropped as soon as they become obsolete and with INTERVAL partitioning the last partition in the range section cannot be dropped. Moreover, in many processes it’s preferrable to have a full control of which partitions are created because data loaded into the system is not guaranteed to be “clean” and rows that don’t map to existing partitions have to be rejected.
After all these considerations, let’s have a look at what we can do to make the best out of both partitioning options (tested on 11g).
In this post I’ll show how incredibly easy is to read data from a table by having only the INDEX privilege granted on it. It’s something I found out when preparing a demo about Oracle Virtual Private Database, you’ll see why later on in this post.
Let’s imagine the following scenario: user SCOTT owns a table CUSTOMERS_CC holding sensitive information about customers
SCOTT> select * from CUSTOMER_CC;
CUST_ID CUST_NAME CC_NUMBER
---------- -------------------- --------------------
1 John Smith 1234-5899-7458-1111
2 Brenda Lipson 5093-1214-0875-1496
3 Mark Walfish 9905-8465-3208-5478
4 Artur Tork 5521-4785-0033-7408
5 Robert Koczinski 1457-9650-0897-0000
The RETURNING INTO clause is a very convenient way to retrieve values from rows that are being inserted/deleted/updated. When used with UPDATE and DELETE statements, it’s possible to retrieve values of multiple rows by using the RETURNING BULK COLLECT INTO. This is something that does not work with the INSERT statements: in that case you will be able to only return values from a single row.
Let’s see how this works and create a simple test table that will be the target of our DML operations
create table mytab(id number(3),
insert into mytab
(select rownum, date '2016-01-01' + round(dbms_random.value(0, 30)), 'CLI'||round(dbms_random.value(100, 999))||upper(dbms_random.string('A', 3))
connect by level <= 100);
First of all let’s use a simple case just to have a look at how this works.
Not everybody knows that in SQL Plus the character # (called hash or pound) has a special use that I discovered it myself not much time ago. Well, it’s not really a secret, you can read about it on the official SQL Plus manual, but it’s rarely used. The sign #, in fact, is the default character used as SQLPREFIX during a SQL Plus session and can be changed executing the command
SET SQLPRE[FIX] prefix_char
But what does it do? It tells SQL Plus to execute a SQL Plus command while in the middle of entering a SQL statement or a Pl/Sql block of code.
For example you are writing a select statement
SQL> select empno, ename,
and, while writing down the list of columns to extract, you don’t remember the name of one of them. Normally you would quit writing the query, execute a desc of the table and start writing it again from scratch. But the # sign give us another possibility. All you have to do is enter a new line, digit the magic sign # and write the desc you need
I’m pretty sure that everybody at least once in his professional coding life had the need to convert a string of character separated values into tokens or rows.
Let’s be honest, we all ended writing something like
for k in 1..length(mystring) loop
if substr(mystring), k, 1) = ',' then
strBuffer := strBuffer || substr(mystring, k, 1);
Problem is that among all those “bla” it’s quite easy to forget something (is there always a separator at the very end of the string? Any space between values and separators? Did I clean the buffer?) and in such code bugs might find a comfortable home.
Why should we write such risky code to do something that can be achieved differently in a completely reliable, fast and elegant way? And all of this just using one powerful feature that Oracle kindly introduced with version 10g (drum roll): regular expressions!