PLS-00320: the declaration of the type of this expression is incomplete or malformed

The PLS-00320: the declaration of the type of this expression is incomplete or malformed error occurs when a variable declaration is missing, incomplete, malformed, or incorrect, or when it throws an error in oracle PL/SQL declaration. When a variable is declared using database objects such as sort, cursor, table, and so on, an error occurs. A variable’s declaration is incomplete, or there is a bug in the declaration. The error PLS-00320: the declaration of the type of this expression is incomplete or malformed will be thrown when you call the variable reference in the PL/SQL language.

The error PLS-00320: the declaration of the type of this expression is incomplete or malformed is caused by some issue in the variable declaration. The variable cannot be used if it is called in PL/SQL code since it is not declared. The variable declaration error must be corrected to use the variable in the oracle PL/SQL code. The declaration was skipped, malformed or wrongly declared, or that an error happened when declaring in Oracle PL/SQL.



Exception

The error occurs on the line where the variable’s reference is called. However, the variable’s type declaration is either incomplete or incorrect. The exception’s stack trace will be shown as follows. The error will be in the variable declaration or the usage of the variable in the PL/SQL code.

Error report -
ORA-06550: line 2, column 5:
PLS-00201: identifier 'STUDENT' must be declared
ORA-06550: line 2, column 5:
PL/SQL: Item ignored
ORA-06550: line 4, column 5:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
ORA-06550: line 5, column 26:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored


Cause

If the variable declaration of type, cursor, table etc throws errors due to incomplete statement or incorrect or malformed, the error will be thrown at which those variables are called in the PL/SQL code. If the variable declaration is erroneous, the variable cannot be executed. If the variable declaration error is corrected, the error PLS-00320: the declaration of the type of this expression is incomplete or malformed will be resolved.



Solution 1

If the variable is declared with a data type, double-check that the data type is defined in the database. The declaration error would be thrown if you call a type or table that does not exist in the database. This PL/SQL error is triggered by the variable’s reference in the code. This error can be resolved by correcting the declaration error.

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

Exception

Error report -
ORA-06550: line 2, column 5:
PLS-00201: identifier 'STUDENT' must be declared
ORA-06550: line 2, column 5:
PL/SQL: Item ignored
ORA-06550: line 4, column 5:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
ORA-06550: line 5, column 26:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored

Solution

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

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 you try to access a variable that has been wrongly declared as an array of objects, an error would be thrown. Otherwise, an error would be thrown if the array declaration is not correct and complete. In Oracle, the variable should be declared as a list in the declare section of the PL/SQL code.

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

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

Exception

Error report -
ORA-06550: line 3, column 5:
PLS-00201: identifier 'STUDENTARR' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Item ignored
ORA-06550: line 5, column 5:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored
ORA-06550: line 6, column 25:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored

Solution

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

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.


Solution 3

The cursor cannot be used in the code if the cursor statement is incomplete, incorrect, or malformed. The exception would be thrown if the cursor is used in the PL/SQL language. Before calling the cursor reference, the error in the cursor declaration should be corrected.

create table employee (
empid number(4),
empname varchar2(50)
);

declare 
   CURSOR employeecursor IS
      select empid, empname, empsalary from employee;
   employeeRow employeecursor%ROWTYPE;
begin
  open employeecursor;
  FETCH employeecursor INTO employeeRow;
  LOOP
     EXIT WHEN employeecursor%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(employeeRow.empid);
     FETCH employeecursor INTO employeeRow;
  END LOOP;
  CLOSE employeecursor;
end;

Exception

Error report -
ORA-06550: line 3, column 30:
PL/SQL: ORA-00904: "EMPSALARY": invalid identifier
ORA-06550: line 3, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 11:
PLS-00341: declaration of cursor 'EMPLOYEECURSOR' is incomplete or malformed
ORA-06550: line 4, column 16:
PL/SQL: Item ignored

In the cursor declaration a select query is used. The employee table has only two columns. There are three columns in the select query. The column empsalary does not exist in the employee table. If you delete the empsalary column from the cursor select statement, the error will be resolved.

Solution

declare 
   CURSOR employeecursor IS
      select empid, empname from employee;
   employeeRow employeecursor%ROWTYPE;
begin
  open employeecursor;
  FETCH employeecursor INTO employeeRow;
  LOOP
     EXIT WHEN employeecursor%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(employeeRow.empid);
     FETCH employeecursor INTO employeeRow;
  END LOOP;
  CLOSE employeecursor;
end;

Output

PL/SQL procedure successfully completed.



Leave a Reply