How do i create a table dynamically with dynamic datatype from a PL/SQL procedure
- by Swapna
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 ?
