CODEWITHSUNDEEP
sqloracle-databaseplsql
Jericho
Jericho

Reputation: 10953

Oracle invalid use of type name or subtype name

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: : invalid identifier
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 22:
PLS-00330: invalid use of type name or subtype name
ORA-06550: line 11, column 1:
PL/SQL: Statement ignored
ORA-06550: line 12, column 22:
PLS-00330: invalid use of type name or subtype name
ORA-06550: line 12, column 1:
PL/SQL: Statement ignored
ORA-06550: line 13, column 22:
PLS-00330: invalid use of type name or subtype name
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored

Upvotes: 4

Views: 15093

Answers (1)

Donato Szilagyi
Donato Szilagyi

Reputation: 4369

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';

Upvotes: 9

Related Questions