0

I want to understand how to create the record type dynamically based on the table name received as input to the procedure.

Ex:

PROCEDURE xxtest(p_table_name IN VARCHAR2)
IS

   TYPE t_test_type IS TABLE OF p_table_name%ROWTYPE;    
   v_test_type t_test_type;

BEGIN    
   NULL;    
END;
Sushil
  • 1
  • 1
  • Can't be done. Record types have fixed projections. What is the actual problem you're trying to solve? That is, why requirement has lead you to think you need to dynamically create record type variables? – APC Nov 26 '18 at 12:31
  • this was asked to me during an interview and was unable to get the solution. So was curious to know how this can be achieved. – Sushil Nov 26 '18 at 14:54
  • People do ask strange questions in interviews. – APC Nov 26 '18 at 15:02
  • Maybe it was one of those "gotcha" questions... the correct answer is "what is the problem you're trying to solve?" (i.e. perhaps their contrived problem can be solved using SQL without needing a PL/SQL type) – Jeffrey Kemp Nov 27 '18 at 02:45

1 Answers1

0

If i was at your place i would try to do simething like that.

create table test_table (f1 number, f2 number);

insert into test_table (f1,f2) values (0,1);
insert into test_table (f1,f2) values (2,3);

select * from test_table

declare
    PROCEDURE xxtest(p_table_name IN VARCHAR2) IS
   vSql varchar2(4000);
   BEGIN    
        vSql := 'declare ';
      vSql := vSql || 'TYPE t_test_type IS TABLE OF '||p_table_name||'%ROWTYPE; ';
      vSql := vSql || 'v_test_type t_test_type; ';
      vSql := vSql || 'begin ';
      vSql := vSql || 'select  a1.f1, a1.f2 ';
      vSql := vSql || 'bulk collect into v_test_type ';            
      vSql := vSql || 'from '||p_table_name||' a1; ';            
      vSql := vSql || 'for i in v_test_type.first..v_test_type.last ';   
      vSql := vSql || 'loop ';   
      vSql := vSql || 'dbms_output.put_line(v_test_type(i).f1||'' ''||v_test_type(i).f1); ';   
      vSql := vSql || 'end loop; ';   
      vSql := vSql || 'end; ';   
      dbms_output.put_line(vSQL);
      execute immediate vSQL;
   END;
begin
    xxtest('test_table');
end;

But actually it took almost hour for me and it's real bad question for interview...
May be they don't want to work with you?:)

Дмитрий
  • 111
  • 12