OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » SELECT INTO - value of variable after "too many rows" exception

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » SELECT INTO - value of variable after "too many rows" exception (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SELECT INTO - value of variable after "too many rows" exception [message #348352]

Tue, 16 September 2008 08:51

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

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	oracle reports in share point
Next Topic:	Ora Apex & external files

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 19:39:05 CST 2024