2 Replies Latest reply on Apr 26, 2012 5:13 AM by 930814

    PL/SQL: ORA-00984: column not allowed here

    930814
      I am trying to learn sql, so this is most likely very simple. If there is any more information that you need me to provide, just request it. I am trying to use a trigger that takes the input and puts it into three tables different tables. I am not sure what I am doing wrong as my error message doesn't seem to tell me much. Maybe one of you could get more out of it?

      I am using sql developer, 11g and this is installed on Windows 7.

      The error reads:
      14/32          PL/SQL: ORA-00984: column not allowed here
      14/5           PL/SQL: SQL Statement ignored
      My tables, sequences and trigger are:
      CREATE TABLE students
      (
      student_id number,
      first_name varchar2(50) not null,
      last_name varchar2(50) not null,
      CONSTRAINT student_id_pk PRIMARY KEY (student_id)
      )
      
      CREATE TABLE courses
      (
      course_id number,
      courses varchar2(50),
      CONSTRAINT course_id_pk PRIMARY KEY (course_id)
      )
      
      CREATE TABLE registration
      (
      reg_id number not null UNIQUE,
      student_id number,
      course_id varchar2(100),
      CONSTRAINT student_course_pk PRIMARY KEY (student_id, course_id)
      )
      
      CREATE SEQUENCE courses_seq
      CREATE SEQUENCE reg_id_seq
      CREATE SEQUENCE course_id_seq
      
      CREATE OR REPLACE TRIGGER instead_insert_multiple
      AFTER INSERT
      ON student_info_original
      FOR EACH ROW
      DECLARE
      placeholder number:=1;
      course varchar2(50);
      firstname varchar2(50);
      lastname varchar2(50);
      BEGIN
        firstname := SUBSTR(:NEW.student_name, 1, (INSTR(:NEW.student_name,' ')));
           lastname := SUBSTR(:NEW.student_name, (INSTR(:NEW.student_name,' ')+1));
        
        INSERT INTO students VALUES(:NEW.student_id, firstname, lastname);
        
        WHILE placeholder<length(:NEW.courses) LOOP
          course := SUBSTR(:NEW.courses, placeholder, INSTR(', CIS', 0)+8);
          INSERT INTO courses VALUES(courses_seq, course);
          INSERT INTO registration VALUES(reg_id_seq.NEXTVAL, :NEW.student_id, course_id_seq.NEXTVAL);
          placeholder := placeholder+10;
           END LOOP;
      END;
      /