What are the errors in the following Oracle database code?

Question

In Oracle, the STAFF table has four columns:

id, first_name, last_name, salary

Columns id and salary are of NUMBER datatype, while columns first_name and last_name are of VARCHAR2(20) datatype.

Here is the PL/SQL code that manipulates the STAFF table. Is there any error?

v_id NUMBER := 200;
v_fname VARCHAR2(20);
v_lname
v_sal NUMBER;
BEGIN
SELECT first_name, last_name, salary
INTO v_fname, v_lname
FROM STAFF
WHERE id=v_id;
DBMS_OUTPUT.PUT(v_fname||‘ ‘||v_fname||’ makes ’||v_sal);

Answer 1

You need:

• DECLARE at the start.
• END; at the end and then a / on a newline.
• ' for string literals and not ‘
• DBMS_OUTPUT.PUT_LINE instead of DBMS_OUTPUT.PUT (which does not flush the line to the buffer).

DECLARE
    v_id NUMBER := 200;
    v_fname VARCHAR2(20);
    v_lname VARCHAR2(20);
    v_sal NUMBER;
BEGIN
    SELECT first_name, last_name, salary
    INTO v_fname, v_lname, v_sal
    FROM STAFF
    WHERE id=v_id;
    DBMS_OUTPUT.PUT_LINE(v_fname||' '||v_fname||' makes '||v_sal);
END;
/

db<>fiddle here

Answer 2

Looks like you didn't provide a DECLARE statement and you left out a type value for v_lname. Also you didn't include v_sal in the INTO clause. Try this:

DECLARE
    v_id NUMBER := 200;
    v_fname VARCHAR2(20);
    v_lname VARCHAR2(20);
    v_sal NUMBER;
BEGIN
    SELECT first_name, last_name, salary
    INTO v_fname, v_lname, v_sal
    FROM STAFF
    WHERE id=v_id;
    DBMS_OUTPUT.PUT(v_fname||' '||v_fname||' makes '||v_sal);
END;