Use Oracle Data Base in Command Line

Cadmus Asks the Delphic Oracle Where He Can Find his Sister, Europa by Hendrik Goltzius, 1615

Sometimes I need to make some quick changes in data base. Or I need to do several changes using some patterns and templates. In that case I don't want to open editor (I use DataGrip) and wait for loading all resources, connecting to DB, opening SQL window and inserting or copying SQL query. I am that kind of person who keeps open only those applications that I am going to use in next 30 min. If I don't plan to use something in next half an hour I close it, so every opening takes some time and effort.

The best way to save time and effort is command line or, actually, scripts. SQLPlus is a great tool for using Oracle data base in the console. You can find instructions about installing SQLPlus on MacOS on StackOverflow: Oracle Sqlplus client on Mac.

The annoying thing about using data base in the terminal is connecting: you have to remember and write all credentials of data base every time you want to connect to it. But, as usually, scripting resolves this problem. I wrote simple Bash script connect-to-oracle-db.sh, that opens connection using small alias as an input. For example, I write sql test in terminal and it opens connection with test data base where I can write some SQL right ahead. Instead of test it can be any base that I work with: demo, live, production etc.

Second script that I use is changing some value in specific data base in specific table. Some times one of the clients asks me to change specific data in their DB, so now instead of opening an editor I can just run a script with parameter given by client and it changes all the data, which saves me time and doesn't disturb my attention on other tasks so much. The script looks something like that:


Of course, SQLPlus doesn't replace editors like SQL Developer or DataGrip, but it can save a lot of time and effort in performing small and routine tasks.

Some Examples Of Testing Tables And Views In Database

