How To Use Oracle SELECT INTO Statement

How To Use Oracle SELECT INTO Statement

December 11, 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 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;

dba oracle dba database management system oracle performance tuning oracle select into oracle select into in oracle online colleges online schools online ORA-01006: bind variable does not exist 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:

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;

dba oracle dba database management system oracle performance tuning oracle select into oracle select into in oracle online colleges online schools online ORA-01006: bind variable does not exist 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

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

dba oracle dba database management system oracle performance tuning oracle select into oracle select into in oracle online colleges online schools online ORA-01006: bind variable does not exist 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

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;

dba oracle dba database management system oracle performance tuning oracle select into oracle select into in oracle online colleges online schools online ORA-01006: bind variable does not exist 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

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.

See Also:
Oracle Select Oracle Home

Leave a Reply

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

*