PLS-00222: no function with name exists in this scope

The PLS-00222: no function with name exists in this scope oracle error occurs occurs when an index is used to change the value of a variable on which indexing is not permitted. If a variable is declared with a datatype and then accessed as a list, such as sort, cursor, table, etc, the error PLS-00222: no function with name exists in this scope will be thrown. Either the variable should be declared as a list or a reference should be used.

If an index value that isn’t permitted is accessed with a variable reference, Oracle won’t be able to index it. The variable may be accessed as a variable reference or declared as a list of objects. In Oracle, the index value can be used to index the list. The varchar data type in Oracle is a string, not a collection of characters. The error PLS-00222: no function with name exists in this scope will be thrown.



Exception

The oracle error will be shown as follows. The variable name that is causing the exception will be shown in the error message. This error is accompanied by the line number at which the error occurred. The variable reference or variable declaration must be modified.

Error report -
ORA-06550: line 4, column 5:
PLS-00308: this construct is not allowed as the origin of an assignment
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
ORA-06550: line 5, column 26:
PLS-00222: no function with name 'ST' exists in this scope
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored


Cause

The variable cannot be indexed if it is declared with a data type and used as a collection. The index can only work on collection items like type, cursor, table, and so on. The variable can either be declared as a list or used as a reference to an object. The variable’s indexing would result in an error.



Problem

This error is caused by a mismatch between a variable and its indexing. Declare a data type for a variable. Use index operations like cursor and loop to access the variable. It is not possible to index the variable. The error PLS-00222: no function with name exists in this scope is going to be thrown.

create or replace type student is object
(
id numeric(5),
name varchar2(100)
);

declare
 st student;
begin
    st(1) :=student(1,'yawin');
    dbms_output.put_line(st(1).id);
end;

Output

Error report -
ORA-06550: line 4, column 5:
PLS-00308: this construct is not allowed as the origin of an assignment
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
ORA-06550: line 5, column 26:
PLS-00222: no function with name 'ST' exists in this scope
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored


Solution 1

The indexing of the variable should be changed to the reference of the variable. If the variable is referenced, the value of the variable will be shown. The indexing of the variable should be converted to access like a variable. The variable reference would be used to access the variable. This error can only be fixed by removing the index.

declare
 st student;
begin
    st :=student(1,'yawin');
    dbms_output.put_line(st.id);
end;

Output

1
PL/SQL procedure successfully completed.


Solution 2

If the variable needs to be indexed, it should be declared as a collection object like sort, cursor, table, row, and so on. In this case, indexing the variable would be sufficient. The variable’s declaration should be modified. The index can be used to get the value of the variable. The index value would be up to the variable’s size.

create or replace type student is object
(
id numeric(5),
name varchar2(100)
);

declare
 st student;
begin
    st(1) :=student(1,'yawin');
    dbms_output.put_line(st(1).id);
end;

Output

Error report -
ORA-06550: line 4, column 5:
PLS-00308: this construct is not allowed as the origin of an assignment
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
ORA-06550: line 5, column 26:
PLS-00222: no function with name 'ST' exists in this scope
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored

Solution

declare
 TYPE studentarr IS TABLE OF student INDEX BY PLS_INTEGER;
 st studentarr;
begin
    st(1) :=student(1,'yawin');
    dbms_output.put_line(st(1).id);
end;

Output

1
PL/SQL procedure successfully completed.



Leave a Reply