CODEWITHSUNDEEP
sqloracle-database
Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Dynamically create a table and then insert the data dynamically

I want to create a procedure to create a table dynamically with user inputted no of columns from 1 to n. I have used below script to create the table dynamically -

CREATE OR REPLACE PROCEDURE TABLES(COLS IN NUMBER)
AS
TABLE_STMT VARCHAR2(100);
TABLE_COLS VARCHAR2(100);
BEGIN
     TABLE_STMT := 'CREATE TABLE TABLS(COL';
     FOR I IN 1..COLS LOOP
         IF I = COLS THEN
             TABLE_STMT := TABLE_STMT || I || ' VARCHAR2(50)) ';
         ELSE
             TABLE_STMT := TABLE_STMT || I || ' VARCHAR2(50), COL';
         END IF;
     END LOOP;
     EXECUTE IMMEDIATE TABLE_STMT;
     DBMS_OUTPUT.PUT_LINE(TABLE_STMT);
END;
/

Demo.

I have another table as -

VAL
A
B
C
D
E
F
G
H

Whose rows needs to be split among the columns created in above table. If user input 2 columns, then output should be - 

col1  |  col2
A     |  B
C     |  D
E     |  F
G     |  H

If user input 3 columns, the output should be -

col1  |  col2  |  col3
A     |  B     |  C
D     |  E     |  F
G     |  H

I have successfully created the table but stucked to insert the data in it. Any help is appreciated.

Upvotes: 1

Views: 74

Answers (2)

MT0
MT0

Reputation: 167774

Use a dynamic pivot to convert the rows to columns:

CREATE PROCEDURE insert_data(
  i_table_name IN VARCHAR2
)
IS
  p_sql CLOB;
BEGIN
  SELECT 'INSERT INTO "' || i_table_name || '"
SELECT ' || LISTAGG( '"' || column_name || '"', ',' )
              WITHIN GROUP ( ORDER BY Column_id ) || '
FROM   (
  SELECT val,
         MOD( ROWNUM - 1, ' || COUNT(*) || ' ) + 1 AS col,
         FLOOR( ( ROWNUM - 1 ) / ' || COUNT(*) || ' ) + 1 AS rn
  FROM   data
)
PIVOT ( MAX( val ) FOR col IN ( '
         || LISTAGG( Column_ID || ' AS "' || column_name || '"', ',' )
              WITHIN GROUP ( ORDER BY Column_id )
         || '))'
  INTO   p_sql
  FROM   USER_TAB_COLUMNS
  WHERE  table_name = i_table_name;

  EXECUTE IMMEDIATE p_sql;
  DBMS_OUTPUT.PUT_LINE( p_sql );
END;
/

then

DECLARE
  cols INTEGER := 3;
BEGIN
  TABLES( cols );
  insert_data( 'TABLS' );
END;
/

outputs:

CREATE TABLE TABLS(COL1 VARCHAR2(50), COL2 VARCHAR2(50), COL3 VARCHAR2(50)) 
INSERT INTO "TABLS"
SELECT "COL1","COL2","COL3"
FROM   (
  SELECT val,
         MOD( ROWNUM - 1, 3 ) + 1 AS col,
         FLOOR( ( ROWNUM - 1 ) / 3 ) + 1 AS rn
  FROM   data
)
PIVOT ( MAX( val ) FOR col IN ( 1 AS "COL1",2 AS "COL2",3 AS "COL3"))

and

SELECT * FROM tabls;

outputs:

COL1 | COL2 | COL3
:--- | :--- | :---
A    | B    | C   
D    | E    | F   
G    | H    | null

db<>fiddle here

Upvotes: 2

Thomas Strub
Thomas Strub

Reputation: 1285

If you don't like a solution with pivot you can generate code with analytical function lead: 

with la as (select chr(level+64)c from dual connect by level <= 8)
, pv as (select c
               ,lead(c) over (partition by 1 order by rownum) c2
               ,lead(c,2)over (partition by 1 order by rownum) c3
               , mod(rownum,3)r 
            from la)
select c,c2,c3  from pv 
where r = 1

Upvotes: 0

Related Questions