Obsessed with Oracle PL/SQL

Suppose I've created a table to keep track of hominids : CREATE TABLE hominids ( hominid_name VARCHAR2 (100), home_territory VARCHAR2 (100), brain_size_cm INTEGER ) / I might then write code like this: DECLARE l_b_hominid_name VARCHAR2 (100) := 'Bonobo'; l_b_brain_size_cm INTEGER := 500; l_g_hominid_name VARCHAR2 (100) := 'Gorilla'; l_g_brain_size_cm INTEGER := 750; l_n_hominid_name VARCHAR2 (100) := 'Neanderthal'; l_n_brain_size_cm INTEGER := 1800; What do you think? I find the little voice of Relational Theory inside my head rebelling. "All that repetition! All that denormalization ! All that typing (or copy-pasting, which is even worse)!" Surely if I should avoid having redundant data in rows of my tables, I should avoid redundant code, too? Yes, I should.   I don't like to see long lists of declarations, especially when the names are very similar and follow a pattern.  A f...

I love to follow in the footsteps of people who are braver, smarter and more knowledgeable than me. So I was happy to wait till SQL Maria ( Maria Colgan ) published her blog post on  Oracle Database 12c now available on Docker , with step-by-step instructions for taking advantage of the new Docker image for 12.2 now available (specifically, 12.2 via Docker on Github, 12.2 via Docker at the Docker Store). I am happy to report that I can now connect SQL Developer to my containerized 12.2 database. Thank you, Maria, for a very helpful post! Now, I am not going to repeat everything Maria already wrote. That would be silly. I will simply point out some things you might find helpful as you do the same thing I did (follow in Maria's footsteps - which, literally, meant lots of copy-pasting rather dumbly). 1. Watch out for those dashes when you copy/paste. Docker was not responding as expected to my commands and I (well, actually, Gerald ) eventually noticed that the dash, copied ...

A developer contacted me with the following questions last week: We have created a function that returns a single row column value form a query. When we call this function with the same input values it takes to long to return. Example: select max (det_function('A2')) from dual connect by rownum <= 1000000 But when we change the function to a deterministic function the statement returns really fast. The only thing where we are unsure is what happens when the tables has changed to which the statement of the function selects? Do we need a to commit this table to bring oracle to re-execute the statement in the function and not use the cache or what should we do to get a consistent return value? FUNCTION det_function (v_in_id VARCHAR2) RETURN NUMBER DETERMINISTIC AS v_ident NUMBER; BEGIN SELECT VALUE INTO v_ident FROM my_table WHERE id = v_in_id; RETURN v_ident; EXCEPTION WHEN VALUE_ERROR OR NO_DATA_FOUND THEN RETURN -1; END; A function is de...