CODEWITHSUNDEEP
databaseoracle-databaseplsql
935
935

Reputation: 59

PL/SQL Using stored procedure error - Identifier must be declared

Here is my successfully stored procedure

CREATE OR REPLACE Procedure add_student
    (ns_sid IN STUDENTS.sid%type,
    ns_firstname IN STUDENTS.firstname%type,
    ns_lastname IN STUDENTS.lastname%type,
    ns_status IN STUDENTS.status%type,
    ns_gpa IN STUDENTS.gpa%type,
    ns_email IN STUDENTS.email%type)
IS
BEGIN
    INSERT INTO STUDENTS (sid, firstname, lastname, status, gpa, email)
    VALUES (ns_sid, ns_firstname, ns_lastname, ns_status, ns_gpa, ns_email);
END;
/

Here's the call that generates the table in the first place

create table students (sid char(4) primary key check (sid like 'B%'),
firstname varchar2(15) not null, lastname varchar2(15) not null, status varchar2(10) 
check (status in ('freshman', 'sophomore', 'junior', 'senior', 'graduate')), 
gpa number(3,2) check (gpa between 0 and 4.0), email varchar2(20) unique);

Originally I tried to call the stored procedure with some pre-defined parameters.

BEGIN
    add_student("B100", "Steve", "Stevenson", "senior", 2.31, "Ssteve@edu");
END;
/

This resulted in the error "PLS-00201: identifier 'B100' must be declared"

I am new to PL/SQL and am having trouble fixing this. I tried adding a DECLARE statement as such

DECLARE
    ns_sid STUDENTS.sid%type;
BEGIN
    ns_sid := "B100";
    add_student(ns_sid, "Steve", "Stevenson", "senior", 2.31, "Ssteve@edu");
END;
/

This still results in the error "PLS-00201: identifier 'B100' must be declared" however it says "PL/SQL: Statement ignored" and then says "PLS-00201: identifier 'Steve' must be declared"

Digging further I tried to declare the firstname as a varchar and see if it would work. Spoilers, it didn't.

DECLARE
    ns_sid STUDENTS.sid%type;
    ns_fn varchar(15);
BEGIN
    ns_sid := "B100";
    ns_fn := "Steve";
    add_student(ns_sid, ns_fn, "Stevenson", "senior", 2.31, "Ssteve@edu");
END;
/

This gives three PLS-00201 saying "B100", "Steve" and "Stevenson" must be declared.

I'm hoping someone can shed a light on my misunderstandings. Thanks!

Upvotes: 2

Views: 1613

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

Don't use double quotes, but single ones while dealing with strings in Oracle, e.g.

BEGIN
  add_student('B100', 'Steve', 'Stevenson', 'senior', 2.31, 'Ssteve@edu');
END;
/

Upvotes: 2

Related Questions