Re: How do you get a verticle list of table columns plus sample value?

From: lujun <lujun_at_asiainfo.com>
Date: Fri, 20 Jul 2001 17:30:05 +0800
Message-ID: <9j8tko$gpd$1_at_news.cz.js.cn>


You can do it as follows:
  1 select '''' || column_name || ' '' ' || '|| ' || column_name || '|| ' || 'chr(10) ||' SQL
  2* from all_tab_columns where table_name = 'EMP' SQL> /

'EMPNO ' || EMPNO|| chr(10) ||
'ENAME ' || ENAME|| chr(10) ||
'JOB ' || JOB|| chr(10) ||
'MGR ' || MGR|| chr(10) ||
'HIREDATE ' || HIREDATE|| chr(10) ||
'SAL ' || SAL|| chr(10) ||
'COMM ' || COMM|| chr(10) ||
'DEPTNO ' || DEPTNO|| chr(10) ||

8 rows selected.

  1 select

  2  'EMPNO ' || EMPNO|| chr(10) ||
  3  'ENAME ' || ENAME|| chr(10) ||
  4  'JOB ' || JOB|| chr(10) ||
  5  'MGR ' || MGR|| chr(10) ||
  6  'HIREDATE ' || HIREDATE|| chr(10) ||
  7  'SAL ' || SAL|| chr(10) ||
  8  'COMM ' || COMM|| chr(10) ||
  9  'DEPTNO ' || DEPTNO|| chr(10)

 10* from emp
SQL> /
EMPNO 7369
ENAME SMITH
JOB CLERK
MGR 7902
HIREDATE 17-DEC-80
SAL 800
COMM
DEPTNO 20 EMPNO 7499
ENAME ALLEN
JOB SALESMAN
MGR 7698
HIREDATE 20-FEB-81
SAL 1600
COMM 300
DEPTNO 30 "John Perkins" <jperkins_at_cwcom.zap.net> wrote in message news:84qsjtkehbiirvrcvab26s55i17e327jvt_at_4ax.com...
> Can anyone offer a method of obtaining a sample record from a table
> which lists each column vertically plus a sample value? VAX Datatrieve has
> a very useful command called LIST which does this but I haven't found an
> Oracle or Steven Feuerstein package that does a similar job. I have used a
> complex PL/SQL loop using ALL_TAB_COLUMNS but am having a job getting this
> to generate dynamic SQL in the spooled output. For example,
> the output from SCOTT.EMP would look like this:
>
> EMPNO 7521
> ENAME WARD
> JOB SALESMAN
> MGR 7698
> HIREDATE 22-Feb-81
> SAL 1250
> COMM 500
> DEPTNO 30
>
> Any suggestions welcomed!
> TIA,
> John
>
Received on Fri Jul 20 2001 - 11:30:05 CEST

Original text of this message