#1 26 Jan 2011 13:40

Rapparee
Member
Registered: 26 Jan 2011
Posts: 2

How to avoid hard-coding very large numbers of column names

Hi All,

Question:

How to avoid hard-coding very large numbers of column names using %ROWTYPE or otherwise.

Setting:

There's two interfacing tables A and B both wit hundreds of columns.
A and B are identical except A has a handful additional meta-data columns.
Now I am trying to transfer rows from A to B and I dont want to write statements explicitly mapping endless lists of columns.

What DIDNT work:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Maybe this failed attempt will clarify things a bit
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
drop table xx_rjv_tst_A
;

drop table xx_rjv_tst_B
;

create table xx_rjv_tst_A
(
STATUS        varchar2(100),
ID             number,
NAME        varchar2(100)
);

create table xx_rjv_tst_B
(
ID             number,
NAME        varchar2(100)
);

insert into xx_rjv_tst_A values ('N',101,'Jan');
insert into xx_rjv_tst_A values ('N',102,'Piet');
insert into xx_rjv_tst_A values ('N',103,'Klaas');
insert into xx_rjv_tst_A values ('N',104,'Henk');
insert into xx_rjv_tst_A values ('N',105,'Bert');

create or replace procedure xx_rjv_pomp
is

  r_type xx_rjv_tst_B%ROWTYPE;

cursor c_tab
is
select r_type
from xx_rjv_tst_A
;

begin
  for r_tab in c_tab
  loop
    insert into xx_rjv_tst_B  values (r_tab);
  end loop;
end xx_rjv_pomp;
/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Offline

#2 16 Dec 2011 05:08

Opus
Member
Registered: 15 Dec 2011
Posts: 17

Re: How to avoid hard-coding very large numbers of column names

Some thoughts:

Such a large number of columns might suggest a design problem with the tables in question.  I often see programmers trying to treat a relational database as if it was a stack of interlocking spreadsheets.  This is not the best way to go about designing tables.

I don't think there is a way to do what you want, but it seems to me that you would have to "code up" those hundreds of column names just once, and you could use a select from user_tab_cols to gen the list.

As Tom Kyte says, "Ultimately, SQL has to know how many columns there are."

Cheers,
Opus

Offline

#3 16 Dec 2011 08:38

Opus
Member
Registered: 15 Dec 2011
Posts: 17

Re: How to avoid hard-coding very large numbers of column names

I've looked at this from several angles using various advanced features of Oracle, but my preference of using SQL doesn't seem to offer any hope here.  It can, however, be done quite easily using PL/SQL:

drop table a cascade constraints;

create table a
 (COL1  NUMBER
 ,COL2  NUMBER
 ,COL3  NUMBER
 ,COL4  NUMBER
 ,COL5  NUMBER);

insert into a values (1,2,3,4,5);
insert into a values (2,3,4,5,6);
insert into a values (3,4,5,6,7);

commit;

drop table b cascade constraints;

create table b
 (COL1  NUMBER
 ,COL3  NUMBER
 ,COL5  NUMBER);
 
declare
 clist varchar2(32000);  -- Make this big for a long column list.
begin
 select listagg(column_name, ',')
  within group
  (order by column_name)
    into clist
    from sys.user_tab_cols
   where table_name = 'A'
     and column_name not in ('COL2','COL4');
 execute immediate 'insert into b (select '||clist||' from a)';
 commit;
end;
/

select * from a;
select * from b;

The output is:

SQL> drop table a cascade constraints;

Table dropped.

SQL> create table a
  2   (COL1  NUMBER
  3   ,COL2  NUMBER
  4   ,COL3  NUMBER
  5   ,COL4  NUMBER
  6   ,COL5  NUMBER);

Table created.

SQL> insert into a values (1,2,3,4,5);

1 row created.

SQL> insert into a values (2,3,4,5,6);

1 row created.

SQL> insert into a values (3,4,5,6,7);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table b cascade constraints;

