CODEWITHSUNDEEP
oracle-databasefunctionplsqlbooleanreturn
mmarkgraf
mmarkgraf

Reputation: 41

Oracle PL-SQL function not returning on true

I am trying to write a function that returns a 1 if it finds the account description already in the table and a 0 if it does not.

I can get it to return the 0 fine, but I cant seem to get it to return 1 

    Create or replace function test_glaccounts_description
    ( 
    var_account_desc varchar2
    )
    return number
    as var_status number
    begin
    select 1 into var_status
    from general_ledger_accounts
    where account_description = var_account_desc;
    exception
    when no_data_found then
    var_status := 0;
    return var_status;
    end;
    /

This is what it is supposed to do

Code a function named test_glaccounts_description that accepts one parameter that tests whether an account description is already in the General_Ledger_Accounts table. This function should return a value of 1 if the account description is in the table or zero if it isn't. (Note: If a SELECT statement doesn't return any data, it throws a NO_DATA_FOUND exception that your function can handle.)

Upvotes: 3

Views: 1110

Answers (1)

Mat
Mat

Reputation: 206929

You need to return from all paths in your code.

Something like this should work:

begin
  select 1 into var_status
    from general_ledger_accounts
    where account_description = var_account_desc;
  return var_status;
exception
  when no_data_found then
    var_status := 0;
    return var_status;
end;
/

Upvotes: 4

Related Questions