SQL & PL/SQL

When I was trying to write code to test out today's PL/SQL challenge quiz, the behavior appears to depend on the table being queried. I can't figure out what attribute of the table drives the behavior (or if there is a different explanation for the behavior).

The quiz is testing what happens when a procedure does a SELECT INTO an OUT parameter and a TOO_MANY_ROWS exception is thrown. The intent is to show that even though the behavior is technically undefined, what actually happens is that the OUT parameter is populated with the first row that is selected (obviously, you would never write code that depends on this behavior-- this is solely an academic exercise). The demonstration code works as expected

CREATE TABLE plch_emp ( emp_name VARCHAR2(100) );

INSERT INTO plch_emp VALUES ('Jones');

INSERT INTO plch_emp VALUES ('Smith');

COMMIT;

CREATE OR REPLACE PROCEDURE plch_get
  (out_name OUT plch_emp.emp_name%TYPE) IS
BEGIN
  SELECT emp_name
  INTO out_name
  FROM plch_emp
  ORDER BY emp_name;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('A:' || out_name);
END;
/
What will be displayed after executing the following block:

DECLARE
  l_name plch_emp.emp_name%TYPE;
BEGIN
  plch_get(l_name);
  dbms_output.put_line('B:' || l_name);
END;
/

and outputs

A:Jones
B:Jones

When I replicate the logic while hitting the EMP table, the PLCH_EMP table, and the newly created EMP2 table, however, I get different behavior for the EMP and EMP2 tables. The procedure that queries PLCH_EMP works as expected but the OUT parameter is NULL when either EMP or EMP2 are created. Any idea what causes the behavior to differ?

select *
  from v$version;

create table emp2
as
select *
  from emp;

create or replace procedure p1( p_out out varchar2 )
is
begin
  select emp_name
    into p_out
    from plch_emp
   order by emp_name;
exception
  when others then 
    dbms_output.put_line( 'P1 A:' || p_out );
end;
/

create or replace procedure p2( p_out out varchar2 )
is
begin
  select ename
    into p_out
    from emp
   order by ename;
exception
  when others then 
    dbms_output.put_line( 'P2 A:' || p_out );
end;
/

create or replace procedure p3( p_out out varchar2 )
is
begin
  select ename
    into p_out
    from emp2
   order by ename;
exception
  when others then 
    dbms_output.put_line( 'P3 A:' || p_out );
end;
/

declare
  l_ename varchar2(100);
begin
  p1( l_ename );
  dbms_output.put_line( 'P1 B:' || l_ename );

  p2( l_ename );
  dbms_output.put_line( 'P2 B:' || l_ename );

  p3( l_ename );
  dbms_output.put_line( 'P3 B:' || l_ename );

end;
/

SQL> select *
  2    from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>
SQL> create table emp2
  2  as
  3  select *
  4    from emp;

Table created.

SQL>
SQL> create or replace procedure p1( p_out out varchar2 )
  2  is
  3  begin
  4    select emp_name
  5      into p_out
  6      from plch_emp
  7     order by emp_name;
  8  exception
  9    when others then
 10      dbms_output.put_line( 'P1 A:' || p_out );
 11  end;
 12  /

Procedure created.

SQL>
SQL> create or replace procedure p2( p_out out varchar2 )
  2  is
  3  begin
  4    select ename
  5      into p_out
  6      from emp
  7     order by ename;
  8  exception
  9    when others then
 10      dbms_output.put_line( 'P2 A:' || p_out );
 11  end;
 12  /

Procedure created.

SQL>
SQL> create or replace procedure p3( p_out out varchar2 )
  2  is
  3  begin
  4    select ename
  5      into p_out
  6      from emp2
  7     order by ename;
  8  exception
  9    when others then
 10      dbms_output.put_line( 'P3 A:' || p_out );
 11  end;
 12  /

Procedure created.

SQL>
SQL> declare
  2    l_ename varchar2(100);
  3  begin
  4    p1( l_ename );
  5    dbms_output.put_line( 'P1 B:' || l_ename );
  6
  7    p2( l_ename );
  8    dbms_output.put_line( 'P2 B:' || l_ename );
  9
 10    p3( l_ename );
 11    dbms_output.put_line( 'P3 B:' || l_ename );
 12
 13  end;
 14  /
P1 A:Jones
P1 B:Jones
P2 A:
P2 B:
P3 A:
P3 B:

PL/SQL procedure successfully completed.

Justin