SQL*Plus Tricks
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.