OraFAQ Forum: SQL & PL/SQL » Procedure returning No Data Found (2 Merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Procedure returning No Data Found (2 Merged) (Oracle 11g/RedHat)

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

Procedure returning No Data Found (2 Merged) [message #535336]

Tue, 13 December 2011 09:41

yngiwulf
Messages: 4
Registered: December 2011
Location: Florida

Junior Member

Hello,

I have a stored procedure that is returning no data. I have read this is most common in stored procedures that use a SELECT INTO that tries to put a null in the variable. I don't think this is the case, though, since I'm using a COALESCE just to be sure I get something. If you could take a look and tell me what you think, it would be much appreciated. First, the stored procedure (from SQL Developer) then th execute and error. Thank you!

   PROCEDURE prc_add_address (addr_id OUT integer, addr_type_id IN integer, addr_line_1 IN varchar2,
                              addr_line_2 IN varchar2 := null, addr_line_3 IN varchar2 := null,
                              prov IN varchar2 := null, zip_id IN number,
                              country_cd IN varchar2 := 'USA', addr_start_date IN date,
                              addr_end_date IN date := null, changed_by IN varchar2, 
                              changed_date IN date, channel_txt IN varchar2 := null)
   IS
      countr integer;
   BEGIN
      SELECT count(*) INTO countr FROM tbl_address 
      WHERE address_line_1 = addr_line_1 
         and COALESCE(address_line_2,'') = COALESCE(addr_line_2,'')
         and COALESCE(address_line_3,'') = COALESCE(addr_line_3,'')
         and COALESCE(province,'') = COALESCE(prov,'')
         and zip_code_id = zip_id and country_code = country_cd;
  
      IF countr > 0 THEN
         INSERT INTO tbl_address
            (address_type_id, address_line_1, address_line_2, address_line_3, province,
             zip_code_id, country_code, address_start_date, address_end_date, last_changed_by,
             last_changed_date, channel_text)
         VALUES (addr_type_id, upper(addr_line_1), upper(addr_line_2), upper(addr_line_3), 
             upper(prov), zip_id, upper(country_cd), addr_start_date, addr_end_date, 
             upper(changed_by), changed_date, upper(channel_txt));
      END IF;   

      SELECT coalesce(address_id,0) into addr_id from tbl_address
      WHERE address_line_1 = addr_line_1 
        and coalesce(address_line_2,'') = coalesce(addr_line_2,'')
        and coalesce(address_line_3,'') = coalesce(addr_line_3,'')
        and coalesce(province,'') = coalesce(prov,'')
        and zip_code_id = zip_id and country_code = country_cd;

   END;

The execution:

declare addrid integer := 0;
BEGIN
   pkg_vic_person.PRC_ADD_ADDRESS (addrid, addr_type_id => 1, addr_line_1 => '351437 Tall Blvd', zip_id => 14906, addr_start_date => '01-FEB-2011', changed_by => 'RS', changed_date => sysdate);
   dbms_output.put_line (addrid);
END;

The sad conclusion:

Error starting at line 1 in command:
declare addrid integer := 0;
BEGIN
pkg_vic_person.PRC_ADD_ADDRESS (addrid, addr_type_id => 1, addr_line_1 => '351437 Tall Blvd', zip_id => 14906, addr_start_date => '01-FEB-2011', changed_by => 'RS', changed_date => sysdate);
dbms_output.put_line (addrid);
END;
Error report:
ORA-01403: no data found
ORA-06512: at "VIC.PKG_VIC_PERSON", line 108
ORA-06512: at line 3
01403. 00000 - "no data found"
*Cause:
*Action:

[Updated on: Tue, 13 December 2011 09:49]

Report message to a moderator

Re: Procedure returning No Data Found (2 Merged) [message #535348 is a reply to message #535336]

Tue, 13 December 2011 10:06

oruorainfo
Messages: 46
Registered: May 2010

Member

You may wish to do a quick validation of the data in your table with the following:

SELECT count(*) FROM tbl_address 
WHERE address_line_1 = '351437 Tall Blvd';

Is the count > 0?

Report message to a moderator

Re: Procedure returning No Data Found (2 Merged) [message #535350 is a reply to message #535348]

Tue, 13 December 2011 10:11

yngiwulf
Messages: 4
Registered: December 2011
Location: Florida

Junior Member

Shoulda mentioned that, yes, I did that already.

If I comment out the following the procedure, it works. Shouldnt this COALESCE(address_id,0) cover me?

      SELECT coalesce(address_id,0) into addr_id from tbl_address
      WHERE address_line_1 = addr_line_1 
        and coalesce(address_line_2,'') = coalesce(addr_line_2,'')
        and coalesce(address_line_3,'') = coalesce(addr_line_3,'')
        and coalesce(province,'') = coalesce(prov,'')
        and zip_code_id = zip_id and country_code = country_cd;

Report message to a moderator

Re: Procedure returning No Data Found (2 Merged) [message #535353 is a reply to message #535350]

Tue, 13 December 2011 10:17

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If no record satisfies the WHERE condition, query returns nothing - no rows. So, you should either handle that exception, or use a little trick - an aggregate function. Here's an example:

SQL> select 'x'
  2  from dept
  3  where deptno = 1000;

no rows selected

SQL> declare
  2    l_cnt number;
  3  begin
  4    select 'x' into l_cnt
  5      from dept
  6      where deptno = 1000;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


SQL> declare
  2    l_cnt number;
  3  begin
  4    select max('x') into l_cnt
  5      from dept
  6      where deptno = 1000;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>

Report message to a moderator

Re: Procedure returning No Data Found (2 Merged) [message #535355 is a reply to message #535353]

Tue, 13 December 2011 10:24

yngiwulf
Messages: 4
Registered: December 2011
Location: Florida

Junior Member

Now that sounds like an interesting solution! I'm trying to figure out how to apply it in this situation.

This still bothers me, though: If the WHERE clause comes up with nothing, I should get a null. The COALESCE(address_id,0) should return a 0, shouldn't it, since address_id is null?

Report message to a moderator

Re: Procedure returning No Data Found (2 Merged) [message #535364 is a reply to message #535355]

Tue, 13 December 2011 11:14

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Let's create a table:

SQL> create table test (id number, address_id number);

Table created.

Here's the first example - yours: no records are retrieved from a table (i.e. no records satisfy conditions in the WHERE clause):

SQL> select coalesce(address_id, 0) from test;

no rows selected

See? No rows selected. Nothing. Why do you think that you should get a 0 (zero) out of nothing?

A step further: let's insert a record into a table. ADDRESS_ID will be empty:

SQL> insert into test (id, address_id) values (1, null);

1 row created.

Let's now see what our function returns:

SQL> select coalesce(address_id, 0) from test;

COALESCE(ADDRESS_ID,0)
----------------------
                     0

Ah, nice! A table contains some records (1 exactly, but it doesn't matter - it is not an empty record set). ADDRESS_ID is NULL, but COALESCE takes care about it and returns something else instead of a NULL - a 0.

Report message to a moderator

Re: Procedure returning No Data Found (2 Merged) [message #535365 is a reply to message #535364]

Tue, 13 December 2011 11:34

yngiwulf
Messages: 4
Registered: December 2011
Location: Florida

Junior Member

Okay, I gotcha now - thanks for the demo!

I changed that section to the following, and its working. Your first question got me thinking when I read it again, basically, if I run that query, do I get what I think I should?

      SELECT address_id into addr_id from tbl_address
      WHERE address_line_1 = addr_line_1 
        and (address_line_2 = addr_line_2 or address_line_2 is null)
        and (address_line_3 = addr_line_3 or address_line_3 is null)
        and (province = prov or province is null)        
        and zip_code_id = zip_id and country_code = country_cd;

Thanks for helping me out with this, Lightfoot!

Report message to a moderator

Previous Topic:	Usage of the "IN" Clause
Next Topic:	how can we update the number of rows in three columns in a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 25 17:02:15 CST 2026