CODEWITHSUNDEEP
oracle-databaseplsql
shashank
shashank

Reputation: 79

how to take value in composite record type in plsql

ERROR at line 4: ORA-06550: line 4, column 5: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 2, column 1: PL/SQL: Item ignored ORA-06550: line 4, column 1: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 10, column 10: PL/SQL: ORA-00904: : invalid identifier ORA-06550: line 9, column 1: PL/SQL: SQL Statement ignored

declare 
type emp_detp_record is record 
( 
emp emp%rowtype,
dept dept%rowtype
);
emp_dept emp_detp_record;
begin 
select * into 
emp_dept 
from emp,dept
where emp.deptno =dept.deptno 
and empno =7839;
end;
/

Upvotes: 1

Views: 1018

Answers (3)

Frank Schmitt
Frank Schmitt

Reputation: 30775

You can use a CURSOR and anchor the record to the cursor's ROWTYPE:

declare 
  cursor emp_dept_cur is
    select * 
    from emp 
    join dept on emp.deptno = dept.deptno;
  emp_dept_rec emp_dept_cur%rowtype;
begin 
  select * into 
    emp_dept_rec
  from emp 
  join dept on emp.deptno =dept.deptno 
  where empno = 7839;
 -- dbms_output.put_line(emp_dept_rec.deptno); -- raises PLS-00302: component 'deptno' must be declared
end;

Some notes:

  • please don't use the old Oracle syntax for JOINs (multiple tables in the FROM clause). Use ANSI JOINs instead
  • this will cause problems with duplicate column names. In your example, you have two columns named deptno - one from emp and one from dept. This will raise an error as soon as you try to access it (see commented line in my code)

Upvotes: 2

user5683823
user5683823

Reputation:

Two problems with your code... The error at line 4 is caused by the name you chose for your attribute; change emp to x, and dept to y in line 5, and the declaration will work.

Then in the body of your procedure you fetch data from a row set. The fact that the row set is created by joining two tables is irrelevant; first the join is computed, and then you extract rows from it and try to separate the values into emp and dept subrows. That is not possible; when the data comes in from the join, the fact it is a join and not a single table is "water under the bridge." If you really want or need to keep the data from emp and from dept in separate attributes you should bring it in from emp and from dept separately.

Upvotes: 1

hemalp108
hemalp108

Reputation: 1249

I use collections to handle such scenarios. Hope this might help you.

declare 
cursor emp_dept_cur is
select emp.empno,dept.deptno 
from emp,dept
where emp.deptno =dept.deptno 
and empno =7839;
type emp_detp_record is table of emp_dept_cur%rowtype;
emp_dept emp_detp_record;
begin 
open emp_dept_cur;
loop
fetch emp_dept_cur bulk collect into emp_dept;
exit when emp_dept.count=0;
null;
end loop;
close emp_dept_cur;
end;
/

Upvotes: 1

Related Questions