Home » SQL & PL/SQL » SQL & PL/SQL » Value of variable after SELECT INTO returns > 1 row (Oracle 10g)
Value of variable after SELECT INTO returns > 1 row [message #348354] Tue, 16 September 2008 08:59 Go to next message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Hi,
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
or:
http://www.dba-oracle.com/t_pl_sql_plsql_select_into_clause.htm]

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: Value of variable after SELECT INTO returns > 1 row [message #348357 is a reply to message #348354] Tue, 16 September 2008 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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 don't see in the link you posted where it is said that the variable is set to NULL.
It is said:
Quote:
PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined.

Undefined means that can contains anything undeterministic.

Regards
Michel
Re: Value of variable after SELECT INTO returns > 1 row [message #348358 is a reply to message #348354] Tue, 16 September 2008 09:20 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>I think it should return this:
>my_variable =
WHY?

How would Oracle "know" to raise the exception BEFORE returning the 1st row & assigning the variable?

The exception is raised by the second row; so the variable is/was loaded by the first row.

Re: Value of variable after SELECT INTO returns > 1 row [message #348360 is a reply to message #348354] Tue, 16 September 2008 09:21 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have tested the same on my Version 9.0.2.0.8, and the variable is empty after the exception is catched.

Of course, having ANY "when others" in ANY procedure IS an elementary mistake. Wink

SQL>
SQL> set serverout on
SQL> DECLARE
  2  v_i NUMBER(4);
  3  BEGIN
  4
  5     SELECT rownum INTO v_i FROM all_objects ;
  6
  7    exception when others then
  8      dbms_output.put_line('my_variable = '|| v_i);
  9
 10  END;
 11  /
my_variable =

PL/SQL procedure successfully completed.

SQL>
SQL>


Re: Value of variable after SELECT INTO returns > 1 row [message #348370 is a reply to message #348360] Tue, 16 September 2008 09:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Interesting, on 10.2.0.3, I get null when we select rownum
  1  DECLARE
  2    v_i NUMBER(4);
  3  BEGIN
  4    SELECT rownum INTO v_i FROM all_objects ;
  5  exception when others then
  6    dbms_output.put_line('my_variable = '|| v_i);
  7* END;
SQL> /
my_variable =

PL/SQL procedure successfully completed.


But if we select a value from the table, then we get something:
  1  DECLARE
  2    v_1 varchar2(30);
  3    v_2 number;
  4  BEGIN
  5    SELECT object_name,rownum INTO v_1,v_2 FROM all_objects ;
  6  exception when others then
  7    dbms_output.put_line('my_variable = '|| v_1||'-'||v_2);
  8* END;
SQL> /
my_variable = DUAL-1

PL/SQL procedure successfully completed.


I guess when it says Undefined, it really means it.
Re: Value of variable after SELECT INTO returns > 1 row [message #348378 is a reply to message #348370] Tue, 16 September 2008 10:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I think we can file this as "phenomenon of the week"

Even in ONE select, some fields are filled and others null.

I my test I found varchar2 fields are more likely to be filled than number fields, no matter in which order they are in the select.

That's undefined behaviour indeed. Wink

SQL>
SQL> DECLARE
  2  v_i NUMBER(4);
  3  v_c VARCHAR2(100);
  4  BEGIN
  5
  6     SELECT object_id, object_name INTO v_i, v_c FROM all_objects ;
  7
  8    exception when others then
  9      dbms_output.put_line('v_i = '|| v_i);
 10      dbms_output.put_line('v_c = '|| v_c);
 11
 12  END;
 13  /
v_i =
v_c = /1005bd30_LnkdConstant

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2  v_i NUMBER(4);
  3  v_c VARCHAR2(100);
  4  BEGIN
  5
  6     SELECT object_name, object_id INTO v_c, v_i  FROM all_objects ;
  7
  8    exception when others then
  9      dbms_output.put_line('v_i = '|| v_i);
 10      dbms_output.put_line('v_c = '|| v_c);
 11
 12  END;
 13  /
v_i =
v_c = /1005bd30_LnkdConstant

PL/SQL procedure successfully completed.

Re: Value of variable after SELECT INTO returns > 1 row [message #348421 is a reply to message #348378] Tue, 16 September 2008 12:54 Go to previous message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Thank you very much for your time and answers.


To Michel Cadot
"I don't see in the link you posted where it is said that the variable is set to NULL.
It is said: Quote: 'PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined.' - Undefined means that can contains anything undeterministic."
--> yes, it was only my incorrect assumption that if value of variable is named "undefined", it is NULL. Thanks.


To ThomasG
"I have tested the same on my Version 9.0.2.0.8, and the variable is empty after the exception is catched.
Of course, having ANY "when others" in ANY procedure IS an elementary mistake."
--> I definitely do not agree. "WHEN OTHERS" is useful, e.g. if error number & description is logged into error table in the exception clause defined as "WHEN OTHERS"


To anacedent:
"WHY? How would Oracle "know" to raise the exception BEFORE returning the 1st row & assigning the variable?
The exception is raised by the second row; so the variable is/was loaded by the first row."
--> The tests prove you are right.
But I must say, for my "imagination" it was hard to presume that:
1. oracle expects one value to be returned
2. a bunch of values is returned
3. oracle feels it totally all right, it takes one of them (randomly?!), and as late as at the 2nd one, a problem is revealed and exception raised.
But the test prove you're right, thanks for answering.

jan





Previous Topic: How i can retrieve the last booking time
Next Topic: ALTER all the tables from a particular schema at one shot
Goto Forum:
  


Current Time: Fri Dec 09 09:52:45 CST 2016

Total time taken to generate the page: 0.27807 seconds