Pls-00201: Identifier 'User Input' Must Be Declared

PLS-00201: identifier 'USER INPUT' must be declared

If you're expecting a string, you need to enclose the substitution variable in quotes when you use it:

  V_CHILD  REGIONS.CHILD_NAME%TYPE := '&p_1';

As it is, it's trying to interpret the substituted value as an identifier, i.e. another variable or a column name. So if you entered CHICAGO it would see:

  V_CHILD  REGIONS.CHILD_NAME%TYPE := CHICAGO;

and if you entered "CHICAGO":

  V_CHILD  REGIONS.CHILD_NAME%TYPE := "CHICAGO";

In Oracle using double quotes still indicates an identifier. You could in fact have entered 'CHICAGO', with quotes in your input, and it would have worked - but not ideal to have to remember to do that.

If you set verify on in SQL*Plus or SQL Developer then you can see the before and after of each substitution, which can help identitify things like this.

Error user input PLS-00201: identifier 'ISS' must be declared

If nursing_unit_id is varchar2(10) and you've defined the substitution variable &v_unit as ISS, then you have to enclose the reference to the substitution variable in quotes, to make it a string:

v_unit ADMISSIONS.NURSING_UNIT_ID%TYPE := '&v_unit';

If you run your code in SQL*Plus or SQL Developer with set verify on, you'll see the before and after values for that substitution, which would show it it trying to to do:

v_unit ADMISSIONS.NURSING_UNIT_ID%TYPE := ISS;

instead of what you'd get with it quoted:

v_unit ADMISSIONS.NURSING_UNIT_ID%TYPE := 'ISS';

You will still get other errors; the next one is:

PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

You've defined v_admissions as a row type, which means it's a record. You can't write a whole record out in one go with dbms_output, you have to refer to each field separately, e.g. (using your amended code, where `r_admissions is also implicitly of that same row type):

    DBMS_OUTPUT.PUT_LINE(r_admissions.nursing_unit_id);

Having an into clause in the cursor definition isn't useful; it isn't actually populated, so you can remove that clause and the v_admissions variable declaration. You don't really need the v_unit local variable either, you can use the substitution variable directly; and there are various cursor forms you can use, such as:

SET SERVEROUTPUT ON;
BEGIN 
  FOR r_admissions IN (
    SELECT *
    FROM ADMISSIONS
    WHERE NURSING_UNIT_ID = '&v_unit'
  )
  LOOP  
    DBMS_OUTPUT.PUT_LINE(r_admissions.nursing_unit_id);
    -- and anything else you want to do with that row's data
  END LOOP;
END;
/

PLSQL error PLS-00201: identifier must be declared

"Florida" is a string, so you'll have to enclose substitution variable into single quotes:

v_state users.state%type := '&state';

PLS-00201 identifier must be declared referencing user defined type as SYS

The referenced objects are owned by ANOTHER_SCHEMA. You are attempting to use them in PL/SQL owned by EXAMPLE_SCHEMA. So the most likely explanation is the grants between the two schemas.

In order to use them in code, ANOTHER_SCHEMA must have granted privileges directly to EXAMPLE_SCHEMA, that is by name and not through a role. This is due to the way Oracle's security model works.

You can check the extant grants like this:

select table_name as object_name
       , privilege
       , grantor
from dba_tab_privs
where grantee = 'EXAMPLE_SCHEMA'
and table_owner = 'ANOTHER_SCHEMA'
/

error PLS-00201: identifier kurtwb must be declared

You have entered kurtwbas a value of the substitution variable named &model_id.

