Forum Stats

  • 3,751,235 Users
  • 2,250,335 Discussions
  • 7,867,351 Comments

Discussions

Nested record in pl/sql

950046
950046 Member Posts: 11
edited Jun 5, 2013 1:28AM in SQL & PL/SQL
I have wanted to use nested records in the below mentioned way

Declare
Type emp_type is record(
ename emp.ename%type,
eno emp.eno%type
);
type emp_whole_type is record (
emp_detail emp_type,
dob date
);
emp_type_1 emp_whole_type;
begin
select ename,eno,dob into emp_type_1 FROM EMP; /* Assume only one row is in emp table*/
end;

What i wanted is to restrict the into clause length because in my original example i have to incorporate lot of columns which leads to lot of errors every time if i change something which means i don't want to use like "emp_type_1.emp_detail.ename, emp_type_1.emp_detail.eno,emp_type_1.dbo".

I have even tried with below syntax. It didn't work out i know it's not object.

SELECT EMP_DETAIL(ENAME,ENO),DOB INTO EMP_TYPE_1 FROM EMP

Is it possible to minimize the into clause. Appreciate your suggestions.
Tagged:

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    shany wrote:
    I have wanted to use nested records in the below mentioned way
    You are a glutton for punishment.
    Nest records add nothing but complications & errors and should be AVOIDED!
  • >
    I have wanted to use nested records in the below mentioned way
    >
    You can't use PL/SQL types in sql; you need to use SQL types.
    >
    What i wanted is to restrict the into clause length because in my original example i have to incorporate lot of columns which leads to lot of errors every time if i change something which means i don't want to use like "emp_type_1.emp_detail.ename, emp_type_1.emp_detail.eno,emp_type_1.dbo".

    Is it possible to minimize the into clause.
    >
    No - you have to create the object instances and to do that you need to reference the columns for each instance type you are creating. So by using objects you are making things MORE complicated rather than less complicated.

    This works for me
    create or replace Type emp_type is object(
    ename varchar2(10),
    eno number(4)
    );
    
    create or replace type emp_whole_type is object (
    emp_detail emp_type,
    dob date
    );
    
    declare
    emp_type_1 emp_whole_type;
    begin
    select emp_whole_type(emp_type(ename, empno), hiredate) into emp_type_1 FROM EMP where rownum = 1; /* Assume only one row is in emp table*/
    end;
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond
    shany wrote:

    Is it possible to minimize the into clause.
    Simplest way is:
    declare
      cursor c is select .. from emp;
      intoBuffer c%RowType;
    begin
      open c;
      fetch c into intoBuffer;
      close c;
    end;
    Of course, bulk fetching should be considered.

    There's also not using an explicit cursor at all and doing it as an implicit cursor, with an implicit "into buffer":
    begin
      for c in(select * from emp) loop
         ..
      end loop;
    end;
    It all comes down to WHAT exactly needs to be done and WHY (the requirement to be addressed). The first example above is one that should be very rare in code. As 99% of the time that structure is abused for what a single insert..select, update..(select), or merge statement should have been doing instead - and doing it faster, and better. The second example should be encountered more (it does implicit bulk fetching) as this should be how data is used to build e-mails, send SOAP envelopes, process data and so on. It is usually less abused, but any DML statements in it is likely to dispute that.
  • 950046
    950046 Member Posts: 11
    We had created object type for subset of table columns. But later on we have to use some other columns apart from the object record definition so i thought to create nested record inside pl/sql for object record and new columns but unfortunately it didn't work . Thanks for your suggestions.
This discussion has been closed.