Display error message using DBMS_OUTPUT.put_line

Question

My requirement is to write a procedure to add values in COUNTRIES table. But, first it should check if corresponding values exist in another table, REGIONS as it is a foreign key. Only if the values exist is the insert into COUNTRIES table allowed. Otherwise, no.

I wrote a piece of code and its working fine:

create or replace procedure addi3 (c_cntry_id  in out countries.country_id%type,
                                   c_cntr_name in countries.country_name%type, 
                                   c_rgn_id    in countries.region_id%type)
is
    region_exists pls_integer;
begin
    begin
        select 1 into region_exists
        from regions r 
        where r.region_id = c_rgn_id;
    exception
      when no_data_found then
        region_exists := 0;
        DBMS_OUTPUT.PUT_LINE('Already present');    
    end;

    if region_exists = 1 then
      insert into countries(country_id, country_name,region_id)
      values (c_cntry_id, c_cntr_name,c_rgn_id);

      DBMS_OUTPUT.PUT_LINE('Inserted');
    end if;
end addi3;
/

It is working fine, except that if I execute the procedure by giving a region_id that does not exist in regions table, it correctly does no insertion in countries table. However, I want to enhance it by throwing an error using DBMS_OUTPUT.put_line if that region_id does not exist and even though I have DBMS_OUTPUT.put_line, it is not displaying appropriate error message. Can someone please guide?

do you have SERVEROUTPUT on for your session? if not, you'll have to manually get your 'Already present' or 'Inserted' messages. — thatjeffsmith
– thatjeffsmith, Commented Dec 1, 2015 at 19:01
When you say you want it to "throw an error", do you mean you want it to say somegthing other than the current 'Already present'? Or have it actually raise an exception and exit the program? — Michael Broughton
– Michael Broughton, Commented Dec 1, 2015 at 19:07
yes,say "Already present" in my case when region_id does not exist.(In actuality,it should be "Not present" but I guess that can be corrected once the error message is visible — user3400060
– user3400060, Commented Dec 1, 2015 at 19:08
If the region doesn't exist - isn't that when its supposed to be doing the insert? — Michael Broughton
– Michael Broughton, Commented Dec 1, 2015 at 19:09

Michael Broughton · Accepted Answer · 2015-12-01 19:28:36Z

An edit of your code per your request in comments:

create or replace procedure addi3 (c_cntry_id in out countries.country_id%type,
                                       c_cntr_name in countries.country_name%type, 
                                       c_rgn_id in countries.region_id%type)
is
    region_exists pls_integer;
begin
    begin
        select 1 into region_exists
        from regions r 
        where r.region_id = c_rgn_id;
    exception
        when no_data_found then
            region_exists := 0;
            DBMS_OUTPUT.PUT_LINE('Region not present '||sqlerrm);
            -- uncomment the RAISE if you want the exception to be
            -- propagated back to the calling code.
            --RAISE;

    end;
    -- if you uncommented the RAISE the IF here is redundant
    -- because you wouldn't have got here if the region didn't exist.
    if region_exists = 1 then
         insert into countries(country_id, country_name,region_id)
         values (c_cntry_id, c_cntr_name, c_rgn_id);
         DBMS_OUTPUT.PUT_LINE('Inserted');
     end if;
end addi3;
/

Collectives™ on Stack Overflow

Display error message using DBMS_OUTPUT.put_line

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related