PL/SQL stored procedures

Question

Please help me with the syntax of this stored procedure? This is being done inside oracle's SQL developer.

The purpose of the stored procedure is to allow the CALL function to insert a new record into the CLASS table (which was created using the syntax below):

CREATE TABLE class
(
class_number NUMBER(8), 
teacher_name VARCHAR2(50),
class_name VARCHAR2(50) NOT NULL,
start_date DATE,
end_date DATE,
class_category VARCHAR2(20),
topic VARCHAR2(20),
teacher_credential VARCHAR2(40),
CONSTRAINT pk_class PRIMARY KEY (class_number,teacher_name,class_name,start_date)
);

With the stored procedure and sequence coded below, a new class is supposed to be created and if the end date is before the start date, then the end date is supposed to be the start date + two weeks by default.

CREATE SEQUENCE class_number_seq;

 CREATE OR REPLACE PROCEDURE insert_class
(
  Teacher_name_param class.teacher_name%TYPE,
  class_name_param class.class_name%type,
  start_date_param class.start_date%TYPE ,
  end_date_param class.end_date%TYPE,
  class_category_param class.class_category%TYPE,
  topic_param class.topic%TYPE,
  end_date_param class.end_date%TYPE
  )
  AS 
      class_number_var class.class_number%TYPE;
      end_date_var class.end_number%TYPE;


BEGIN
   SELECT class_number_seq.NEXTVAL INTO class_number_var FROM dual;

   IF end_date_param < start_date_param OR end_date_param IS NULL THEN
     end_date_param := start_date_param + 14; 
    ELSE
      end_date_var := end_date_param;
    END IF;
   INSERT INTO class
   VALUES(class_number_var, teacher_name_param, class_name_param, start_date_param, end_date_param, class_category_param, topic_param);
    END;

the error I'm getting is:

PL/SQL complilation unit analysis terminated and: duplicated fields in RECORD, TABLE or argument listed are not permitted.

I'm not sure if there is anything wrong with my logic -- or where the duplicate fields are.

Help please!

ramana_k · Accepted Answer · 2015-10-31 18:11:53Z

end_date_param class.end_date%TYPE is listed twice in the parameter list of procedure. Removing one of them should fix this error.

CREATE OR REPLACE PROCEDURE insert_class ( Teacher_name_param class.teacher_name%TYPE, class_name_param class.class_name%type, start_date_param class.start_date%TYPE , end_date_param class.end_date%TYPE, class_category_param class.class_category%TYPE, topic_param class.topic%TYPE, end_date_param class.end_date%TYPE )

I think the following line has a typo

end_date_var class.end_numberdate%TYPE;

The following statement may cause an error, because end_date_param is not declared as OUT parameter. So end_date_param here should probably be changed to end_date_var

IF end_date_param < start_date_param OR end_date_param IS NULL THEN end_date_paramvar := start_date_param + 14; ELSE

The insert statement provides 7 values, whereas the table has 8 columns.

INSERT INTO class
VALUES(class_number_var, teacher_name_param, class_name_param,
start_date_param, end_date_param, class_category_param, topic_param);

This may result in too few values error.

It is usually better to write it as

INSERT INTO class
(class_number, teacher_name, class_name, 
 start_date, end_date, class_category, topic)
VALUES(class_number_var, teacher_name_param, class_name_param,
 start_date_param, end_date_param, class_category_param, topic_param);

so we can verify easily and avoid errors.

Collectives™ on Stack Overflow

PL/SQL stored procedures

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related