Créez un nouveau tableau avec les premières lignes comme les noms de colonnes de l'ancien tableau.

Question

Ce problème très intéressant est de créer une procédure stockée pour prendre les paramètres du nom de la table oldTable et du nom de la nouvelle table newTable . Les noms des colonnes de la nouvelle table doivent être les valeurs de la première ligne de l'ancienne table. et le reste des rangs sont satisfaits. Jusqu'à présent, j'ai réussi si je connais le nombre de colonnes comme indiqué dans la requête suivante. Ceci est nécessaire pour importer des données à partir d'un outil mais il n'y a pas d'option pour sélectionner la ligne d'en-tête, les colonnes sont nommées comme champ_1, champ_2 etc. dans cet outil.

--Following is a solution but does not work when number of columns vary :     
SELECT 'CREATE TABLE NEWTABLE AS SELECT ' || REGEXP_REPLACE(FIELD_0, '[^a-zA-Z'']') 
     || ',' || REGEXP_REPLACE(FIELD_1, '[^a-zA-Z'']') 
     || ',' ||REGEXP_REPLACE(FIELD_2, '[^a-zA-Z'']') 

     || ' FROM (SELECT ROWNUM  R,' 
           || 'FIELD_0 ' || REGEXP_REPLACE(FIELD_0, '[^a-zA-Z'']', '') 
           || ', FIELD_1 ' || REGEXP_REPLACE(FIELD_1, '[^a-zA-Z'']', '') 
           || ', FIELD_2 ' || REGEXP_REPLACE(FIELD_2, '[^a-zA-Z'']', '') 

           || ' FROM test) WHERE R <> 1'
      FROM oldTable
     WHERE ROWNUM = 1

Ici, l'ancienne table est structurée comme suit

 CREATE TABLE oldTable 
   (    "FIELD_0" VARCHAR2(30), 
    "FIELD_1" VARCHAR2(30), 
    "FIELD_2" VARCHAR2(30)
   )  ;
insert into oldTable (FIELD_0, FIELD_1, FIELD_2)
values ('a', 'b', 'c');

insert into oldTable (FIELD_0, FIELD_1, FIELD_2)
values ('apple', 'ball', 'cat');

insert into oldTable (FIELD_0, FIELD_1, FIELD_2)
values ('1', '23', '4');

Old Table
            FIELD_0 FIELD_1 FIELD_2
        1   a       b       c
        2   apple   ball    cat
        3   1       23      4

NewTable (First row of old table is Column of new table)
                a       b       c
            1   apple   ball    cat
            2   1       23      4

J'essaie de créer une solution qui fonctionne avec n'importe quel tableau avec n'importe quel nombre de colonnes, la solution devrait être utile à de nombreuses personnes s'il y en a une.

Il pourrait y avoir une solution si nous pouvions faire une boucle à travers les valeurs et les colonnes comme indiqué ci-dessous.

BEGIN
    FOR col IN (select column_name
                  from cols
                 where upper(table_name) = upper('oldTable')) LOOP

     --Some code here
    END LOOP;
  END;

Answer 1

J'ai été confronté à ce problème dans le passé, et la seule façon dont j'ai pu le résoudre, c'est en utilisant DBMS_SQL . La procédure stockée ci-dessous peut être adaptée en fonction de vos besoins. Il y a beaucoup de commentaires, lisez-les attentivement car ils expliquent le fonctionnement de cette procédure. Je suppose que vous êtes capable d'identifier la première ligne qui sera le nom des colonnes dans la nouvelle table :

PROCEDURE create_stg_tab(old_tab_name IN VARCHAR2, new_tab_name IN VARCHAR2)
IS
v_ct             number default 0;
v_col            varchar2(1000) default '';
l_theCursor      integer default dbms_sql.open_cursor; 
l_colCnt         number; 
l_descTbl        dbms_sql.desc_tab; 
l_columnValue    varchar2(4000); 
l_status         integer; 
v_dest_cols      varchar2(1000) default '';
v_sql            varchar2(1000) default '';
v_col_insert     varchar2(32000) default '';
v_sql_insert     varchar2(32000) default '';

BEGIN

--get the number of columns of the source table
select count(*) into v_ct from all_tab_cols where upper(table_name) = old_tab_name AND OWNER = <SCHEMA NAME>;

--build your dynamic source query
if v_ct > 0 then
    for i in (select column_name from all_tab_cols where table_name = old_tab_name AND OWNER = <SCHEMA NAME>)
    loop
        v_col := ltrim((v_col||','||i.column_name),',');
    end loop;

    --Get dynamic select all columns from old table
    v_sql := 'select '||v_col||' from '||old_tab_name;
    dbms_sql.parse(l_theCursor,v_sql,dbms_sql.native ); 
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl);

    for i in 1 .. l_colCnt loop 
        dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000); 
    end loop; 

    --execute cursor
    l_status := dbms_sql.execute(l_theCursor); 

    --loop through the rows 
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop 
        --loop through the columns
        for i in 1 .. l_colCnt loop 

            dbms_sql.column_value( l_theCursor, i, l_columnValue ); 
            IF l_columnValue IS NOT NULL THEN
                --REGEXP_REPLACE start from inner most (You can customize/ignore this): 
                --1)if value is just a number, replace it with the source column name
                --2)if value starts with anything except letters (e.g. start with number but has letters), remove the leading non-letters
                --3)replace all non leading characters (except numbers and letters) with '_'
                --Ex.1289789bbB#4B$5 => bbB_4B5, 222 => source_column name
                v_dest_cols := v_dest_cols || (l_descTbl(i).col_name||' as '||substr(REGEXP_REPLACE(REGEXP_replace(REGEXP_REPLACE(l_columnValue,'^\d+$', l_descTbl(i).col_name, 1),'^[^a-z,A-Z]+', null, 1),'[^a-z,A-Z,0-9]', '_'),0,30))||',';
                v_col_insert := ltrim((v_col_insert||','||l_descTbl(i).col_name),',');
            END IF;
        end loop; 

        --remove last ','
        v_dest_cols := rtrim(v_dest_cols,',');
        --build create new empty table statement
        v_sql := 'create table '||new_tab_name||' as select '||v_dest_cols||' from '||old_tab_name||' where 1=2';
        --build insert into the new table statement
        v_sql_insert := 'insert into '||new_tab_name||' select '||v_col_insert||' from '||old_tab_name||' WHERE <condition to fetch rows from 2nd onwards>';
    end loop; 

    --execute
    execute immediate v_sql;
    execute immediate v_sql_insert;
end if;

exception
    --your exception
END create_stg_tab;