Apex 5 and PL/SQL error in select statements

I have this PL/SQL code which does a select from a Table and inserts into a temp Table when an exact match is found, but in a separate view should show all matches if found. So let's say I have a part number called SKU001 and type it in the field, it should do the display in one field and insert into another table. When I only type SKU it should not insert into the other table, but only display all matches like SKU001, SKU002, SKU003 etc. The code works sort off, but I keep getting erros which does not make sense as I am catering for the errors.

So let me try and break it down a bit. let's say I have a table called TABLENAME with the following:

NAME  | STOCKCODE | PRICE | other columns ...
Item1 | SKU001    | 12.99
Item2 | SKU002    | 13.99
Item3 | SKU003    | 14.99

The temp table has only the NAME, STOCKCODE and PRICE columns.

To get the data, I have 2 tabular forms which runs the following.

select * from TABLENAME where regexp_like(NAME, :P4_SEARCH, 'i') or regexp_like(BARCODE, :P4_SEARCH, 'i') or regexp_like(STOCKCODE, :P4_SEARCH, 'i');

and for the TEMPTABLE the Apex page SQL is.

select STOCKCODE, NAME, PRICE TEMPTABLE where SESSION_USER=:P4_USER


DECLARE
l_stock VARCHAR2(200);
l_name VARCHAR2(200);
l_price VARCHAR2(200);
BEGIN
select STOCKCODE, NAME, PRICE
into l_stock, l_name, l_price
from TABLENAME where (NAME=:P4_SEARCH) or (BARCODE=:P4_SEARCH) or (STOCKCODE=:P4_SEARCH);
insert into TEMPTABLE (STOCKCODE, NAME, PRICE) 
 values (l_stock, l_name, l_price);
     exception
when too_many_rows then
select STOCKCODE, NAME, PRICE
into l_stock, l_name, l_price
from TABLENAME where regexp_like(NAME, :P4_SEARCH, 'i') or regexp_like(BARCODE, :P4_SEARCH, 'i') or regexp_like(STOCKCODE, :P4_SEARCH, 'i');
when no_data_found then
select STOCKCODE, NAME, PRICE
into l_stock, l_name, l_price
from TABLENAME where regexp_like(NAME, :P4_SEARCH, 'i') or regexp_like(BARCODE, :P4_SEARCH, 'i') or regexp_like(STOCKCODE, :P4_SEARCH, 'i');
END;

When I only this:

BEGIN
select STOCKCODE, NAME, PRICE
into l_stock, l_name, l_price
from TABLENAME where regexp_like(NAME, :P4_SEARCH, 'i') or regexp_like(BARCODE, :P4_SEARCH, 'i') or regexp_like(STOCKCODE, :P4_SEARCH, 'i');
END;

it works perfectly each time.

If I execute only this:

BEGIN
select STOCKCODE, NAME, PRICE
into l_stock, l_name, l_price
from TABLENAME where (NAME=:P4_SEARCH) or (BARCODE=:P4_SEARCH) or (STOCKCODE=:P4_SEARCH);
insert into TEMPTABLE (STOCKCODE, NAME, PRICE)
END;

but when running the entire PL/SQL script like above I get errors as such: When I search SKU001, it will display the Search item and add it to the TEMPTABLE, but when I search SKU it will give me an error:

ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at line 23 
ORA-01403: no data found

NOTE!! Line 23 display here is not Line 23 in the above code due to formatting of my browser, Line 23 is as below:

22 ->     when no_data_found then
23 -> select STOCKCODE, NAME, PRICE into stock, name, price from TABLENAME where regexp_like(NAME, :P4_SEARCH, 'i') or regexp_like(BARCODE, :P4_SEARCH, 'i') or regexp_like(STOCKCODE, :P4_SEARCH, 'i');

At the same time, if I clear or reset the session, it displays:

ORA-01403: no data found
ORA-06512: at line 23
ORA-01403: no data found

Which I find strange because I am adding the exception for no_data_found as well as for too_many_lines

What am I doing wrong here? I hope I have provided enough information here in order for it to make sense.

Last Note! The Barcode is part of the search and does not get inserted, just incase someone asks about it. The search is based on Name, Barcode or Stockcode, but Name, Price and Stockcode are displayed based on the search. P4_USER is a session user I built seperately that just does a select to display the user's inserts only, and not other user data.

Solution

The first query will not raise a too_many_rows exception unless you have duplicate barcode stockcode or name

just raise exeption for no_data_found because the first query wants exact match, will raise no data found and then run second query.

DECLARE
L_STOCK VARCHAR2(200);
L_BARCODE VARCHAR2(200);
L_NAME VARCHAR2(200);
L_PRICE VARCHAR2(200);
BEGIN
select STOCKCODE, NAME, PRICE
into l_stock, l_name, l_price
from TABLENAME 
where (:P4_SEARCH=NAME) or (:P4_SEARCH=BARCODE) or (:P4_SEARCH=STOCKCODE);
insert 
into TEMPTABLE (STOCKCODE, NAME, PRICE)
values (l_stock, l_name, l_price);
exception
when no_data_found 
then
select STOCKCODE, NAME, PRICE
into stock, name, price
from TABLENAME 
where regexp_like(NAME, :P4_SEARCH, 'i') or regexp_like(BARCODE, :P4_SEARCH, 'i') or regexp_like(STOCKCODE, :P4_SEARCH, 'i');
end;

This should then give you ether a match of SKU001 in the first query and insert into TEMPTABLE or if partial match only display results of last query.