How to Write SELECT In Oracle

How to Write SELECT In Oracle

December 12, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This online tutorial is based on examples to make it easier to follow. Statement Oracle Select is used in the way as it is regulated in ANSI standard.
The basic Select Oracle syntax is:

SELECT <columns> 
  FROM <tables>;

The following Oracle DBA example is the basic Oracle Select statement with using Oracle Dual table.

SELECT * 
  FROM DUAL;

database management system oracle performance tuning oracle select oracle dba oracle select in oracle online it degree oracle listagg oracle partition oracle cursor oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

About this Oracle SQL statement above we are using table Dual in Oracle database and since it is an one-row table it did return this one row. It is important to know that the Oracle table is present in all Oracle database versions and you should not have any trouble to execute it.

To see how looks the Select in Oracle with a different table we just created one named CLIENTS that only for this example. The select statement with using table CLIENTS is below:

SELECT * 
  FROM CLIENTS;

To write a query using the Oracle Dual table and to make it to returning more than one row, our Oracle DBA wrote the following example. This Oracle Select statement will return 10 rows. The query is using pseudo-column named Oracle ROWNUM and it is combined with the CONNECT BY operator. The Connect By operator is taken from Oracle hierarchical query group and it creates and join with the table itself. This type of join called as self-join. Using the Connect By operator the query is smaller and easier to use.

 SELECT ROWNUM 
   FROM DUAL 
CONNECT BY ROWNUM < 11;

database management system oracle performance tuning oracle select oracle dba oracle select in oracle online it degree oracle listagg oracle partition oracle cursor oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

The next example is about grouping the values by using a special Oracle Listagg function. if you are using Oracle 11g database then you can use very 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 example we are going to separate the values using coma as you can see on the following Oracle Select statement. It will show you how to use Oracle Listagg function and to set all Oracle Rownum values on the same line.

SELECT LISTAGG (ROWNUM, ',') WITHIN GROUP (ORDER BY ROWNUM) AS ROW_NUMBERS
  FROM DUAL CONNECT BY ROWNUM < 11;

database management system oracle performance tuning oracle select oracle dba oracle select in oracle online it degree oracle listagg oracle partition oracle cursor oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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 starting with Oracle database version 11g. Take a look at the following picture to see the ORA-00923: FROM keyword not found where expected error:

database management system oracle performance tuning oracle select oracle dba oracle select in oracle online it degree oracle listagg oracle partition oracle cursor oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Oracle Select can be look in a very different way, so the next example will be Oracle Select using directly Oracle Partition table. The query with Oracle partition can be written as well many ways and to bring out more clear we will show you the two most common styles. It is important to know that the partition oracle method will give much better performance than a usual table when we have a big amount of data stored in it. Read more about oracle partition and table partitioning from here.

In this Oracle partition example we are going to use 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 2012 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 first.

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. The 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;

database management system oracle performance tuning oracle select oracle dba oracle select in oracle online it degree oracle listagg oracle partition oracle cursor oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

See Also:
Oracle Select Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*