CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Minhaj Patel
Minhaj Patel

Reputation: 579

PLS-00201: identifier must be declared in Procedure

I have a PL/SQL Procedure code, which runs when it is / , but doesn't runs when it's executed. The error message I get is 

SQL> EXECUTE MAXINUM;
BEGIN MAXINUM; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'MAXINUM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The code which I'm working on is :

DECLARE
    N NUMBER;
    M NUMBER;
    O NUMBER;
    P NUMBER;
    X NUMBER;
PROCEDURE MAXINUM(N IN NUMBER, M IN NUMBER, O IN NUMBER, P IN NUMBER, X OUT NUMBER) IS
    BEGIN
    IF N>M AND N>O AND N>P THEN
        X:=N;
    ELSIF M>N AND M>O AND M>P THEN
        X:=M;
    ELSIF O>N AND O>M AND O>P THEN
        X:=O;
    ELSIF P>N AND P>M AND P>O  THEN
        X:=P;
    END IF;
    END;

BEGIN
    N:=&NUMBER;    
    M:=&NUMBER;
    O:=&NUMBER;
    P:=&NUMBER;
    MAXINUM(N,M,O,P,X);
    DBMS_OUTPUT.PUT_LINE('HIGHEST NUMBER = '||X);
END;
/

When it gave the 'identifier error', I tried dropping this procedure I got the error:

SQL> DROP PROCEDURE MAXINUM;
DROP PROCEDURE MAXINUM
*
ERROR at line 1:
ORA-04043: object MAXINUM does not exist

I have so far read this, this, this solutions and other solutions some what related to this error.

Upvotes: 1

Views: 27212

Answers (1)

APC
APC

Reputation: 146239

You have written an anonymous block with a local procedure MAXINUM(). This procedure can be called within that block but does not exist outside that block. Consequently you cannot call it independently.

If you want to use the procedure elsewhere you need to create it as a first class database object:

create or replace procedure MAXINUM
   (N IN NUMBER, M IN NUMBER, O IN NUMBER, P IN NUMBER, X OUT NUMBER)
is 
BEGIN
    IF N>M AND N>O AND N>P THEN
        X:=N;
    ELSIF M>N AND M>O AND M>P THEN
        X:=M;
    ELSIF O>N AND O>M AND O>P THEN
        X:=O;
    ELSIF P>N AND P>M AND P>O  THEN
        X:=P;
    END IF;
END;
/

Now you can call it in your code, like this:

DECLARE
    N NUMBER;
    M NUMBER;
    O NUMBER;
    P NUMBER;
    X NUMBER;
BEGIN
    N:=&NUMBER;    
    M:=&NUMBER;
    O:=&NUMBER;
    P:=&NUMBER;
    MAXINUM(N,M,O,P,X);
    DBMS_OUTPUT.PUT_LINE('HIGHEST NUMBER = '||X);
END;
/

Points to note:

  1. what happens if a parameter is null?
  2. what happens if two arguments have the same value?
  3. the convention would be to declare this as a function and return the highest value instead of setting an OUT parameter.

Incidentally I assume you're doing this as an exercise, as it is a re-implementation of an existing Oracle built-in function, greatest().

Upvotes: 4

Related Questions