Create a new table with the first rows as column names from the old table
This very interesting problem is creating a stored procedure to accept the parameters of the table name oldTable and the new table name newTable. The column names newTable must be the values of the first row of oldTable , and the rest of the rows are content. So far, I have success if I know the number of columns designed in the following query. This is needed to import data from the tool, but there is no option to select the header row, the columns are named field_1, field_2, etc. In this tool.
--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
Here oldTable is structured like this
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
I am trying to create a solution that works on any table with any number of columns, the solution should be useful to many people if any.
There might be a solution if we could iterate over values and columns as below. The only problem is getting the values of the first row to construct a dynamic query.
BEGIN
FOR col IN (select column_name
from cols
where upper(table_name) = upper('oldTable')) LOOP
--Some code here
END LOOP;
END;
source to share
I've run into this problem in the past and the only way I've been able to solve it is by using DBMS_SQL . The stored procedure below can be adjusted according to your needs. There are many comments, please read them carefully as they explain how this procedure works. I am assuming that you can define the first row to be the column names in the new 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;
source to share