SELECT IN ORACLE - Online Tech Support

How to Write Oracle SELECT Statement

online tech support Comments Off

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);

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

How To Write Oracle Select Into Statement

online tech support Comments Off

This tutorial is based on examples to be easier to follow. Function Oracle Select Into helps you to store or keep the values returned by your Oracle Select. The purpose is to re-use the values returned by Select Oracle and one of the ways is to use returned values in the next SQL query. The options to store values are: to store only one value, or a row of values or values from many rows. Please keep in mind that your Oracle database has it limits and holding too many rows of values in your variable can break your Oracle Select query. The syntax of Oracle Select Into is:

SELECT <values>
  INTO <variables>
  FROM <tables>;

Using bind variable as you can see in the first example you can write a Select in Oracle without using Oracle PL/SQL blocks as the following online tech support example shows:

VARIABLE a NUMBER;

SELECT ROWNUM
  INTO :a
  FROM dual;

Important To Know:

When your Select Into Oracle returned error ORA-01006: bind variable does not exist then either one of the followings:

Your SQL development tool does not support the SQL Prompt mode
You left out or did forget to declare your bind variable before the Oracle Select Into statement. As in the example above: VARIABLE a NUMBER;

The next example from Online Tech Support computer technician is with using an Oracle PL/SQL anonymous block. In this example the PL/SQL oracle block doesn’t output any value, because we like to keep the example as simple as possible, but you can add DBMS_OUTPUT.PUT_LINE(‘i_number=>’||i_number); if you are interested to see the results. The last statement we left out because the code will be more easy to read to keep it as simple as possible.

DECLARE
  i_number PLS_INTEGER;
BEGIN
  SELECT ROWNUM
    INTO i_number
    FROM dual;
END;

To store more than one value you just have to declare more variables in the oracle PL/SQL declaration and as well in the INTO keyword. The third Select Into Oracle example is about declaring 3 variable and online tech support computer technician will just hard code the numbers in the Select query.

DECLARE
  i_number1 PLS_INTEGER;
  i_number2 PLS_INTEGER;
  i_number3 PLS_INTEGER;
BEGIN
  SELECT 1, 2, 3
    INTO i_number1, i_number2, i_number3
    FROM dual;
END;

To store into your variable more than one row you need to use a special PL/SQL Oracle type named Oracle collection.In the fourth Oracle Select Into example we will declare a collection type named t_numbers and we will store into that variable the rows generated by table Dual Oracle with function CONNECT BY. Please note that using type collection in Oracle database you need to use additional keywords instead of only INTO you would need words BULK COLLECT INTO.

DECLARE
  TYPE tab_numbers IS TABLE OF PLS_INTEGER;
  t_numbers tab_numbers;
BEGIN
  SELECT rownum
    BULK COLLECT INTO t_numbers
    FROM dual
 CONNECT BY rownum < 11;
END;

Now the oracle select into stored 10 lines of rownum values into variable t_numbers.

See Also:
Home