CODEWITHSUNDEEP
oracle-databaseplsqlexception
user3400060
user3400060

Reputation: 309

Display error message using DBMS_OUTPUT.put_line

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?

Upvotes: 1

Views: 17794

Answers (1)

Michael Broughton
Michael Broughton

Reputation: 4055

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;
/

Upvotes: 2

Related Questions