Invalid reference in Oracle database

Question

I am using Oracle database 19c for a project. I have created the alumni table as shown below:

CREATE TABLE Alumni (
ID                  NUMBER GENERATED ALWAYS AS IDENTITY ,
First_Name          varchar2(20) NOT NULL,
Last_Name           varchar2(20) NOT NULL,
Gender              char(1) NOT NULL,
                CHECK (Gender IN('M','F')),
Graduation_Year     number NOT NULL,
                check (Graduation_Year>=1980 AND Graduation_Year<=2020),
Degree_Course       varchar2(255) not null,
Award_Nominated     CHAR(1),
                CHECK (Award_Nominated IN('Y','N')),
Award_Won           CHAR(1),
                CHECK (Award_Won IN('Y','N')),
Phone_Number        VARCHAR2(15) not null,
Email_Address       VARCHAR2(255) not null,
CONSTRAINT ALUM_PK PRIMARY KEY(ID)
);

Then I tried to define a function that will allow me to search by First or last name in a section of the table, shown below:

CREATE OR REPLACE TYPE alum_row_type is object(ID number,First_name varchar2(20),Last_Name 
varchar2(20),Phone_number varchar2(15),Email_Address varchar2(255));

CREATE OR REPLACE TYPE t_alum_row_type as table of alum_row_type;

CREATE OR REPLACE FUNCTION Name_Search RETURN t_alum_row_type
IS
    L_alum_row t_alum_row_type := t_alum_row_type();
    X integer :=0;
BEGIN
    FOR R IN (SELECT ID,First_Name, Last_Name,Phone_Number,Email_Address FROM alumni)
    LOOP
        L_alum_row.extend;
        X:=X+1;
    L_alum_row(X):= alum_row_type(X.ID,X.First_Name, X.Last_Name, X.Phone_Number, X.Email_Address);
    END LOOP;
    RETURN L_alum_row;
END;

After running this, I get this error:

LINE/COL  ERROR
--------- -------------------------------------------------------------
10/9      PL/SQL: Statement ignored
10/41     PLS-00487: Invalid reference to variable 'X'
Errors: check compiler log

How can I resolve this issue. I am relatively new to PL\SQL, so please explain it simply.

X is an integer, so what is X.ID supposed to be? Presumably those references should all be R.ID etc.? — Alex Poole, Apr 20 '21 at 17:50
[You should really avoid `char` for table columns](https://stackoverflow.com/a/42165653/230471). — William Robertson, Apr 20 '21 at 21:23

score 1 · Accepted Answer · answered Apr 20 '21 at 19:39

This is not possible

L_alum_row(X):= alum_row_type(X.ID,X.First_Name, X.Last_Name, X.Phone_Number, X.Email_Address);

You are referencing X.ID, X.Phone_Number etc. . . but declared X as an integer. If you are trying to access the current loop record, try with

L_alum_row(X):= alum_row_type(R.ID,R.First_Name, R.Last_Name, R.Phone_Number, R.Email_Address);

As you declared the loop iterator as R for your cursor

 FOR R IN (SELECT ID,First_Name, Last_Name,Phone_Number,Email_Address FROM alumni)

score 1 · Answer 2 · answered Apr 20 '21 at 20:57

You can eliminate the loop altogether by using BULK COLLECT. Thereby reducing the function to basically a single select statement.

function name_search 
  return t_alum_row_type
is
    l_alum_row t_alum_row_type := t_alum_row_type();
begin
    select alum_row_type(id,first_name, last_name,phone_number,email_address)  
      bulk collect 
      into l_alum_row
      from alumni; 
      
    return l_alum_row;
end;

Invalid reference in Oracle database

2 Answers2