I want to insert all columns data of an table in to another table in form of rows

Question

-- Create table

create table RADIOLOGY.TEST_DATA
(
  EMP_ID         VARCHAR2(100),
  EMP_NAME       VARCHAR2(500),
  EMP_CONTACT_NO VARCHAR2(500),
  EMP_ADDRESS    VARCHAR2(500),
  EMP_GENDER     VARCHAR2(500)
);

---

table data is in form as

1   JOHN    03000000    LONDON  MALE

---

and i want output as

EMP_ID         1
EMP_NAME       JOHN
EMP_CONTACT_NO 03000000
EMP_ADDRESS    LONDON
EMP_GENDER     MALE

Answer 1

Your question is:

I want to insert all columns data of an table in to another table in form of rows

What does that mean? Makes no sense at all. Here's why: when I saw it, my first thought was the print_table procedure. I can't remember who is the original author (I am not); was it Ask Tom or someone else, I really don't know & I apologize for that.

Here it is:

SQL> set serveroutput on
SQL> create or replace procedure print_table (p_query in varchar2)
  2     authid current_user
  3  is
  4     l_thecursor     integer default dbms_sql.open_cursor;
  5     l_columnvalue   varchar2 (4000);
  6     l_status        integer;
  7     l_desctbl       dbms_sql.desc_tab;
  8     l_colcnt        number;
  9  begin
 10     execute immediate 'alter session set
 11          nls_date_format=''dd.mm.yyyy hh24:mi:ss'' ';
 12
 13     dbms_sql.parse (l_thecursor, p_query, dbms_sql.native);
 14     dbms_sql.describe_columns (l_thecursor, l_colcnt, l_desctbl);
 15
 16     for i in 1 .. l_colcnt
 17     loop
 18        dbms_sql.define_column (l_thecursor,
 19                                i,
 20                                l_columnvalue,
 21                                4000);
 22     end loop;
 23
 24     l_status := dbms_sql.execute (l_thecursor);
 25
 26     while (dbms_sql.fetch_rows (l_thecursor) > 0)
 27     loop
 28        for i in 1 .. l_colcnt
 29        loop
 30           dbms_sql.column_value (l_thecursor, i, l_columnvalue);
 31           dbms_output.
 32            put_line (
 33              rpad (l_desctbl (i).col_name, 30) || ': ' || l_columnvalue);
 34        end loop;
 35
 36        dbms_output.put_line ('-----------------');
 37     end loop;
 38
 39  end;
 40  /

Procedure created.

What does it do? Presents data in format you wanted:

SQL> exec print_table('select * from emp where deptno = 30');
EMPNO                         : 7499
ENAME                         : ALLEN
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 20.02.1981 00:00:00
SAL                           : 1600
COMM                          : 300
DEPTNO                        : 30
-----------------
EMPNO                         : 7521
ENAME                         : WARD
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 22.02.1981 00:00:00
SAL                           : 1250
COMM                          : 500
DEPTNO                        : 30
-----------------
EMPNO                         : 7654
ENAME                         : MARTIN
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 28.09.1981 00:00:00
SAL                           : 1250
COMM                          : 1400
DEPTNO                        : 30
<snip>

Now, back to "makes no sense": how exactly do you plan to insert something like that into another table? How does that "another table" look like? Does it have (up to) 1000 columns (which is/was the limit) so e.g. each employee would be inserted into one column? I've never seen such a data model. How would you use it? Present to end users? Honestly, no idea and I don't like it at all.

If I misunderstood the problem, please, explain it.