Retrieve data by user input with a message (PL/SQL)

Question

I want to how to retrieve data from the table I have added here by inserting C_Id as the user input with defined variables and exceptions. If any customer is not available it has to display a message showing "No customer found". Please help me to understand this one.

Thank you!

score 2 · Answer 1 · answered Nov 23 '21 at 11:22

If it is a stored procedure (should be; it accepts a parameter) and you just want to display what you found, then this might be one option:

create or replace procedure p_test (par_c_id in customer_details.c_id%type)
is
  l_row customer_details%rowtype;
begin
  select *
    into l_row
    from customer_details
    where c_id = par_c_id;
    
  dbms_output.put_line(l_row.customer_name ||', '||
                          l_row.address       ||', '||
                          to_char(l_row.date_of_joined, 'dd.mm.yyyy')
                         );
exception
  when no_data_found then
    dbms_output.put_line('No customer found');
end;
/

Then run it as

set serveroutput on
begin
  p_test(121);
end;
/

What does it do?

accepts a parameter
declares local variable which is equal to table's rowtype
select everything into the variable
using dbms_output.put_line, display elements you want
if nothing has been found, exception (no_data_found) is raised and handled in a way that you display appropriate message

Note that such an option works if tool you use (e.g. SQL*Plus, SQL Developer, TOAD, ...) allow displaying result of dbms_output.put_line. Otherwise, if you use e.g. Oracle Apex, procedure will still work, but you won't see anything.

It is a parameter you pass to the procedure. How will you pass it? Depends on a tool. In SQL*Plus, you'd use a substitution variable, e.g. `p_test(&par_id);`. Or, in TOAD you could use a bind variable, e.g. `p_test(:par_id);` — Littlefoot, Nov 23 '21 at 11:47

Retrieve data by user input with a message (PL/SQL)

1 Answers1