CODEWITHSUNDEEP
oracle-databaseplsql
krackoder
krackoder

Reputation: 2981

Reading IN parameters in a query in PL/SQL

I have written a PL/SQL procedure as:

CREATE OR REPLACE PROCEDURE checkProdQuantity (productid IN number, orderqty IN number)
IS
    qty number;
    qty_diff number;
BEGIN
    SELECT quantity INTO qty from Products where ProductID=productid;
    IF orderqty>qty THEN
            dbms_output.put_line('Ordered quatity is greater than available quantity');
    ELSE
            qty_diff:=qty-orderqty;
            UPDATE Products set quantity=qty_diff where ProductID=productid;
    END IF;
END;
/

But when I try to execute this procedure with valid parameters, it shows an error: exact fetch returns more than the requested number of rows.

I have checked my table, and for the parameters I am supplying it should return only one row. I think for some reason, the value of productid IN parameter is not being read in the select query. Even if I provide some random values for productid parameter, it still gives the same error. I am unable to figure out where the problem is.

Upvotes: 0

Views: 96

Answers (2)

crowne
crowne

Reputation: 8544

I don't think the compiler is distinguishing between the parameter productid and the column name ProductID. Try renaming your parameter to a_productId, or something different from the column name.

Upvotes: 3

vishad
vishad

Reputation: 1164

Its happning because, when you write

SELECT quantity INTO qty from Products where ProductID=productid;

Oracle scope resolution interprets productid as the column_name and not as your input variable.

Change the name of the input variable to something other than the column name and it should work.

Hope it helps

Vishad

Upvotes: 2

Related Questions