CODEWITHSUNDEEP
plsqloracle11g
MKS
MKS

Reputation: 59

PLS-00103 error when creating object type

I am trying to create and assign variables using following code to create object types in plsql (11g) but facing some errors:

begin
execute immediate 'drop type picu_obj force'; 
execute immediate 'drop type picu_obj_tab force'; 
execute immediate 'create type picu_obj as object(Customer_ID varchar2(32767),Customer_Name varchar2(32767),Server_Name varchar2(32767),Time_stamp varchar2(32767))';
execute immediate 'create type picu_obj_tab is table of picu_obj;';
picu_var picu_obj_tab;
picu_var := picu_obj_tab(picu_obj('101','xyz','pro-ssr-qr','12:13'));
end;

The above code gives following errors:


ERROR at line 6:
ORA-06550: line 6, column 10:
PLS-00103: Encountered the symbol "PICU_OBJ_TAB" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "PICU_OBJ_TAB" to continue.

Please suggest what I am doing wrong here.

Upvotes: 0

Views: 1391

Answers (1)

user330315
user330315

Reputation:

There are two problems with this code:

First: In Oracle 11g you can not use varchar2(32767) the maximum length is 4000 for a varchar there. So even if the code did run, it wouldn't create the types.

Secondly: the PL/SQL code is validated/compiled when you run it. But as you use dynamic SQL to create the types, the PL/SQL compiler can't see those types when it tries to compile the lines:

picu_var picu_obj_tab;
picu_var := picu_obj_tab(picu_obj('101','xyz','pro-ssr-qr','12:13'));

and that's the error you are seeing.

You have to create the types before you run PL/SQL code that uses them.

Upvotes: 1

Related Questions