This tutorial page is based on examples to make it easier to follow. Function Oracle Select Into allows you to keep the returned values in a variable. One of the purpose is to re-use the Oracle Select statement values and in the next SQL query or send them to output. You can store values coming from SQL select in a three different ways:
- only one value
- entire row
- many row
Please keep in mind that Oracle database has it limits and when you are 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>;
We are using bind variable as you can see in the first example. There are two ways to write Oracle Select Into – you can either use the bind variable as in the example below or you need to use Oracle PL/SQL block. In this example we are using the bind variable option.
VARIABLE a NUMBER; SELECT ROWNUM INTO :a FROM dual;
When your Oracle Select Into returned error ORA-01006: bind variable does not exist then the reason could be one of the following reasons:
- Your SQL development tool does not support the SQL Prompt mode and bind value option is disabled
- You left out or did forget to declare your bind variable before the Oracle Select Into statement. Take a look at the example above there is a line that declare the variable: VARIABLE a NUMBER;
The next example from Oracle DBA is with Oracle PL/SQL anonymous block. In this example the PL/SQL block does not output any value, because we will keep the example as simple as possible, but you can add DBMS_OUTPUT.PUT_LINE(‘i_number=>’||i_number); before the END; line to see the output.
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 add them in the INTO statement. This Select Into Oracle example is about to 3 variables that are declared by Oracle DBA and the Select statement will fill them with hard coded numbers (1, 2, 3).
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 would need to use a special PL/SQL type named Oracle collection. In this fourth example we will declare a collection type named t_numbers and we will store into that variable the rows generated by table Oracle Dual and with using function CONNECT BY. Please note that using the type collection you need to use additional keyword BULK COLLECT INTO instead of INTO. Take a close look at the example below to all those changes.
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, but your select can return more and while the amount is not more than 100 000 lines the Oracle statement should work fine.