CODEWITHSUNDEEP
sqloracle-databaseplsql
eharrell
eharrell

Reputation: 1

PL/SQL Function error message

I have written a simple function that compiles without errors in SQL Developer. I try to call the function and get an error that I don't understand how to fix. 

   CREATE OR REPLACE FUNCTION POUNDS_SF
  (p_idbasket in number)
  RETURN NUMBER 
  AS 
  lv_lbs bb_basketitem.option1%type;
  lv_qty bb_basketitem.quantity%type;
  lv_idbasket bb_basket.idbasket%type;
  lv_total_lbs_num number;
BEGIN
  select bi.option1, bi.quantity, b.idbasket
    into lv_lbs, lv_qty, lv_idbasket
    from bb_basketitem bi, bb_basket b
    where p_idbasket = lv_idbasket;
    lv_total_lbs_num := lv_lbs * lv_qty;
  RETURN
    lv_total_lbs_num;
END POUNDS_SF;

I call the function with this anonymous block:

declare
  lv_basket_id_num number(1) :=3;
  lv_total_num number(5,2);
begin
  lv_total_num := pounds_sf(lv_basket_id_num);
  dbms_output.put_line(lv_total_num);
end;

The error I get is:

Error report:
ORA-01403: no data found
ORA-06512: at "STUDENT22.POUNDS_SF", line 10
ORA-06512: at line 5
01403. 00000 -  "no data found"
*Cause:    
*Action:

I apologize if this question is too basic or in the wrong place, etc. Obviously, I am a student trying to learn PL/SQL. Any assistance is greatly appreciated.

EH

Upvotes: 0

Views: 204

Answers (4)

krokodilko
krokodilko

Reputation: 36127

Look carefully at your where clause:

where p_idbasket = lv_idbasket;

p_idbasket - is a function parameter
lv_idbasket - is a local variable that is not initialized anywhere in the code - it is always NULL


Therefore the where condition always evaluates to NULL, and the query returns empty set.
And this is a reason of the ORA-01403: no data found.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231801

A SELECT ... INTO statement throws an exception if the SELECT statement returns anything other than 1 row. The error you're getting indicates that the SELECT statement returned 0 rows.

Without knowing what is in your tables, it's hard to know for sure exactly what the solution to your problem is. My guess, though, is that you want to compare the parameter p_idbasket to the idbasket value from the bb_basket table. It's not obvious to me what role the local variable lv_idbasket is playing in this code, my guess is that it should be eliminated.

You're also apparently missing a join condition between the two tables. Assuming that both tables have an idbasket column

select bi.option1, bi.quantity
  into lv_lbs, lv_qty
  from bb_basketitem bi
       join bb_basket b
         on( bi.idbasket = b.idbasket )
 where b.idbasket = p_idbasket;

Upvotes: 2

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

Apart from no data found problem your query 

select bi.option1, bi.quantity, b.idbasket
into lv_lbs, lv_qty, lv_idbasket
from bb_basketitem bi, bb_basket b
where p_idbasket = lv_idbasket;

can cause a cartesian join problem of bb_basketitem and bb_basket tables because there is no any joins between these tables.

Upvotes: 2

StephaneM
StephaneM

Reputation: 4899

The error message is pretty clear: there are no data matching your criteria, i.e. no row in bb_basketitem or bb_basket with p_idbasket=3.

You should rewrite your procedure like this:

   CREATE OR REPLACE FUNCTION POUNDS_SF
  (p_idbasket in number)
  RETURN NUMBER 
  AS 
  lv_lbs bb_basketitem.option1%type;
  lv_qty bb_basketitem.quantity%type;
  lv_idbasket bb_basket.idbasket%type;
  lv_total_lbs_num number;
BEGIN
  select bi.option1, bi.quantity, b.idbasket
    into lv_lbs, lv_qty, lv_idbasket
    from bb_basketitem bi, bb_basket b
    where p_idbasket = lv_idbasket;
    lv_total_lbs_num := lv_lbs * lv_qty;
  RETURN
    lv_total_lbs_num;
   EXCEPTION WHEN NO_DATA_FOUND THEN
  RETURN 0;
END POUNDS_SF;

(I guess 0 is an acceptable value with what I see.)

Upvotes: 0

Related Questions