Skip to main content


Showing posts from January, 2023

Searching for multiple words

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,

On checking input values

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) &

APEX & Stripe payment gateway (demo app)

I I have been ask to implement Stripe payments into APEX. I see two options here. You can either do it in JavaScript or in PL/SQL. You can easily call a web service from PL/SQL, you wont need any extra JS library and code would be probably more secured. So let's do that. You can find several other articles about this around. If you saw those, you can evaluate yourself on how is this different. The easiest API choice on almost every payment gateway is called "checkout" or "checkout form". The beauty about this is that there are no sensitive data processed on your side, everything is processed on the payment gateway side. No worries about storing or processing client cards. You should check the Stripe Checkout and specially Quickstart . Stripe (and others) like to show the user a list of products with quantities and prices during the checkout. Hence you need to create products on Stripe and you have to do that everytime you add (or change) a new produc