Home » Developer & Programmer » Application Express & MOD_PLSQL » SELECT INTO - value of variable after "too many rows" exception (Oracle 10g)
SELECT INTO - value of variable after "too many rows" exception [message #348352] Tue, 16 September 2008 08:51 Go to next message
jsulc
Messages: 19
Registered: October 2005
Junior Member
I think I'am making some elementary mistake, can you help me pls?
In PL/SQL code, I use "SELECT INTO [my variable] column_XY from table_1"... etc
What will be the value of my variable in case of Select statement returning more than 1 row?

I have exception defined in the code.
All literature I have found so far says variable should remain being NULL.
For example:
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/13_elems045.htm#LNPLS01345


I am surprised that in my example, see code below, it turns out otherwise.
Exception is raised, but variable is filled with value of one of returned rows!!! (Chosen randomly???)

Thanks for any idea. Probably some small&stupid mistake of mine?

My example uses "scott" schema.
There are 4 rows in "EMP" table, which have JOB = 'SALESMAN'.

declare
my_variable number;
begin
    select empno 
      into my_variable
      from emp
     where job = 'SALESMAN';     
exception when others then
    dbms_output.put_line('my_variable = '|| my_variable);        
end;
/


I think it should return this:
my_variable =

But in my case it returns this:
my_variable = 7499

Thanks,
Jan


Re: SELECT INTO - value of variable after "too many rows" exception [message #348422 is a reply to message #348352] Tue, 16 September 2008 13:01 Go to previous message
Littlefoot
Messages: 19893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First of all, do NOT use WHEN OTHERS unless there's a really good reason to do that. In this case, it is the TOO MANY ROWS we are trying to handle.

Here's contents of my EMP table:
SQL> select empno from emp where job = 'SALESMAN';

     EMPNO
----------
      7499
      7654
      7844

Here's the first attempt:
SQL> declare
  2    my_variable number;
  3  begin
  4    select empno
  5      into my_variable
  6      from emp
  7      where job = 'SALESMAN';
  8
  9  exception
 10    when too_many_rows then
 11      dbms_output.put_line('Too many rows; my_variable = ' || my_variable);
 12  end;
 13  /
Too many rows; my_variable = 7499

PL/SQL procedure successfully completed.
Just the same as you've said; randomly chosen EMPNO.

Now, the second attempt, with the help of the ORDER BY clause:
SQL> declare
  2    my_variable number;
  3  begin
  4    select empno
  5      into my_variable
  6      from emp
  7      where job = 'SALESMAN'
  8      order by empno desc;
  9
 10  exception
 11    when too_many_rows then
 12      dbms_output.put_line('Too many rows; my_variable = ' || my_variable);
 13  end;
 14  /
Too many rows; my_variable = 7844

PL/SQL procedure successfully completed.

SQL>
No more randomly chosen EMPNO, but exactly the one we have wanted: the "last" one (ORDER BY empno DESC, right?).

In other words: variable gets value of the FIRST record that matches the WHERE condition.
Previous Topic: oracle reports in share point
Next Topic: Ora Apex & external files
Goto Forum:
  


Current Time: Mon Dec 22 10:35:26 CST 2014

Total time taken to generate the page: 0.09475 seconds