Table dropped.

SQL> create table b
  2   (COL1  NUMBER
  3   ,COL3  NUMBER
  4   ,COL5  NUMBER);

Table created.

SQL> declare
  2   clist varchar2(32000);  -- Make this big for a long column list.
  3  begin
  4   select listagg(column_name, ',')
  5    within group
  6    (order by column_name)
  7      into clist
  8      from sys.user_tab_cols
  9     where table_name = 'A'
 10       and column_name not in ('COL2','COL4');
 11   execute immediate 'insert into b (select '||clist||' from a)';
 12   commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> select * from a;

      COL1       COL2       COL3       COL4       COL5
---------- ---------- ---------- ---------- ----------
         1          2          3          4          5
         2          3          4          5          6
         3          4          5          6          7

SQL> select * from b;

      COL1       COL3       COL5
---------- ---------- ----------
         1          3          5
         2          4          6
         3          5          7

The secret here is in the listagg built-in that is new to 11g.  (If you are not using 11g then the solution will require more code.)

The above code is pivoting the column list for table a (from sys.user_tab_cols) to fashion a SQL column list that looks like "(COL1,COL3,COL5)" that is then seeded into the insert statement as the column list of the query used to obtain values for the insert.  I intentionaly selected the odd-numbered columns so as to demonstrate that we have arbitrary control over the column list.

Cheers,
Opus

Offline

#4 16 Dec 2011 08:46

Opus
Member
Registered: 15 Dec 2011
Posts: 17

Re: How to avoid hard-coding very large numbers of column names

I know this was an old thread, but it was an interesting challenge.

Cheers,
Opus

Offline

#5 17 Dec 2011 02:50

Rapparee
Member
Registered: 26 Jan 2011
Posts: 2

Re: How to avoid hard-coding very large numbers of column names

Yes it was old but similar problems are common in data migrations so it'll come in handy next time
Thank you for your effort!

Offline

#6 17 Dec 2011 03:06

Opus
Member
Registered: 15 Dec 2011
Posts: 17

Re: How to avoid hard-coding very large numbers of column names

This is the sort of challenge that hones a DBAs skills.  I seriously had my doubts that it could be accomplished without coding the long list of column names.  I was about to offer a query of the dba_tab_cols table that would at least generate your column list for you, but instinct told me that there had to be a way.  Then the listagg() built-in function occurred to me as a possibility.  I am grateful for the exercise.

Cheers,
Opus

Offline

#7 03 Jan 2012 09:16

sanjay
Member
Registered: 03 Jan 2012
Posts: 3

Re: How to avoid hard-coding very large numbers of column names

you can also avoid define columns which are not in table B

=============================================================
drop table a cascade constraints;

create table a
(COL1  NUMBER
,COL2  NUMBER
,COL3  NUMBER
,COL4  NUMBER
,COL5  NUMBER);

insert into a values (1,2,3,4,5);
insert into a values (2,3,4,5,6);
insert into a values (3,4,5,6,7);

commit;

drop table b cascade constraints;

create table b
(COL1  NUMBER
,COL3  NUMBER
,COL5  NUMBER);

declare
clist varchar2(32000);  -- Make this big for a long column list.
begin
select listagg(column_name, ',')
  within group
  (order by column_name)
    into clist
    from sys.user_tab_cols
   where table_name = 'A'
     and column_name in (select column_name from sys.user_tab_cols where table_name = 'B');
execute immediate 'insert into b (select '||clist||' from a)';
commit;
end;
/

select * from a;

select * from b;

Offline

#8 17 Jan 2012 20:50

Opus
Member
Registered: 15 Dec 2011
Posts: 17

Re: How to avoid hard-coding very large numbers of column names

While this may seem to be an obvious improvement, the other approach is better because it is source-driven instead of target.  What if, for example, we had non-mandatory columns in B that do not exist in A.

Cheers,
Opus

Offline

Board footer

Powered by FluxBB