How to insert the result of a query into a record of record-CodePudding

declare
     type a is record(a1 number, a2 varchar2(10));
     type b is record(b1 number, b2 varchar2(10));
     type c is record(a2 a, b2 b);

      ra a;
      rc c; 
 begin  
      with pa as(   select 1,'a'  from dual)   select pa.* into ra from pa;

  dbms_output.put_line(ra.a2); --works

   with pa as( select 1, 'a' from dual)-- expression 'RC' in the INTO list is of wrong type       
    select pa.*,pa.* into rc from pa;

    with pa as( select 1, 'a' from dual) --invalid user.table.column, table.column, or column specification
    select a(pa.*),b(pa.*) into rc from pa;

    with pa as( select 1, 'a' from dual) --invalid user.table.column, table.column, or column specification    
    select c(a(pa.*),b(pa.*)) into rc from pa;
     
      end;

I've the record c which is a record of record. I would like to insert in c the result of a query. In this example I have tried wiht 3 different way. It doesn't work.

Is it possible and how?

code on dbfiddle

CodePudding user response：

If it doesn't work at once, it works step-by-step:

SQL> declare
  2       type a is record(a1 number, a2 varchar2(10));
  3       type b is record(b1 number, b2 varchar2(10));
  4       type c is record(a2 a, b2 b);
  5
  6        ra a;
  7        rb b;
  8        rc c;
  9  begin
 10    with pa as( select 1, 'a' from dual)
 11    select pa.*
 12    into ra
 13    from pa;
 14
 15    with pa as( select 1, 'a' from dual)
 16    select pa.*
 17    into rb
 18    from pa;
 19
 20    rc := c(ra, rb);
 21  end;
 22  /

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

SET SERVEROUTPUT ON
Declare
     type a is record(a1 number, a2 varchar2(10));
     type b is record(b1 number, b2 varchar2(10));
     type c is record(a2 a, b2 b);
      ra a;
      rb b;
      rc c;
Begin
    Select 1, 'a' into ra.a1, ra.a2 from dual;
    DBMS_OUTPUT.PUT_LINE('Record A = ' || To_Char(ra.a1) || ', ' || ra.a2);
    Select 1, 'b' into rb.b1, rb.b2 from dual;
    DBMS_OUTPUT.PUT_LINE('Record B = ' || To_Char(rb.b1) || ', ' || rb.b2);
    rc.a2 := ra;
    rc.b2 := rb;
    DBMS_OUTPUT.PUT_LINE('Record C = ' || To_Char(rc.a2.a1) || ', ' || rc.a2.a2 || ', ' || To_Char(rc.b2.b1) || ', ' || rc.b2.b2);
End;

/*  R e s u l t :
anonymous block completed
Record A = 1, a
Record B = 1, b
Record C = 1, a, 1, b
*/