Home » SQL & PL/SQL » SQL & PL/SQL » RE:Nested Record
RE:Nested Record [message #157089] Tue, 31 January 2006 05:04 Go to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

declare
type emp_1_rec is record(v_name varchar2(30));
type emp_2_rec is record(name emp_1_rec);
name_tab emp_2_rec;
cursor c1 is select ename from test1;
begin
open c1;
loop
fetch c1 into name_tab;
exit when c1%notfound;
dbms_output.put_line(name_tab.name.v_name);
end loop;
end;

error :ORA-06550: line 0, column 0:
PLS-00801: internal error [0]

but if run the following code 

SQL> declare
  2  type emp_1_rec is record(v_name varchar2(30));
  3  type emp_2_rec is record(name emp_1_rec);
  4  name_tab emp_2_rec;
  5  --cursor c1 is select ename from test1;
  6  begin
  7  name_tab.name.v_name:='scott' ;
  8  dbms_output.put_line(name_tab.name.v_name);
  9  end;
 10  /
scott

PL/SQL procedure successfully completed.
 
plz suggest if i m missing out something.
regards,
Re: RE:Nested Record [message #157141 is a reply to message #157089] Tue, 31 January 2006 08:52 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You are trying to assing a VARCHAR2 column to a locally declared type. I could easily reproduce it.
SQL> declare
  2  type emp_1_rec is record(v_name varchar2(30));
  3  type emp_2_rec is record(name emp_1_rec);
  4  name_tab emp_2_rec;
  5  cursor c1 is select last_name from employees;
  6  begin
  7  open c1;
  8  loop
  9  fetch c1 into name_tab;
 10  exit when c1%notfound;
 11  dbms_output.put_line(name_tab.name.v_name);
 12  end loop;
 13  end;
 14  /
declare
*
ERROR at line 1:
ORA-06550: line 0, column 0:
PLS-00801: internal error [pdticv:CHR/AFC->]


But if you explicitly store it in the VARCHAR2 member of your type, all works well. Pay attention to line 9.

SQL> declare
  2  type emp_1_rec is record(v_name varchar2(30));
  3  type emp_2_rec is record(name emp_1_rec);
  4  name_tab emp_2_rec;
  5  cursor c1 is select last_name from employees;
  6  begin
  7  open c1;
  8  loop
  9  fetch c1 into name_tab.name;
 10  exit when c1%notfound;
 11  dbms_output.put_line(name_tab.name.v_name);
 12  end loop;
 13  end;
 14  /
King
Kochhar
De Haan
<<...snip...>>
Higgins
Gietz

PL/SQL procedure successfully completed.


Lesson learnt: make sure both sides of an assignment are of the same type Wink.
MHE
Re: RE:Nested Record [message #157195 is a reply to message #157089] Tue, 31 January 2006 23:30 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

point noted.thanks for your reply Maaher.

regards,
Previous Topic: sql query
Next Topic: A weird situation when ran a query as standalone and in a procedure
Goto Forum:
  


Current Time: Tue Jun 03 14:14:56 CDT 2025