How do i create a table dynamically with dynamic datatype from a PL/SQL procedure

Question

CREATE OR REPLACE PROCEDURE p_create_dynamic_table IS 
  v_qry_str VARCHAR2 (100);
  v_data_type VARCHAR2 (30); 
BEGIN 
  SELECT data_type || '(' || data_length || ')' 
    INTO v_data_type 
    FROM all_tab_columns  
   WHERE table_name = 'TEST1' AND column_name = 'ZIP'; 

  FOR sql_stmt IN (SELECT * FROM test1 WHERE zip IS NOT NULL)
  LOOP
    IF v_qry_str IS NOT NULL THEN     
      v_qry_str := v_qry_str || ',' || 'zip_' || sql_stmt.zip || ' ' ||v_data_type;     
    ELSE     
      v_qry_str := 'zip_' || sql_stmt.zip || ' ' || v_data_type;  
    END IF;
  END LOOP; 

  IF v_qry_str IS NOT NULL THEN 
    v_qry_str := 'create table test2 ( ' || v_qry_str || ' )';
  END IF;

  EXECUTE IMMEDIATE v_qry_str;
  COMMIT;
END p_create_dynamic_table; 

Is there any better way of doing this ?

Answer 1

Why don't you create a view on the table, which contains only those columns with a zip?

create or replace view Zip_View as
select * from test1
where test1.zip is not null;

That way you don't need to copy the data. Or what are your exact requirements?

Answer 2

If I'm reading this correctly, it appears that you want to create a new table containing one column for each zip code.

I think the answer you came up with is the best possible way to accomplish your stated goals. I would add that you probably want to sort the cursor used for the loop, which will ensure that the columns are always in the same order.

However, your goal is highly suspect. It might be better to take a step back and consider whether creating this table is really the right way to solve your problem. This appears to be a massive de-normalization and will be a nightmare to maintain. Without knowing why you're taking this approach I can't offer a better solution, but, nonetheless, I think there probably is one.