In our project testers are using database (DB) all the time. We query/modify/delete data in test databases to check data flows, to configure something or to just make testing faster (sometimes it's way faster to change some value in DB than to change it through application).

That means, that testers can also test the DB objects – tables and views.

Usually I test them after creation – if I have to test some new component in which the table or view has been added/modified, then I always test this table or view. And in this post I write about some common but not trivial problems that I see at work.

Popular question: should I test DB object, if .sql file of creation was made by analyst?
Definitely yes, you should. Generally analysts don't know much about DB rules and they may not see what problems may create "smelly" structure. Not to mention the fact, that you should always test not only developers, but analysts (and everyone!).

How can I test tables?
Here are some concrete tips that I haven't seen in other articles.

• If there is field like STATUS and field like IS_DELETED find out what's the difference between them (if you want to delete row should you set STATUS=DELETED or IS_DELETED=Y)? Often they are used for the same purpose and you actually don't need one of them. If you keep both and they do the same thing you will end up to change always both values, which is double work.
• If there is some value with foreign key its type and size should be the same as primary key to which it refers. Example:
  table USER has PK column NAME(VARCHAR2 55)
  table ACCOUNT has FK column USER_NAME(VARCHAR2 50)type of ACCOUNT.USER_NAME is wrong, because it's shorter than the column which it refers to. So you can insert some long username into USER table, but you won't be able to create account for that, because ACCOUNT table can't save that long username.
• Column type and size should match the data. For example, it seems like column for the telephone number should be the NUMBER, but actually people want to write their telephone numbers with spaces and dashes, so the column type should be rather VARCHAR (you should also check does appropriate application field has necessary constraints – you shouldn't allow to users insert symbols into field that saves data into NUMBER column).
• NULLABLE property should match the logic. If some field is not mandatory in the application, then appropriate column should not be NOT NULL.
• Almost all tables have (or should have) primary or foreign keys, so you should understand them. If some column refers to the data from another column – it should be the FK for sure.
• Table and column names should be logical and clear without documentation. This may seem obvious but a lot of testers are afraid to ask to change the names, because they thing that it's not important (we already have bad name, ok then, nobody wants to change it, lets move on). But actually it is important, because good names saves your time in the future when you need to come back to this table and can't remember what was this bad name for.
• Learn about database normalization. There are some rules in organizing tables and fields to minimize redundancy.

How can I test views?
Testing view means basically testing the SQL query what creates the view. So you need to know SQL to test does query return all necessary data and doesn't return unnecessary.

• Double rows with identical data is common problem in queries with OUTER JOIN conditions. View shouldn't return double rows even if it seems harmless (you never know how you gonna use this view next month, maybe you need to count all rows).
• You need to understand difference between OUTER and INNER JOIN. OUTER JOIN may return more data than you need, INNER JOIN is faster.
• Usually you don't want to see cancelled or deleted rows in view. Analyst may not write that condition in specs, because he thinks it's too obvious and developer may not write this condition, because it wasn't mentioned in specs. So be sure you understand the purpose of view, to decide should it return cancelled rows or not.
• You may have some table with properties or parameters in your DB and view can take some parameters from this table. In that case it's possible that in test DB you have (and always had) only one parameter, but in live – two or more. Developers often don't have permissions of live DB, so they don't know the real data. Be sure that query can work with multiple rows in this parameter's table (maybe developer used = instead of IN).
• Be sure that data in the view is logically correct. For example, I had once one view with two columns in it: DOES_DOCUMENT_EXIST and DURATION_OF_DOCUMENT. And I found some rows where DOES_DOCUMENT_EXISTS was N, but DURATION_OF_DOCUMENT > 0 – obviously if document doesn't exist duration should be 0. In that case error can be in raw data in tables, not in view (then you should find out why data is wrong).
• If queried table has ID column which you don't need in view – keep it anyway. ID is the easiest way to find row in the table, so keep this possibility.
• Not exactly the DB thing, but some field in application may take value from view. You may check does size and type of returning value match the size and type of field. For example, what happens if you return value with maximum possible length? Or may be your field takes values from two columns (sums two texts, for example)? What happens if both texts are with maximum size?
• SQL query should be easy to read, so if it's complex query be sure that every table has unique and clear alias, columns are in right order (for example, START_DATE is right before END_DATE or ID is at the beginning), column names are unique (if query returns two ID columns from different tables be sure the name of each column is changed to something like USER_ID and ACCOUNT_ID) and the whole query is formatted. Even if you understand the complex query now, you may forget about it in a year, so make it as easy as possible to save the time on understanding.
• NB! Dangerous step, if you don't understand how view is working don't do that! Run query of the view in live DB. It may identify performance problems (live DB usually contains more data than test DB) and in DB testing it is always good to work with real data.

If you also support customers, then probably you are the person who uses DB more ofter than others. Which means you are the person who should care about DB structure and keep it clear and tidy.

q - Text as Database

Nice software, that allows treat text as database - q Text as data.

Shortly, it allows to perform SQL-like queries in tabular text. For example, we have some file.txt with following data:
1 646524 some_text
5 878060 some_other_text
9 676876 some_text

And if q is installed we can perform SQL query like this (where cN is column number):
q "SELECT sum(c1), c3 FROM file.txt WHERE c2 > 600000 GROUP BY c3 ORDER BY 2"

The output will be:
5 some_other_text
10 some_text



Very simple (for those who knows SQL) and convenient way to work with big files with tabular data.

In our project we use it to obtain data about slow java methods in our application: once a day automated script extracts data about execution time of methods from logs and then aggregates executed text with q to group methods and show the slowest and most frequent of them (and then we use AWK to show final result in HTML table).

So, It's good to know about this tool for those, who encounter with tabular texts.

XRebel - Revolution in Exploratory Testing

In April Estonian company ZeroTurnaround (authors of JRebel) releases beta version of XRebel. I think it's the most useful tool for testing that I ever had!


XRebel is a -javaagent JVM plugin, that shows you 3 types of data (at least, for now): exceptions, session data and SQL queries. First one is easy - it shows exceptions that were thrown by your application (even if they are not visible in GUI). Session data (attribute name, value, value size, value size difference compared to the previous state of the session) - OK, maybe for someone it is useful.

But the last one - SQL queries - is magic. It shows all SQL queries with parameters that application does in a session. And it shows not only SELECT queries, but also calls for functions (call some_package.function(null, 'some parameter', 13, 'en', null)).
Possibility to see queries with real time parameters without digging a code makes testing way more faster, easier and more complete (sometimes it's not possible to control all conditions, but now you can see them). I think this tool is revolutionary for exploratory testing - now you are able to understand the flow of data with exploring application, not code.

I have 13 SQL queries in session that were executed in 586ms (you can see queries by clicking on the icon);


Total size of session is 887.7 KiB, difference compared to the precious state is +14.3 KiB;


0 exceptions;

Send feedback (it's possible to send log file);

Settings.



Some nice points that are not related to the functionality:

• very easy installation - you should just download XRebel and include it't path into JVM startup arguments
• fast feedback - you can send questions, suggestions, bugs to ZT about XRebel and they answer very fast, which is nice
• daily updates - there are available two builds: stable version and nightly build (which updates every day), so they improve tool very fast

You can't simply download it yet, but you can request a private beta invite and if you are lucky - can use beta version for free for one month.

The NULL Logic In The SQL

On today's course "Anatomy Of Database" by Targo Tennisberg among other things I have learned very simple thing about NULL in SQL:

I've always thought that NULL means nothing, but actually it means unknown.
And that is why we can not compare some value with NULL - we can not use expressions like value = NULL or value > NULL, because it basically means that we are askind "does value equals to unknown?" It is impossible to answer that question. So we need to use expression value IS NULL, which means "does value is unknown?" Yes, it can be unknown.

The thing that impresses me - that I've always knew, that I should use IS NULL, not = NULL, but I've never thought why (when I started to use PL/SQL Developer, for the first time I wrote = NULL in the querry).

Bug In The Oracle Pl/SQL Developer 7.1.5

There is a bug in Oracle PL/SQL Developer 7.1.5: timestamp field is sorted only by the day, not taking in account months, years and time.



In the picture you can see, that the sorting is ASC and the first value is 01.11.2010 09:37:54, the last value is 26.04.2010 13:02:21, which is definitely older, so should be before the first one.

But the sorting is wrong only if use GUI - this sorting icon to the right of each field. If give sorting rules in the SQL query ORDER BY SYS_START_TIME ASC, then it is correct.