MacLochlainns Weblog

Somebody said, I shouldn’t have buried so much information in an answer to a question posed in a comment on a summary blog page. They suggested I put it in a regular blog post, and here it is with as little editing as possible.

The Ron Quizon user provided this sample code and a “What’s wrong with this PL/SQL program?”

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

DECLARE
   v_name friends.fname%TYPE;
   v_grade friends.id%TYPE;
BEGIN
   SELECT fname, grade
      INTO &ssv_name, v_grade
   FROM friends
   WHERE v_name = fname;
   DBMS_OUTPUT.PUT_LINE(NVL(v_name,'No Name ')||' has an id of '||NVL(v_grade, 0));
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('There is no record with '||'id 123');
END;

While this certainly looks like a question from a class on PL/SQL or something from Steven Feuerstein’s PL/SQL question quizzes, I paused before answering it. The give away is the style is what Steven’s advocated for two decades. My guess is that it’s for Steven’s Q&A stuff, which means there’s no harm in answering it because I’m likely not defeating a teacher’s learning objective.

There are two core errors. The first error is an inappropriate assignment target on line #6 and the second is failing to assign a value to the local v_name variable. If you’d taken the time to create the tables and try it, you should generate an error like this:

SQL> /
Enter VALUE FOR ssv_name: Harry
OLD   6:       INTO &ssv_name, v_grade
NEW   6:       INTO Harry, v_grade
      INTO Harry, v_grade
           *
ERROR at line 6:
ORA-06550: line 6, COLUMN 12:
PLS-00201: identifier 'HARRY' must be declared
ORA-06550: line 7, COLUMN 4:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 5, COLUMN 4:
PL/SQL: SQL Statement ignored

Also, at this point if you couldn’t see the error by quick inspection, it should be transparent to you. However, I don’t believe in playing games. Here’s the answer you need:

• The SELECT-INTO statement is an left-to-right assignment operator in SQL (available in all dialects), and the right operand (variable) or list of operands (variables) must be identifier(s). “Identifiers are words. They can be reserved words, predefined identifiers, quoted identifiers, user-defined variables, subroutines, or user-defined types. (Oracle Database 11g PL/SQL Programming on page #51).” In this case, as the right operand(s), they are user-defined variables.
• The & (ampersand) preceding ssv_name makes that a substitution placeholder or target, which is a SQL*Plus prompt for a value. The value provided at run-time is assigned to the SQL*Plus placeholder as a string literal during the preparing phase. That phase precedes the anonymous block parse, fetch, and execute cycle. Therefore, you raise a parsing error while running the anonymous block unless the &ssv_name input value is a valid locally declared variable or defined session bind variable name.
• Assuming you input a valid identifier, the next problem is that the WHERE clause uses an equality comparison operator against the v_name local variable, which holds a null value. That means the SELECT-INTO always fails on a no data found error unless you add an assignment statement for the v_name variable.

Anyway, I hope spelling it out in a formal blog post was helpful to someone in the future. If so, leave a comment and let me know.