Re: What is "dual"?

On Sat, 13 Jan 2001 23:14:56 GMT, chriss_at_enteract.com wrote:

>I am learning PL/SQL and have looked through two books, google
>searched, and 10 pages of DEJA and haven't found a good explanation...
>
>My book gives the example:
>SELECT 'COLUMBUS'
> INTO v_city
> FROM dual;
>
>There is no "dual" table though, so I'm curious just what this is. I
>would think it would be "FROM zipcode" which is the table that contains
>the city information.
>
>Insight appreciated. Thanks!

Let's see what I can come up with.

DUAL is a table. It has a column called DUMMY with a value of 'X'.

Basically, all SQL statements, (not PL/SQL), are based on tables.

When you execute a SQL query, all valid rows are cycled through one by one, and the query is run with each one being valid for that query. SO, if a table has 10,000 rows, the qeury is executed 10,000 times.

You can SELECT one of two things. A literal or a value. Values come from the table. When SELECTing a literal, you select just that, and then execution moves to the next row in the query. When you select a value, it must be in table.column format. (If the table is not specified, it is implied by the FROM clause, if not, an error occurs.) So, you specify the (table and) column, and the query specifies the row, and you have an x and y pinpoint to the value you want in the table. The column you want is always the same, the row changes per exection on the query.

If all you want is a literal, and only once, you need to query a table that will return exactly one row. To make this easy on you, Oracle includes a table called DUAL, with exactly one row.

If you'd like, you can make your own table to do the same thing.

CREATE TABLE Cow (Moo AS CHAR(1));
INSERT INTO Cow VALUES('A');

You can then insert and delete rows from the table and see the results on your SQL queries.

PL/SQL is a language, so you could alo do the following.

v_city := 'COLUMBUS;

Brian Received on Mon Jan 15 2001 - 07:51:27 CST