how to reference function parameter in query?

Question

In the declared function definition I want to use value of function parameter item_account_id in the select query's WHERE clause .

CREATE OR REPLACE 
FUNCTION UPDATE_CURRENT_BALANCE( item_account_id IN item_account.item_account_id%TYPE)                               
RETURN boolean 
AS 
BEGIN        
    if item_data_table_id = 10 then
       select current_balance_curr_id 
       from BANK_ACCOUNT 
       where item_account_id = item_account_id;        
    end if;
  RETURN true;
END UPDATE_CURRENT_BALANCE;

APC · Accepted Answer · 2019-05-22 14:13:58Z

1

You have a scoping issue because the parameter name is the same as the column. The way Oracle's name resolution works, both these item_account_id = item_account_id will identify the table column. Even it we added a table alias and used it in the one side of the equality operation Oracle will still evaluate it as 1 = 1.

The solution is simple: rename the parameter. Using a prefix is popular:

CREATE OR REPLACE FUNCTION UPDATE_CURRENT_BALANCE
    ( p_item_account_id IN item_account.item_account_id%TYPE)   
RETURN boolean 
AS 
BEGIN        
    if item_data_table_id = 10 then -- the posted code has no source for this? perhaps it's another parameter?
       select current_balance_curr_id 
       from BANK_ACCOUNT 
       where item_account_id = p_item_account_id;        
    end if;
  RETURN true;
END UPDATE_CURRENT_BALANCE;

I presume item_data_table_id is another parameter which got lost in transcription. You should prefix that too: consistency is a good thing in naming conventions.

edited May 22, 2019 at 14:13

answered Mar 29, 2014 at 4:10

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richard Pascual Over a year ago

If the function execution gets inside the IF-THEN logical block and there is no match for the input parameter, the function will error out from a NO DATA FOUND error. You should also tighten up the function's response to more variations of input values and their conditions.

Collectives™ on Stack Overflow

how to reference function parameter in query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related