CODEWITHSUNDEEP
oracle-databaseplsql
p.ro
p.ro

Reputation: 53

error on calling function by procedure

I coded for the function, and calling procedure below. Both of coding is possible to execute, but when I exec ProdDetails(10010), it show error. Can anybody know what are problems?

       create or replace function ProdCheck(
    f_product_id in number)
    return varchar IS
    f_product_name varchar(30);
    f_product_price number;
    begin
     select product_name, price into f_product_name, f_product_price 
     from product
     where product_id = f_product_id;
    return f_product_name;
    end;

create or replace procedure ProdDetails(
sp_product_id in number
)IS
f_product_id number;
sp_name varchar(30);
sp_price number;

begin
f_product_id := ProdCheck(sp_product_id);
if f_product_id > 0 then
dbms_output.put_line('Product Name : '||sp_name);
dbms_output.put_line('Product Price : '||sp_price);
else
dbms_output.put_line('Product not in the database!');
end if;
end;

enter image description here

Upvotes: 0

Views: 35

Answers (1)

user5683823
user5683823

Reputation:

Your function prodcheck takes a product_id and returns the product_name. Then in the procedure you call the function, you feed the function a product_id (everything fine so far), but then assign the return value from the function, which is the product name, to the variable f_product_id, which you declared as number. Obviously that won't work. And, indeed, the function and the procedure are both syntactically correct; only when you put them together will this fail, and only at runtime since Oracle doesn't strictly enforce data types (if the product name was '1000' instead, perhaps the function would execute OK - and produce garbage results since it would interpret this product name as the product id instead).

You query your table in the function to check if the product id is valid, and you return the name. In the procedure, you could assign the return value from the function to sp_name. The function does not return the price though (it can't - a function cannot return more than one value), so you can't display the price in the procedure. You could query the table again in the procedure, but that seems pretty senseless; it would be better to combine everything into a single procedure. (You probably don't need the check at all - if the product id doesn't exist in the table, you will get a "no rows" exception and you can use that instead of prodcheck.)

Upvotes: 1

Related Questions