SQLPLus (or Oracle-SQL-Developer`) substitutes this variable in this line:

BEGIN
  model_id:=&model_id;
  ....
  ....

with the entered value kurtwb. After the substitution the code looks like this:

BEGIN
  model_id:=kurtwb;
  ....
  ....

since kurtwb is not defined anywhere, you get PLS-00201: identifier 'KURTWB' must be declared error.

---

I guess you want to store kurtwb as a string in model_id variable, in this case you must use apostropher aroud the substitution variables:

BEGIN
  model_id:='&model_id';
  ....
  ....

PLS-00201 - identifier must be declared

When creating the TABLE under B2BOWNER, be sure to prefix the PL/SQL function with the Schema name; i.e. B2BOWNER.F_SSC_Page_Map_Insert.

I did not realize this until the DBAs pointed it out. I could have created the table under my root USER/SCHEMA and the PL/SQL function would have worked fine.

PLS-00201 Identifier must be declared

PLS-00201: identifier 'ART' must be declared

I think the problem may be that when the user inputs "Art" the program takes it in as "ART" and since my table type is "Art" not "ART" then that's why I get the error.

It has nothing to do with the UPPER/LOWER case. The query is parsed as:

SELECT TITLE into output FROM CLASS where TYPE=ART;

If type is varchar type, then you must pass the value as a string, i.e. you must enclose it within single-quotation marks. Else, Oracle interprets it to be an identifier. So, it should be:

SELECT TITLE into output FROM CLASS where TYPE='ART';

For example,

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    o_dept NUMBER;
  3  BEGIN
  4    SELECT DEPTNO INTO o_dept FROM EMP WHERE ENAME=SCOTT;
  5    dbms_output.put_line(o_dept);
  6  END;
  7  /
  SELECT DEPTNO INTO o_dept FROM EMP WHERE ENAME=SCOTT;
                                                 *
ERROR at line 4:
ORA-06550: line 4, column 50:
PL/SQL: ORA-00904: "SCOTT": invalid identifier
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

SQL> DECLARE
  2    o_dept NUMBER;
  3  BEGIN
  4    SELECT DEPTNO INTO o_dept FROM EMP WHERE ENAME='SCOTT';
  5    dbms_output.put_line(o_dept);
  6  END;
  7  /
20

PL/SQL procedure successfully completed.

SQL>

And, it is not a good idea to use reserved keywords as object names. TYPE is a reserved keyword.

Error(66,3): PLS-00201: identifier must be declared

you should be granted to use that procedure with :

SQL> conn of_device/pwd1
SQL> grant execute on p_msg to cc_device
SQL> conn cc_device/pwd0

now you can use it from cc_device...

PLS-00201 – identifier must be declared, passing a collection to procedure

A type declared locally to a procedure and another type declared locally to another procedure with exactly the signature are not the same types and you cannot pass one to the other. You need to create the type externally to the procedures rather than internally.

Also, you cannot use the type in the signature for the package when it is not declared except internally to the procedure which is declared in the body of the package.

create or replace package PACK_DW_TEMP  
as
  TYPE error IS RECORD(
    cod_error       NUMBER,
    descr_error     VARCHAR2(100)
  );

  type l_error is table of error;
  procedure A;
  procedure B (error_list in out l_error);
  
end PACK_DW_TEMP;
/

and

create or replace package body PACK_DW_TEMP
as
  procedure A
  as
    error_list  l_error := l_error(); 
  begin
    error_list.EXTEND(2);
    error_list(1).cod_error   := 1;
    error_list(1).descr_error := 'DESCR1';
    error_list(2).cod_error   := 2;
    error_list(2).descr_error := 'DESCR2';
    
    B(error_list);

    FOR i IN 1 .. error_list.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        error_list(i).cod_error || ': ' || error_list(i).descr_error
      );
    END LOOP;
  end;

  procedure B ( error_list in out l_error )
  as
  begin
    error_list.EXTEND;
    error_list(error_list.COUNT).cod_error   := 99;
    error_list(error_list.COUNT).descr_error := 'DESCR99';
  end;
end;    
/

Then you can call it using:

BEGIN
  PACK_DW_TEMP.A();
END;
/

Which outputs:

1: DESCR1
2: DESCR2
99: DESCR99

db<>fiddle here

---

Related Topics