SQL Stored Procedure Invalid Identifier

Question

I'm getting ORA-00904 Invalid Identifier for several variables in this stored procedure when trying to compile it in oracle but cannot figure out why; All working storage events are declared at the DECLARE block of the code, and the variables from the schema are all properly defined too. Has anyone had this error and/or knows how to fix it?

This is the DDL for the schema im working with

CREATE TABLE history
("history_id" number(4) primary key,
  "history_dt" date not null,
  "history_time" timestamp not null,
  "booking_cost" varchar2(50) not null,
  "booking_status" varchar2(50) not null
);


CREATE TABLE attendees 
("attendee_id" number(8) primary key,
  "attendee_name" varchar2(50) not null,
  "attendee_class" number(4) not null,
  "attendee_school" varchar2(50) not null,
  "attendee_status" varchar2(50) not null
);


CREATE TABLE event 
("event_id" number(10) primary key,
  "event_name" varchar2(100) not null,
  "event_location" varchar2(100) not null,
  "event_size" number(4) not null,
  "start_dt" date not null,
  "end_dt" date not null,
  "class_restriction" number(4) not null,
  "school_restriction" varchar2(100) not null
);


CREATE TABLE reservation 
("reservation_id" number(3) primary key,
  "event" number(10) references event("event_id"),
  "attendee" number(8) references attendees("attendee_id"),
  "booking" number(4) references history("history_id"),
  "reservation_status" varchar2(50) not null
);

These are the error messages I'm getting

Compilation failed,line 19 (15:38:10) PL/SQL: ORA-00904: "END_DT": invalid identifierCompilation failed,line 18 (15:38:10)

PL/SQL: SQL Statement ignoredCompilation failed,line 26 (15:38:10) PLS-00302: component 'EVENT_ID' must be declaredCompilation failed,line 26 (15:38:10) PL/SQL: ORA-00904: "RESERVATION"."EVENT_ID": invalid identifierCompilation failed,line 26 (15:38:10)

PL/SQL: SQL Statement ignoredCompilation failed,line 36 (15:38:10) PL/SQL: ORA-00904: "EVENT_ID": invalid identifierCompilation failed,line 35 (15:38:10)

PL/SQL: Statement ignoredCompilation failed,line 51 (15:38:10) PL/SQL: ORA-00947: not enough valuesCompilation failed,line 51 (15:38:10)

create or replace procedure Event_Planning
(arg_event_id in number, arg_student_id in number)

IS
ws_event_name varchar(100);
ws_capacity number;
ws_event_school varchar2(4);
ws_event_class number;

past_event exception;
capacity exception;
school exception;
class exception;

BEGIN
--Test for active event

select max(event_name) into ws_event_name from event
where event_id = arg_event_id and end_dt > SYSDATE;

if ws_event_name is null
then raise past_event;
end if;

--Test for capacity
select max(event_capacity) into ws_capacity from event JOIN reservation ON event.event_id = reservation.event_id
where event_id = arg_event
and event_capacity > reservation_size;

if ws_capacity is null
then raise capacity;
end if;

--Test for restricted school
select max(school_restriction) into ws_event_school from event
where event_id = arg_event_id;

if ws_event_school = arg_school
then raise school;
end if;

--Test for restricted class
select max(class_restriction) into ws_event_class from event
where event.id = arg_event;

if ws_event_class = arg_class
then raise class;
end if;

--Update reservation table
insert into reservation values
(Seq.nextval, arg_event, arg_student);

update reservation
set reservation_size = reservation_size + 1;

--Exceptions
Exception
when past_event
then raise_application_error(-20001, 'Event has passed');
when capacity
then raise_application_error(-20002, 'Event at capacity');
when school
then raise_application_error(-20003, 'Invalid school');
when class
then raise_application_error(-20004, 'Invalid class');

END;

Answer 1

That doesn't need to use end_date on filters, because event_id is a primary key.

select "event_name" into ws_event_name
from event
where "event_id" = arg_event_id;

That looks so confuse to know, where the following columns come from: ---=^^^=--- -- event_capacity -- reservation_size

select max("event_size") into ws_capacity
from event
join reservation
    on event."event_id" = reservation."event"
where "event_id" = arg_event_id
    and "event_size" > count("reservation_id");

Syntax error for event.id, it must event_id

select "class_restriction" into ws_event_class
from event
where "event_id" = arg_event_id;

Insert into reservation table:

select count(*) into reserve_count
from reservation
where "event" = arg_event_id
   and "attendee" = arg_studen_id;

if reserve_count = 0
then
    insert into reservation values
    (Seq.nextval, arg_event_id, arg_student_id, null, "R");
end if;

--- note: that needs to declare reserve_count

Use count() to populate attendees, then no need to update reservation table.

-- update reservation
-- set reservation_size = reservation_size + 1;

reservation table:

CREATE TABLE reservation
("reservation_id" number(13) primary key,
 "event" number(10) references event("event_id"),
 "attendee" number(8) references attendees("attendee_id"),
 "booking" number(10) references history("history_id"),
 "reservation_status" varchar(1) not null
);

To merge as a single query:

select count(*), "event_name", "class_restriction"
   into event_count, ws_event_name, ws_event_class
from event
where "event_id" = arg_event_id;

-- note: that needs to declare event_count

Answer 2

By default, Oracle identifiers such as table and column names are case-insensitive, so for example you can

select dummy, DUMMY, Dummy
from   dual;

However, double-quoting is also available to allow you to override the standard rules if you really need to:

create table demo("123/Wow!" number);

SQL> desc demo

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
123/Wow!                                           NUMBER

Your tables history, attendees, event etc have case-sensitive, lowercase column names due to the double quoting, so you have to follow the same convention whenever you use them:

SQL> select count(event_id) from event;
select count(event_id) from event
             *
ERROR at line 1:
ORA-00904: "EVENT_ID": invalid identifier


SQL> select count("event_id") from event;

COUNT("EVENT_ID")
-----------------
                0

1 row selected.

Unless there is some important reason to do this, it would be simplest to recreate the tables without double-quoted column names.

(Also, reservation has an "event" column, not "event_id". There may be other typos like this - I haven't checked the whole thing.)

Answer 3

To make it more likely to have your questions answered, share everything.

That includes, the actual error messages. And if you REALLY want to be nice, include the TABLE DDL (and even some data) for your EVENT and RESERVATION tables.

I was too 'lazy' to guess what yours looked like, and just changed your queries to dummy tables to replicate the issue.

You haven't declared

ws_school
arg_school
ws_class
arg_class

When you compile, the compiler will return the line number and curpos of your issue. You're referring to things the db doesn't know about.

Recommendations Don't hard code the data type definitions for your variables. Because, tables CAN and WILL change.

Instead, make them dynamic.

So, instead of saying

WS_SCHOOL        VARCHAR2(4);

do something like

WS_SCHOOL        TABLE.COLUMN%TYPE;

Then when your tables change, your code won't necessarily break.