PL/SQL ORA-06550: component 'FNAME' must be declared-CodePudding

I get keep getting this error I can't figure out what is wrong.

Here is my code:

DECLARE
    fname doctor.dc_f_name%TYPE;
    lname doctor.dc_l_name%TYPE;
BEGIN
FOR rec IN(
    SELECT 
        dc_f_name, dc_l_name, dc_salary   
    FROM 
        Doctor
    WHERE 
        dc_salary > 600)
LOOP
    DBMS_OUTPUT.put_line(rec.fname || ' ' || rec.lname);
    dbms_output.put_line('____________________________');
END LOOP;
END;

Im new in PL/SQL so I can't figure out the solution.

The Error Report Is:

Error report - ORA-06550: line 13, column 30: PLS-00302: component 'FNAME' must be declared ORA-06550: line 13, column 5: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

CodePudding user response：

Assuming the columns of your table are dc_f_name, dc_l_name and dc_salary and in your sub-query you are not aliasing them to something else then you do not need to declare any variables and can when you refer to the attributes of the cursor row then you should use the same identifiers as in the query:

BEGIN
  FOR rec IN(
    SELECT dc_f_name,
           dc_l_name,
           dc_salary   
    FROM   Doctor
    WHERE  dc_salary > 600
  )
  LOOP
    DBMS_OUTPUT.put_line(rec.dc_f_name || ' ' || rec.dc_l_name);
    dbms_output.put_line('____________________________');
  END LOOP;
END;
/

Then, for the sample data:

CREATE TABLE doctor (
  dc_f_name VARCHAR2(50),
  dc_l_name VARCHAR2(50),
  dc_salary NUMBER(10,2)
);

INSERT INTO doctor (dc_f_name, dc_l_name, dc_salary)
SELECT 'Alice', 'Abbot', 1000000 FROM DUAL UNION ALL
SELECT 'Betty', 'Baron', 2000000 FROM DUAL UNION ALL
SELECT 'Carol', 'Count', 3000000 FROM DUAL;

The PL/SQL block outputs:

Alice Abbot
____________________________
Betty Baron
____________________________
Carol Count
____________________________

db<>fiddle here

CodePudding user response：

I would think the error is quite clear as dc_f_name isn't declared. Why not simply your code

Create table doctor (
fname VARCHAR2(30),
lname VARCHAR2(30),
salary integer);
/

INSERT into doctor (fname, lname, salary) VALUES ('John', 'Doe', 50000);

DECLARE
    fname doctor.fname%TYPE;
    lname doctor.lname%TYPE;
BEGIN
FOR rec IN(
    SELECT 
        fname, lname, salary   
    FROM 
        Doctor
    WHERE 
        salary > 600)
LOOP
    DBMS_OUTPUT.put_line(rec.fname || ' ' || rec.lname);
    dbms_output.put_line('____________________________');
END LOOP;
END;

Statement processed.
John Doe
____________________________