Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> problem w/ exception handling in pl/sql

problem w/ exception handling in pl/sql

From: EdStevens <quetico_man_at_yahoo.com>
Date: 2 Jan 2007 17:37:14 -0800
Message-ID: <1167788234.528885.256340@v33g2000cwv.googlegroups.com>


Oracle 10.2.0.1

Working on a 'self-education' project in pl/sql, getting more familiar with bulk collect ... In the code below, if my SELECT returns zero rows, I'd expect to drop into the EXCEPTIONS section with a NO_DATA_FOUND from the select. Instead, I'm getting a VALUE_ERROR on the FOR loop, due to the .FIRST and .LAST being unitialized. I can code around it, but really don't understand why I didn't just get a NO_DATA_FOUND before getting that far.

Here's the code. I don't think the snipped out parts should be relevant to this issue.

DECLARE
<snip>

  TYPE usernameTYP IS TABLE OF dba_users.username%TYPE   INDEX BY BINARY_INTEGER;
  username_t usernameTYP;

<snip more similar to above>

BEGIN
  select username,

<snip>

        bulk collect into username_t,

<snip>

  from (

        select u.username,

<snip>

        from dba_users u,

<snip>

	where	<snip>
     );
--
  FOR i IN username_t.FIRST .. username_t.LAST
  LOOP
  DBMS_OUTPUT.PUT_LINE(username_t(i) ...
			 );
  END LOOP;


<snip>
EXCEPTION when NO_DATA_FOUND then DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND '); DBMS_OUTPUT.PUT_LINE(''); when COLLECTION_IS_NULL then DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL '); when SUBSCRIPT_BEYOND_COUNT then DBMS_OUTPUT.PUT_LINE('SUBSCRIPT_BEYOND_COUNT '); when SUBSCRIPT_OUTSIDE_LIMIT then DBMS_OUTPUT.PUT_LINE('SUBSCRIPT_OUTSIDE_LIMIT '); when VALUE_ERROR then DBMS_OUTPUT.PUT_LINE('VALUE_ERROR '); when OTHERS then DBMS_OUTPUT.PUT_LINE('some error occurred'); END;
Received on Tue Jan 02 2007 - 19:37:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US