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

Posted by Swapna on Stack Overflow See other posts from Stack Overflow or by Swapna
Published on 2010-03-25T15:06:28Z Indexed on 2010/03/25 15:23 UTC
Read the original article Hit count: 489

Filed under:
|
|
|
|
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 ?

© Stack Overflow or respective owner

Related posts about dynamic

Related posts about create