This page is based on examples to be easier to follow. The Oracle SELECT statement can be written in the way as it is regulated by the ANSI standard. The general Select syntax is:
SELECT <columns> FROM <tables>;
The first example is the most basic Oracle Select statement with using Oracle DUAL table.
SELECT * FROM DUAL;
About the SQL statement above we are using table Dual that is an one-row table available in all Oracle database versions.
To see how looks an Oracle Select statement with a real table that you for example just created, we will use a table named CLIENTS and the Select statement will look follow:
SELECT * FROM CLIENTS;
To write a query using the Oracle Dual table and to making it to return more than one row, we wrote the following example. The Oracle Select statement will return 10 rows. So what did we exactly write here? The query is using pseudo-column named Oracle ROWNUM and it is combined with CONNECT BY operator. The Connect By operator is taken from Oracle hierarchical queries and it creates and join with the table itself this is called as self-join. Using Connect By operator the query is smaller and easier to use.
SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM < 11;
If you are using Oracle 11g database then we got useful build-in function Oracle LISTAGG. It helps you to group the values on the same line and separate with some character defined by you. In this computer help example we are going to separate the values using coma. The following Oracle Select statement will show you how to use Oracle Listagg function and it sets all Oracle Rownum values on the same line, so the last picture will be turned to the next picture. Compare them both to see the differences.
SELECT LISTAGG (ROWNUM, ',') WITHIN GROUP (ORDER BY ROWNUM) AS ROW_NUMBERS FROM DUAL CONNECT BY ROWNUM < 11;
Important To Know:
While you tried the query and it raised error ORA-00923: FROM keyword not found where expected then your Oracle database version does NOT support the Oracle Listagg function and you would not be able to use it. Function Listagg Oracle has been included to version 11g. See the ORA-00923: FROM keyword not found where expected exception on the following picture:
Oracle Select has quite many different options and ways to write it, so the next example will be Oracle Select with Oracle Partition table. The query with Oracle partition can be written as well many ways and will bring out following two most common styles. It is important to know that the partition oracle method will give much better performance than a usual table with the same amount of rows. Read more about oracle partition and table partitioning from here.
In this Oracle partition example we do have table “old_users” that has column “ACTIVE_YEAR” as “Number(4) Not NULL”. All users in that table have the active_year column filled with a year number i.e. “2012”. The table is partitioned by using column “ACTIVE_YEAR” and since we do have data for only years 202 and 2011 the Oracle partition names are “YEAR_2012” and “YEAR_2011”. To find all users for year 2012 use of the following queries:
SELECT * FROM old_users PARTITION (YEAR_2012);
or
SELECT * FROM old_users WHERE ACTIVE_YEAR = 2012;
P.S. When you are using an Oracle partition table do not forget to add the partition condition (“ACTIVE_YEAR = 2012”) as the first condition into your Select Oracle statement to make sure it always uses the Oracle Partition By condition.
You can also write an Oracle Select statement using an Oracle Cursor function. To see the result you need to use some visual PL/SQL development tool like Toad, SQL Navigator or Oracle SQL Developer. Another option would be to insert the Oracle cursor result into declared Oracle collection type and see the result using a PL/SQL procedure or anonymous block. Still you should be able to execute without declaring anything the following example what is Oracle Cursor in Oracle Select query.
SELECT CURSOR (SELECT ROWNUM
FROM DUAL
CONNECT BY ROWNUM < 11) AS cursor_lines
FROM DUAL;
See Also:
Home