Home > other >  What are the errors in the following oracle database code?
What are the errors in the following oracle database code?

Time:05-01

In the oracle, the "STAFF" has four columns: id, first_name, last_name and salary. Columns id and salary have NUMBER data type. Columns first_name and last_name have VARCHAR2(20) data type.

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);

CodePudding user response:

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

CodePudding user response:

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;
  • Related