I Imagine a table with products. For simplicity lets use just product name and description. CREATE TABLE products ( product_id NUMBER(10), product_name VARCHAR2(256), product_desc VARCHAR2(4000), -- CONSTRAINT pk_products PRIMARY KEY (product_id) ); -- DELETE FROM products; INSERT INTO products SELECT 1, 'First product name, one', 'Some description' FROM DUAL UNION ALL SELECT 2, 'Second product name, two', 'Another description' FROM DUAL UNION ALL SELECT 3, 'Third product, three', 'Other description' FROM DUAL; -- COMMIT; How would you search for a product with specific words in name and description? If you want to search for products containing 'First' OR 'one' words in name, then it is quite easy (case search and sanitization of inputs not included). WITH input AS ( SELECT /*+ MATERIALIZE */ 'First:one' AS in_name,
U Usually I see (and do) evaluations at the start of the procedure. When you check for multiple things, it can get lengthy and the reusability of the code is very low. Does this look familiar? DECLARE in_user_id CONSTANT VARCHAR2(30) := 'USER_NAME'; BEGIN -- check inputs IF in_user_id IS NULL THEN RAISE_APPLICATION_ERROR(-20000, 'USER_ID_MANDATORY'); END IF; -- IF LENGTH(in_user_id) < 3 THEN RAISE_APPLICATION_ERROR(-20000, 'USER_ID_MIN_LENGTH'); END IF; -- IF NOT REGEXP_LIKE(in_user_id, '@') THEN RAISE_APPLICATION_ERROR(-20000, 'USER_ID_FAILED'); END IF; -- continue with your code NULL; END; / Imagine this instead: DECLARE in_user_id CONSTANT VARCHAR2(30) := 'USER_NAME'; BEGIN -- check inputs assert.is_not_null('USER_ID_MANDATORY', in_user_id); assert.is_false('USER_ID_MIN_LENGTH', LENGTH(in_user_id) &