This is a procedure to update multiple table's data in oracle , as compiler is showing Error: ORA-00922: missing or invalid option error

Question

This is a procedure to update multiple table's data in oracle , do not know where i am getting wrong as compiler is showing Error: ORA-00922: missing or invalid option error please help

I have checked the syntax I think it is correct .

create or replace procedure add_Course(
  p_title_name in  course.tilte%type,
  p_course_id  in  course.course_id%type,
  p_credits    in  course.credits%type,
  p_program_id in  PROGRAMS.program_id%type,
  p_grade      in  PROGRAMS.grade%type,
  p_room_type  in  classroom.room_type%type,
  p_room_no    in  classroom.room_no%type,
  p_sec_id     in  section.sec_id%type,
  p_semester   in  section.semester%type,
  p_year       in  section.year%type,
  p_building   in section.building%type)
as 
  cnt number;
begin
  select count(*) into cnt from course where title=p_title_name;
  if cnt = 1 then
    update course set credits=p_credits where title=p_title_name;
    update program set program_id=p_program_id,semester=p_semester,year=p_year,grade=p_grade where course_id=p_course_id;
    update classroom set room_type=p_room_tpe, room_no=p_room_no, building=p_building where COURSE_TYPE=p_course_type;
    update section set sec_id=p_sec_id, semester=p_semestr, year=p_year,BUILDING=p_building,room_type=p_room_type where course_id=p_couse_id;
    dbms_output.put_line('course detils is changed');
    commit;

  else 
    dbms_output.put_line('couser dose not exist');

  end if;
end;

There are several typos in your code: `p_semestr`, `p_couse_id`, `p_room_tpe`. Certainly your code won't compile until you correct those to be the actual parameter names. — APC, Oct 11 '19 at 14:38
You should also correct the similar typos for your variable types (`course.tilte%type` -> `course.title%type`). — Acroyear, Oct 11 '19 at 15:14

score 0 · Answer 1 · answered Oct 11 '19 at 19:24

When the procedure is created (with errors), depending on tool you use, see errors. For example, in SQL*Plus:

SQL> create or replace procedure add_Course(
  2    p_title_name in  course.tilte%type,
  3    p_course_id  in  course.course_id%type,
  4    p_credits    in  course.credits%type,
  5    p_program_id in  PROGRAMS.program_id%type,
  6    p_grade      in  PROGRAMS.grade%type,
  7    p_room_type  in  classroom.room_type%type,
  8    p_room_no    in  classroom.room_no%type,
  9    p_sec_id     in  section.sec_id%type,
 10    p_semester   in  section.semester%type,
 11    p_year       in  section.year%type,
 12    p_building   in section.building%type)
 13  as
 14    cnt number;
 15  begin
 16    select count(*) into cnt from course where title=p_title_name;
 17    if cnt = 1 then
 18      update course set credits=p_credits where title=p_title_name;
 19      update program set program_id=p_program_id,semester=p_semester,year=p_year,grade=p_grade where course_id=p_course_id;
 20      update classroom set room_type=p_room_tpe, room_no=p_room_no, building=p_building where COURSE_TYPE=p_course_type;
 21      update section set sec_id=p_sec_id, semester=p_semestr, year=p_year,BUILDING=p_building,room_type=p_room_type where course_id=p_couse_id;
 22      dbms_output.put_line('course detils is changed');
 23      commit;
 24
 25    else
 26      dbms_output.put_line('couser dose not exist');
 27
 28    end if;
 29  end;
 30  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE ADD_COURSE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/20     PLS-00201: identifier 'COURSE.TILTE' must be declared
SQL>

Option that works everywhere is to select that info from user_erros:

SQL> select * from user_errors where name = 'ADD_COURSE';

NAME                           TYPE           SEQUENCE       LINE   POSITION
------------------------------ ------------ ---------- ---------- ----------
TEXT
--------------------------------------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
ADD_COURSE                     PROCEDURE             1          2         20
PLS-00201: identifier 'COURSE.TILTE' must be declared
ERROR                201

ADD_COURSE                     PROCEDURE             2          0          0
PL/SQL: Compilation unit analysis terminated
ERROR                  0


SQL>

This should help you fix most errors. Say if there's any you don't understand or can't fix.

score 0 · Accepted Answer · answered Oct 15 '19 at 10:43

0

There was some syntax error ,

There are several typos in your code: p_semestr, p_couse_id, p_room_tpe. Certainly your code won't compile until you correct those to be the actual parameter names

answered Oct 15 '19 at 10:43

MAHENDRA

1
2

This is a procedure to update multiple table's data in oracle , as compiler is showing Error: ORA-00922: missing or invalid option error

2 Answers2