CODEWITHSUNDEEP
plsqlplsqldeveloper
Vinod Satpute
Vinod Satpute

Reputation: 51

How to fetch data in nested record in PL/SQL?

I was trying to work on nested record example. Please have a look at declaration section. 

DECLARE
  TYPE r_emp IS RECORD (
   name VARCHAR2(50),
   dept_rec departments%ROWTYPE
  );
  r_emp_row r_emp;

BEGIN
  SELECT emp.first_name||' '||emp.last_name,dept.*
  INTO r_emp_row
  FROM   employees emp,departments dept 
  WHERE  emp.department_id = dept.department_id 
  AND    emp.employee_id = 100;

  DBMS_OUTPUT.PUT_LINE('Name:'||r_emp_row.name);
  DBMS_OUTPUT.PUT_LINE('Department ID:'||r_emp_row.dept_rec.department_id);
  DBMS_OUTPUT.PUT_LINE('Department Name:'||r_emp_row.dept_rec.department_name);

EXCEPTION
  when others then
    null;
END;

I am trying to run above block but getting error as below:

Error report -
ORA-06550: line 10, column 8:
PLS-00597: expression 'R_EMP_ROW' in the INTO list is of wrong type
ORA-06550: line 11, column 3:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 9, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

Help appreciated.

Upvotes: 1

Views: 425

Answers (1)

William Robertson
William Robertson

Reputation: 16001

Your record type r_emp is a composite of one scalar attribute and one record, so you will need to reflect that in the select into. Something like:

select emp.first_name||' '||emp.last_name, dept.*
into   r_emp_row.name, r_emp_row.deptrec.dempno, r_emp_row.deptrec.dname ...

btw unrelated to the problem, when others then null is a famously dangerous construction:

What is bad in "When Others Then Null" in PL/SQL?
http://www.orafaq.com/wiki/WHEN_OTHERS
http://tkyte.blogspot.co.uk/2008/06/when-others-then-null-redux.html

What if there is no employee 100? Fine, it does nothing - perhaps that's OK. What if there is an employee 100 but there is a corrupt block in the index causing the query to fail with ORA-01578: ORACLE data block corrupted, or not, depending on the execution plan? It does nothing and doesn't tell you, and you'll think there's no employee 100.

Upvotes: 3

Related Questions