1

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.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Vinod Satpute
  • 51
  • 4
  • What is a purpose of this construction? Maybe you can simplify this using `FOR LOOP Statement` `for rec in (select * from xxx) loop rec.column_name end loop; ` – Arkadiusz Łukasiewicz May 10 '17 at 12:00
  • Yes I know about FOR LOOP statement and also its more preferred over nested records. This query was raised during training session. – Vinod Satpute May 12 '17 at 06:21

1 Answers1

3

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.

Community
  • 1
  • 1
William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • "when others then null is a famously dangerous construction:"Yes I agree with you. However the point is not related to exception section, rather the problem is related to filling nested record and how to do it? – Vinod Satpute May 12 '17 at 06:19
  • Anyway your suggestion helped! – Vinod Satpute May 12 '17 at 06:28
  • My answer starts with how to do the `select into`. I just added a comment about the exception hider starting 'btw...' but feel free to skip that and focus on the first part. – William Robertson May 12 '17 at 09:49