PLS-00308: this construct is not allowed as the origin of an assignment

The PLS-00308: this construct is not allowed as the origin of an assignment error 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 the variable is accessed as a list such as type, cursor, table, and so on, the error would be thrown. To fix this error PLS-00308: this construct is not allowed as the origin of an assignment, either declare the variable as a list or use the variable reference.

Oracle cannot index using a variable reference if it is accessed using an index value that is not allowed. The variable can either be declared as a list of objects or accessed as a variable reference. The index value in Oracle can be used to index the list. In Oracle, the varchar data type is a string, not a sequence of characters. The PLS-00308: this construct is not allowed as the origin of an assignment error will be thrown.



Exception

If an index is used to manipulate the value of a variable, the variable may not allow changes. An error will be thrown. The index of the variable allows access to collections such as sort, cursor, table, and so on. The error will be shown as seen below.

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


Cause

The construct or expression does not designate a value that can be assigned to a variable. For example, the datatype name NUMBER cannot appear on the right hand side of an assignment statement as in X := NUMBER.

This error is caused by an inconsistency between the number and the list, such as assigning a variable to a list object or assigning a list to a variable. The variable reference can be used to access the variable. The index of the list will be used to access the list. When you combine both the variable and the list, you have a conflict resulting in an error.



Problem

The construct or expression can be assigned to a variable. The exception would be thrown if the expression return datatype and the assigned variable data type do not match. If the variable is created with an oracle data type and a value is added to one of the variable’s indexes, an error would be thrown.

declare
    empname varchar(10):='yawin';
begin
    empname(1) := 'k';
    dbms_output.put_line(empname);
end;

Output

declare
 empname varchar(10):='yawin';
begin
    empname(1) := 'k';
    dbms_output.put_line(empname);
end;
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


Solution1

An index should not be used to access the variable. The value can be completely modified. The varchar is not a character list. Varchar is a value that represents a string. As a string, the string value can be modified.

declare
 empname varchar(10):='yawin';
begin
    empname := 'kawin';
    dbms_output.put_line(empname);
end;

Output

kawin
PL/SQL procedure successfully completed.


Solution 2

The variable’s indexing can be changed to the variable’s reference. The value of the variable would be shown if it is referenced. The variable indexing can be converted to access as if it were a variable.

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;

Exception

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
 st student;
begin
    st :=student(1,'yawin');
    dbms_output.put_line(st.id);
end;

Output

1
PL/SQL procedure successfully completed.


Solution 3

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 appropriate. The variable’s declaration should be modified.

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;

Exception

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