I learned something new today (I think)

I'm not sure if I had not seen this before, or if I had and simply forgotten about it.

You can use the assignment statement ":=" to designate a parameter as having a default value as well as using the DEFAULT keyword!

DECLARE
  PROCEDURE p_default_param1(in_param IN VARCHAR2 := 'Default')
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('in_param = '||in_param);
  END p_default_param1;

  PROCEDURE p_default_param2(in_param IN VARCHAR2 DEFAULT 'Default')
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('in_param = '||in_param);
  END p_default_param2;
BEGIN
  p_default_param1;
  p_default_param1('A');

  p_default_param2;
  p_default_param2('B');
END;
/
in_param = Default
in_param = A
in_param = Default
in_param = B


PL/SQL procedure successfully completed.

For the sake of semantic clarity I much prefer the DEFAULT keyword. The extra keystrokes are worth it, in my opinion.

NVL() does not short circuit

Here's an interesting tidbit about NVL(expr1,expr2).  The documentation states that " If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1." What you may not know is that both expr1 and expr2 are evaluated even if expr1 is not null. NVL does not short circuit. Fortunately alternative methods like COALESCE and the CASE statement will.

DECLARE
  str VARCHAR2(1);

  FUNCTION dummy_function RETURN VARCHAR2
  IS
  BEGIN
    DBMS_OUTPUT.PUT('(Function ran)');
    RETURN 'X';
  END dummy_function;
BEGIN
  str := 'A';

  DBMS_OUTPUT.PUT_LINE('NVL: '||NVL(str,dummy_function));
  DBMS_OUTPUT.PUT_LINE('COALESCE: '||COALESCE(str,dummy_function));
  DBMS_OUTPUT.PUT_LINE('CASE: '||CASE WHEN str IS NOT NULL THEN str ELSE dummy_function END);
END;
/
(Function ran)NVL: A
COALESCE: A
CASE: A


PL/SQL procedure successfully completed.

This is definitely something to keep in mind if, like in the example above, you are calling a function for expr2. If that function does a significant amount of processing and/or the NVL() is inside of a loop with many iterations, the cycles it takes to execute the function every time could add up.

Implicit Cursors are Wonderful

I was sent this script for review. Can you see what's wrong with this picture?

DECLARE
  l_limit CONSTANT PLS_INTEGER := 1000;
  
  TYPE varchar2_tab_typ IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  
  varchar2_tab   varchar2_tab_typ;
  
  CURSOR grants_cur IS
   SELECT 'GRANT SELECT ON '||table_name||' TO some_schema'
   FROM   user_tables;
   
BEGIN
  varchar2_tab.DELETE;
  
  OPEN grants_cur;
  
  LOOP
    FETCH grants_cur BULK COLLECT INTO varchar2_tab LIMIT l_limit;
    
    EXIT WHEN grants_cur%NOTFOUND;
  END LOOP;
  
  CLOSE grants_cur;
  
  FOR i IN 1..varchar2_tab.COUNT LOOP
    EXECUTE IMMEDIATE varchar2_tab(i);
  END LOOP;
END;
/

Too much of a good thing?

I remember one fateful day when I found the following line of PL/SQL code at a client site

g_period CONSTANT VARCHAR2(1) := '.';

Constants are great. They allow you to rely on a persistent value for processing. They are also really good if you need to use a value again and again in code, the value must be the same throughout the code, and if the value could change due to new requirements, change in direction, etc. For example, your code uses the following constant to denote a value of "True"

g_true CONSTANT VARCHAR2(4) := 'TRUE';

At some later point it is decided that the value should simply be the letter 'T' and not 'TRUE'. If you've used the constant throughout your code it's as simple as changing the value of the constant in one place - where it's declared. Quick and easy. That's just good programming.

g_true CONSTANT VARCHAR2(1) := 'T';

The g_period example listed above is a little overboard. All the programmer had accomplished was declared a constant of something that is already a constant! If we followed that example we might as well declare every character as a constant and use them everywhere in the code.

g_c CONSTANT VARCHAR2(1) := 'C';
g_h CONSTANT VARCHAR2(1) := 'H';
g_r CONSTANT VARCHAR2(1) := 'R';
g_i CONSTANT VARCHAR2(1) := 'I';
g_s CONSTANT VARCHAR2(1) := 'S';

Now I can spell my name and never worry about the letters changing!

DECLARE
  l_my_name VARCHAR2(5);
BEGIN
  l_my_name := g_c||g_h||g_r||g_i||g_s;
END;
/

It looks a little silly when it's put like that, doesn't it?

Using constants properly can save you a lot of pain, and using them improperly can cause just as much pain as you may save. Food for thought.

Happy coding.