Online Tech Support

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 read more about the Oracle PL/SQL use this link.

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