In this lesson, I will give you a very simple explanation of a couple of things you will be seeing or using very frequently during the course, and during your time working with Oracle databases in general. This includes the Dual table, and the way in which Oracle displays error messages.
There is a special table that is present in all Oracle databases, which may cause some confusion to people new to Oracle, and it is called “the DUAL table”.
The Dual Table
This is a special one-row, one-column table present by default in all Oracle databases.
It has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’.
You can see its contents running this simple command:
SELECT * FROM DUAL;
You will typically use the DUAL table to test functions or to return constant values that don’t come from any table in the database. For example, if you want to get the database’s current date, there’s a function called SYSDATE that does just that, but in SQL, you can’t call a function alone. You need to call it in the context of an SQL statement, and that is a typical use for the DUAL table.
So, I can use this simple SELECT statement to see the database’s current date:
SELECT sysdate FROM dual;
Don’t worry too much about the SELECT command right now. We are going to look at it with great detail in this course. By now, I just want to show you that I used the DUAL table, because I must call SYSDATE inside an SQL statement and because in this case, I don’t really want to get data from any table in the database, I used the DUAL table.
I will use this table in some lessons to return constant values or to test functions with literals or constants that don’t come from any of my tables, and you will most likely get accustomed to using it to test things as well.
How Error Messages are Displayed
Now, I’m going to make this statement fail, to show you the second thing I wanted to cover in this lesson. I will change the table name to DUALY, which doesn’t really exist:
This is the way Oracle displays error messages. There’s always an error code, which In this case is “ORA-00942”, and an error message, wich, in this case, is “table or view does not exist”.
Sometimes a cause and a recommended action will be available, and sometimes you will have the line and column numbers where the error was detected. In this case, it tells me that the error was encountered at line 1 and column 15. That is why it is important to set the line gutter in SQL Developer to display line numbers.
Let’s see another example.
As always, there is an error code and an error message.
In this case, we have the cause and action I mentioned before, but we don’t have line and column numbers. Why? That’s because the statement is syntactically correct. I mean, the table name is correct, the column name “dummy” is correct, the to_number function is correct. The whole command is syntactically correct.
The problem here is with the data because I’m trying to convert an “X”, which is what the dummy column of the DUAL table stores, into a number, which is not possible. So, the error is raised at run time because of the data, not because my command was incorrectly written. That is why I don’t have line and columns numbers here.
If you want to get information about some error you get when running an SQL command, searching on Google with the error code is usually a good way to do it.
So, in this case, you would Google “ORA-01722”, and it will usually get you a lot of pages with help and information about it.
And that’s it for this lesson.
In the next section you will start learning how to retrieve data from your database, which is most likely what you’ve been waiting for till now, so, yes, the wait is over!