MacLochlainns Weblog

Have you ever wondered how to leverage substitution variables in anonymous block programs? There are several tricks that you can use beyond passing numeric and string values to local variable. The generic default appears to take a number unless you cast it as a string but that’s not really the whole story. The first two are standard examples of how to use numeric and string substitution values.

The following accept a numeric substitution value:

1
2
3
4
5
6
7
8
9
10

DECLARE
  lv_input  NUMBER;
BEGIN
  /* Assign substitution value to local variable. */
  lv_input := &input;
 
  /* Print the local variable. */
  dbms_output.put_line('['||lv_input||']');
END;
/

The following accept a string substitution value, casts the input as a string, assigns the string value to a 4,000 character length local variable, checks whether the 4,000 character length is greater than 10, and assigns the first 10 characters to the lv_parse_input variable:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

DECLARE
  lv_unparsed_input  VARCHAR2(4000);
  lv_parsed_input    VARCHAR2(10);
BEGIN
  /* Assign substitution value to local variable. */
  lv_unparsed_input := '&input';
 
  /* Check size of input value. */
  IF LENGTH(lv_unparsed_input) > 10 THEN
    lv_parsed_input := SUBSTR(lv_unparsed_input,1,10);
  END IF;
 
  /* Print the local variable. */
  dbms_output.put_line('Print {lv_parsed_input}: ['||lv_parsed_input||']');
END;
/

Next, let’s examine two tricks. The first passes a case insensitive variable name and the second passes a case sensitive variable name as a parameter to an anonymous block program.

This declares an anonymous block program that uses a substitution value as a variable name:

1
2
3
4
5
6
7

DECLARE
  mine  VARCHAR2(10) := 'Default';
BEGIN
  /* Print the local variable's value. */
  dbms_output.put_line('Print {mine} variable value: ['||&input||']');
END;
/

When you run the anonymous block, you’re prompted for an input variable. You provide a case insensitive variable name as the input value:

Enter value for input: MINE
old   5:   dbms_output.put_line('['||&input||']');
new   5:   dbms_output.put_line('['||MINE||']');
Print {mine} variable value: [Default]

The downside of this approach, yields an ORA-06550 and PLS-00201 exception. Neither of these can be caught because Oracle raises the errors during parsing when the variable name isn’t a 100% case insensitive match. The same type of problem occurs in the next example when the input variable isn’t a 100% case sensitive match.

You can rewrite the program to handle case insensitive variables like this:

1
2
3
4
5
6
7

DECLARE
  "Mine"  VARCHAR2(10) := 'Default';
BEGIN
  /* Print the local variable's value. */
  dbms_output.put_line('Print {mine} variable value: ['||"&input"||']');
END;
/

When you run the anonymous block, you’re prompted for an input variable. You provide a case sensitive variable name as the input value:

Enter value for input: Mine
old   5:   dbms_output.put_line('['||&input||']');
new   5:   dbms_output.put_line('['||"Mine"||']');
Print {Mine} variable value: [Default]

Hope this helps those looking for a solution.