PL/SQL Function with Exception Handling

Question

I am a beginner in PL/SQL. I need to write a function with following details:

Create a function named 'find_transaction_type' that will accept the transaction_type_id as input. Based on this input, the function must return the transaction type name of type varchar.

Function name : find_transaction_type,

Input Parameter : transaction_type_id in int

Design rules:

1)If transaction type id(i.e, transaction_type_id) passed as input, matches with the id in the transaction table,then it returns the type of the given transaction_type_id.

2)If the transaction type id passed as input, does not match with the id in the transaction table,then it throws ' no_data_found' exception and displays it with the text as ' No such Type'

Note: Use variable to print the exceptions instead of 'dbms_output.put_line' ie: umpire_name := 'No such umpire';

My Solution is:

    create or replace function find_transaction_type(transaction_type_id in integer) return varchar is
           transaction_type_name varchar(255);
           type_id               integer;
           error_msg             varchar(255);
        begin
           error_msg := 'No such Type';
           select id
             into type_id
             from transaction_type;
           if type_id = transaction_type_id
           then
              select type
                into transaction_type_name
                from transaction_type
               where id = transaction_type_id;
              return(transaction_type_name);
           else
              raise no_data_found;
           end if;
        exception
           when no_data_found then
              raise_application_error(-10403, error_msg);
        end;
/

What's wrong with my code?

Apart from the lack of any indentation to make it readable? :-) Well, your first select has no where clause so will return all rows from transaction_type, not just the one you want. — Tony Andrews
– Tony Andrews, Commented Aug 31, 2017 at 12:32

Jeff Holt · Accepted Answer · 2017-08-31 13:58:35Z

You do not need the first select nor do you need the if statement. Just let the query raise the no_data_found exception. Refer to types by their table's respective types.

create or replace function find_transaction_type (
    transaction_type_id in transaction_type.transaction_type_id%type
    )
    return transaction_type.type%type is
       transaction_type_name transaction_type.type%type;
    begin
       select type -- not a good column name
         into transaction_type_name -- this should be the column name also
         from transaction_type
        where id = transaction_type_id;
        return transaction_type_name;
    exception
       when no_data_found then
          if transaction_type_id is null then
             raise_application_error(-10403, "type argument is null");
          else
             raise_application_error(-10403, "type '" || transaction_type_id || "' not found");
          end if;
    end;

Rene · Accepted Answer · 2017-08-31 12:37:31Z

0

Use varchar2 instead of varchar.
Add a where clause to the first select statement.
If you do 2 no need for the if then else.

answered Aug 31, 2017 at 12:37

Rene

10.6k5 gold badges37 silver badges48 bronze badges

Collectives™ on Stack Overflow

PL/SQL Function with Exception Handling

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related