Issue in dynamic table creation

Question

i am trying to create the tables dynamically based on the information below..it is not working properly as you can see in the output below i am not able to get rid of extra ',' at the end and not even close the parenthesis.. please advise.

drop table t1;
/
create table t1 (table_name varchar2(10),COLUMN_NAME varchar2(10),DATATYPE varchar2(10),COLUMN_WIDTH NUMBER,is_null varchar2(1));
/
insert into t1 values ('TEST','FNAME','VARCHAR2',10,'Y');
insert into t1 values ('TEST','LNAME','VARCHAR2',10,'N');
commit;

code

create or replace PROCEDURE P1(
    P_TABLE_NM IN VARCHAR2 )
IS
  LSQL VARCHAR2(1000);
  LSQL2 VARCHAR2(100);
  CURSOR C1
  IS
    SELECT * FROM T1 WHERE TABLE_NAME = P_TABLE_NM ;
    RC1 C1%ROWTYPE;
BEGIN
  lsql := 'create table '||P_TABLE_NM||'(';
  OPEN C1;
  LOOP
   FETCH C1 INTO RC1;
   EXIT WHEN C1%NOTFOUND;
    LSQL := lsql||RC1.COLUMN_NAME||' '||RC1.DATATYPE||'('||RC1.COLUMN_WIDTH||') ';
     BEGIN
       IF (RC1.IS_NULL='Y') THEN
          BEGIN
            lsql := lsql || 'NOT NULL';
          END;
        END IF;
         lsql := lsql || ',' || CHR(10);      
     END;
     END LOOP;
      DBMS_OUTPUT.PUT_LINE(LSQL);
  CLOSE C1;
END;

output

create table TEST(FNAME VARCHAR2(10) NOT NULL,LNAME VARCHAR2(10) ,

Unrelated, but: the `/` after a "plain" SQL statement (no PL/SQL) that ends with `;` is useless. See here: https://stackoverflow.com/a/10207695/330315 — , Feb 22 '18 at 11:57

William Robertson · Accepted Answer · 2018-02-22T15:15:45.007

0

The simplest fix would be to strip the trailing comma at the end:

dbms_output.put_line(rtrim(lsql,',') || ' )');

It seems like the procedure could be simplified to:

create or replace procedure p1(p_table_nm in varchar2)
is
    l_sql long := 'create table ' || p_table_nm || chr(10);
    l_colsep varchar2(2) := '( ';  -- changes to comma after first item
begin
    for rc1 in (
        select * from t1
        where  table_name = p_table_nm
    )
    loop
        l_sql := l_sql || l_colsep || rc1.column_name || ' ' || rc1.datatype ||
            case
                when rc1.column_width is not null then '(' || rc1.column_width || ')'
            end;

        if rc1.is_null = 'N' then  -- Changed from 'Y' - check requirement
            l_sql := l_sql || ' NOT NULL';
        end if;

        l_sql := l_sql || chr(10);
        l_colsep := ', ';
    end loop;

    dbms_output.put_line(rtrim(l_sql,chr(10)) || ' )');
end;

This gives output like this:

create table TEST
( FNAME VARCHAR2(10) 
, LNAME VARCHAR2(10) NOT NULL
, STARTDATE DATE NOT NULL )

Are you planning to handle default values, generated identifiers, virtual columns, or any specified column ordering?

Also are you sure that rc1.is_null = 'Y' means the column is mandatory? It looks like the opposite.

edited Feb 22 '18 at 15:15

answered Feb 22 '18 at 10:22

William Robertson

15,273
4
38
44

Thanks William.. i have provided the data as below drop table t1; / create table t1 (table_name varchar2(10),COLUMN_NAME varchar2(10),DATATYPE varchar2(10),COLUMN_WIDTH NUMBER,is_null varchar2(1)); / insert into t1 values ('TEST','FNAME','VARCHAR2',10,'Y'); insert into t1 values ('TEST','LNAME','VARCHAR2',10,'N'); commit; – kashi Feb 22 '18 at 10:39
What goes in `column_width` for `date`, `number` or `number(10,2)`? – William Robertson Feb 22 '18 at 11:06
Updated code - my `rtrim` needed to handle linefeeds. I've added a couple of questions about this design. – William Robertson Feb 22 '18 at 12:33
@William..as of now default values, generated identifiers, virtual columns, and ordering is not handled as it would be a simple ddl only..but yes i want to handle composite primary keys on it ..there would be another column in the T1 as IS_PK if it is 'Y' then include that column as PK like that there could be multiple PK.. i will try this and get back if stuck ... – kashi Feb 22 '18 at 16:53

Kaushik Nayak · Answer 2 · 2018-02-22T11:01:59.263

0

To fix your code, you may change lsql := lsql || ',' || CHR(10) to lsql := lsql || ',' and add an RTRIM as below after END LOOP to remove additional comma and include right parentheses

lsql := RTRIM(lsql, ',' )|| ' )';

A better option is to use a single SQL statement using LISTAGG like this.

SELECT  'CREATE TABLE '
         || TABLE_NAME
         || '('
         || CHR (10)
         || LISTAGG (
                  COLUMN_NAME
               || '  '
               || DATATYPE
               || CASE
                     WHEN COLUMN_WIDTH IS NOT NULL
                     THEN
                        '(' || COLUMN_WIDTH || ')'
                  END
               || CASE WHEN IS_NULL = 'Y' THEN ' NOT NULL' END,
               ',' || CHR (10))
            WITHIN GROUP (ORDER BY COLUMN_NAME)
         || CHR (10)
         || ')'
            AS create_table
    FROM t1
   WHERE table_name = 'TEST'
GROUP BY TABLE_NAME;

edited Feb 22 '18 at 11:01

answered Feb 22 '18 at 10:45

Kaushik Nayak

30,772
5
32
45

@Kaushik..Thanks the approach looks good ... to add the date column as suggested by Wiiliam which i didnt think of i have modified this ..can you check if it looks good.. || DECODE(DATATYPE , 'DATE',NULL,'(') || COLUMN_WIDTH || DECODE (DATATYPE ,'DATE',NULL,')') – kashi Feb 22 '18 at 11:01
@kashi : That's not required. For `DATE` column, you just populate `COLUMN_WIDTH` as NULL in table t1, it will be taken care of by this query. – Kaushik Nayak Feb 22 '18 at 11:42
@Kaushik..thanks it worked .. i will come back with some additional questions or additions on this logic .. as of now it is perfectly fine.. thanks again !! – kashi Feb 22 '18 at 12:09

Issue in dynamic table creation

2 Answers2

Linked