CODEWITHSUNDEEP
oracle-databaseplsql
andrew17
andrew17

Reputation: 925

What does error 'identifier must be declared' means in PL/SQL?

The task is to write PL/SQL block for entering new employee throw the Oracle database user's dialog to insert data into the table. I dont really understand what I am missing here:

SET SERVEROUTPUT ON
ACCEPT empno PROMPT 'Enter empno: '
ACCEPT ename PROMPT 'Enter ename: '
ACCEPT job PROMPT 'Enter job: '
ACCEPT mgr PROMPT 'Enter mgr: '
ACCEPT sal PROMPT 'Enter sal: '
ACCEPT deptno PROMPT 'Enter deptno: '
DECLARE
empn NUMBER := &empno;
ena VARCHAR(255) := &ename;
ejob VARCHAR(255) := &job;
emgr NUMBER := &mgr;
ehire DATE := Sysdate;
esal NUMBER := &sal;
ecomm NUMBER := null;
edeptno NUMBER := &deptno;
BEGIN
    INSERT INTO EMP(EMPNO, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (empn, ena, ejob, emgr, ehire, esal, ecomm, edeptno);
END;

Error report: Error report - ORA-06550: line 3, column 21: PLS-00201: identifier 'ANDREW' must be declared ORA-06550: line 3, column 5: PL/SQL: Item ignored ORA-06550: line 4, column 22: PLS-00201: identifier 'MANAGER' must be declared ORA-06550: line 4, column 6: PL/SQL: Item ignored ORA-06550: line 11, column 17: PL/SQL: ORA-00913: too many values ORA-06550: line 11, column 5: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Could you explain, what are the mistakes in my code?

I tried the following:

SET SERVEROUTPUT ON
ACCEPT empno PROMPT 'Enter empno: '
ACCEPT ename PROMPT 'Enter ename: '
ACCEPT job PROMPT 'Enter job: '
ACCEPT mgr PROMPT 'Enter mgr: '
ACCEPT sal PROMPT 'Tneter sal: '
ACCEPT deptno PROMPT 'Enter deptno: '
DECLARE
empn NUMBER := '&empno';
ena VARCHAR(255) := '&ename';
ejob VARCHAR(255) := '&job';
emgr NUMBER := '&mgr';
ehire DATE := Sysdate;
esal NUMBER := '&sal';
ecomm NUMBER := null;
edeptno NUMBER := '&deptno;
BEGIN
    INSERT INTO EMP(EMPNO, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (empn, ena, ejob, emgr, ehire, esal, ecomm, edeptno);
END;

Error:

Error report -
ORA-06550: line 9, column 19:
PLS-00103: Encountered the symbol "30;
BEGIN
    INSERT INTO EMP(EMPNO, JOB, MGR, HIREDATE, SAL, CO" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   continue avg count current exists max min prior sql stddev
   sum variance execute forall merge time timestamp interval
   date <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
   <an alternatively-quoted string
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

Upvotes: 0

Views: 1555

Answers (1)

Thomas Kirchhoff
Thomas Kirchhoff

Reputation: 988

The substitution variables are replaced using your input. For example, becomes:

ena VARCHAR(255) := &ename;

ena VARCHAR(255) := ANDREW;

And here is the error. Single quotes are missing.

So correct would be:

ena VARCHAR(255) := '&ename';

You should also specify the data type and the format mask. For example:

ACCEPT esal NUMBER FORMAT '999.99'
ACCEPT hired DATE FORMAT 'dd/mm/yyyy'

Upvotes: 1

Related Questions