Oracle sql - Errors creating a procedure

Question

I've created this procedure

create or replace procedure enrollstudent(
    rno in STUDENTS.ROLL_NUMBER%type,
    sname in STUDENTS.STUDENT_NAME%type,
    cid in STUDENTS.COURSEID%type,
    cfees in STUDENTS.FEES%type,
    spercent in students.percentage%type
)as 
    discount number;
begin 
    discount := spercent*5;
    cfees := cfees-discount;
    insert into STUDENTS values(rno, sname, cid, cfees, spercent);
    commit;
end;

that works with this table

CREATE TABLE STUDENTS(
    ROLL_NUMBER  NUMBER(20)   NOT NULL,
    STUDENT_NAME VARCHAR2(25) NOT NULL,
    COURSEID     NUMBER(20)   NOT NULL,
    FEES         NUMBER(20)           ,
    PERCENTAGE   NUMBER(20)
);

When I run the procedure creation I have a Procedure ENROLLSTUDENT compiled and it gets created but i have the following errors in the compiler log

Error(8,1): PL/SQL: Statement ignored
Error(8,1): PLS-00363: expression 'CFEES' cannot be used as an assignment target

If I try to run the procedure with some data I simple have PLS-00905: object [schema].ENROLLSTUDENT is invalid

OldProgrammer · Accepted Answer · 2017-12-18 22:52:19Z

2

CFEES is an IN parameter. You can't change it. Create a local variable, and use that, example:

cfees2 NUMBER := cfees;
cfees2 := cfees2 - discount;
insert into STUDENTS values(rno, sname, cid, cfees2, spercent);

answered Dec 18, 2017 at 22:52

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5683823 · Accepted Answer · 2017-12-19 01:54:10Z

0

You don't need to define and calculate any local variables; you can do the computation of cfees in place (right in the INSERT statement):

create or replace procedure enrollstudent(
    rno in STUDENTS.ROLL_NUMBER%type,
    sname in STUDENTS.STUDENT_NAME%type,
    cid in STUDENTS.COURSEID%type,
    cfees in STUDENTS.FEES%type,
    spercent in students.percentage%type
)as
begin 
    insert into STUDENTS values(rno, sname, cid, cfees-spercent*5, spercent);
    commit;
end;
/

answered Dec 19, 2017 at 1:54

user5683823

Collectives™ on Stack Overflow

Oracle sql - Errors creating a procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related