Oracle Apex 5 Search, display results but insert if exact match

Question

I have a search field that does a search (case insensitive) and returns the relevant values to a Tabular form.

  select STOCKCODE, BARCODE, NAME from TABLE where regexp_like(NAME, :P1_SEARCH, 'i') or regexp_like(BARCODE, :P1_SEARCH, 'i') or regexp_like(STOCKCODE, :P1_SEARCH, 'i')

So if a search is done for part of the STOCKCODE, BARCODE or NAME it will return the results to the table. This means all results that matches the search string.

Once I have what I want, I add a quantity value to the row and then hit the "Add" button. This button only takes the selection and inserts into another Table using a PL/SQL statement via a Procedure.

BEGIN
insert into TEMP_TABLE (STOCKCODE, NAME, BARCODE) values (:STOCKCODE, :NAME, BARCODE);
END;

This all works perfectly, but going through numerous searches becomes a mission when you find a match and then have to select it, then click add. So I want to do the following. Doing a search on a partial match should return the results into the tabular form. Alot of codes are known however so if I know one of the values in full and put that in, do not display it in the tabular form only, but do a direct insert into the TEMP_TABLE.

Something like this

select STOCKCODE, BARCODE, NAME from TABLE where regexp_like(NAME, :P1_SEARCH, 'i') or regexp_like(BARCODE, :P1_SEARCH, 'i') or regexp_like(STOCKCODE, :P1_SEARCH, 'i')
if (NAME=:P1_SEARCH) or (BARCODE=P1:SEARCH) or (STOCKCODE=:P1_SEARCH) then
insert into TEMP_TABLE (STOCKCODE, NAME, BARCODE) values (:STOCKCODE, :NAME, BARCODE);
end if;

Any ideas? :D

Tony Andrews Tony Andrews · Accepted Answer · 2016-10-20T11:05:53

You could have a page process that runs on page load and checks whether the input is a full match:

begin
  select name, barcode, stockcode
    into l_name, l_barcode, l_stockcode
    from table
   where (NAME=:P1_SEARCH) or (BARCODE=P1:SEARCH) or (STOCKCODE=:P1_SEARCH);

  -- Full match if no exception raised
  insert into TEMP_TABLE (STOCKCODE, NAME, BARCODE) 
     values (l_stockcode, l_name, l_barcode);

  :P1_SHOW_TABFORM := 'N';

exception
   when too_many_rows
     -- Partial match (more than one match found)
     :P1_SHOW_TABFORM := 'Y';
   when no_data_found then
     -- No match found
     (Do whatever you think best here)
end;

Then make the tabform report display conditional on P1_SHOW_TABFORM equal to Y.

Oracle Apex 5 Search, display results but insert if exact match

1 Answers