Oracle invalid use of type name or subtype name

Question

What is wrong with the this block am using? please help to solve this. Only one row will be selected based on the where clause. Table clazzes ``` Name Type Nullable Default Comments ---- ------------- -------- ------- -------- ID NUMBER(10) NAME VARCHAR2(100) Y ``` PL/SQL Block : ``` declare type clazzes_row_type is record (clazz_rownum number, clazz_id clazzes.id%type, clazz_name clazzes.name%type); begin clazzes_row_type.clazz_rownum :=111; select id,name into clazzes_row_type.clazz_id,clazzes_row_type.clazz_name from clazzes where name ='leo1'; dbms_output.put_line(clazzes_row_type.clazz_id); dbms_output.put_line(clazzes_row_type.clazz_rownum); dbms_output.put_line(clazzes_row_type.clazz_name); end; ``` Exception : ``` ORA-06550: line 8, column 1: PLS-00330: invalid use of type name or subtype name ORA-06550: line 8, column 1: PL/SQL: Statement ignored ORA-06550: line 9, column 21: PLS-00330: invalid use of type name or subtype name ORA-06550: line 9, column 75: PL/SQL: ORA-00904:…

Accepted Answer

You should declare a variable with the specified type. And instantiate your variable with a constructor:

```
declare
type clazzes_row_type is record
(clazz_rownum number,
 clazz_id clazzes.id%type,
 clazz_name clazzes.name%type);

myvariable clazzes_row_type;

begin
  myvariable := clazzes_row_type(111, null, null);
```

or with your select:

```
begin
  select 111,id,name into myvariable from clazzes where name ='leo1';
```

Oracle invalid use of type name or subtype name

Solution

Problem