Re: Sorting by primary key

Gordon Duns <Gordon_at_gctd.demon.co.uk> wrote:

>Is there a way of selecting records from a table in order of primary
>key without specifying the column names?
>What I want to do is generate a series of SQL enquiries as follows :-
 

>select 'select * from scott.' || table_name || ' order by ??????;'
> from dba_tables
> where owner='SCOTT';
 

>?????? being the primary key of each table.
 

>--
>GD

this assumes that your primary keys consist of one column. I'll let you figure out how to do this with multiple col keys. ( hint: use PL/SQL and a cursor loop )

create table pk (

   table_name varchar2(30),
   col_name varchar2(30)
)
/

insert into table pk(table_name, col_name) select a.table_name, b.column_name
from dba_constraints a, dba_cons_columns b where a.owner='SCOTT'

and b.owner = 'SCOTT'
and b.table_name = a.table_name
and a.constraint_type = 'P'

select 'select * from scott.' || a.table_name || ' order by ' b.col_name || ';'

       from dba_tables a, pk b
       where a.owner='SCOTT' 
       and b.table_name = a.table_name

Jar

jared still                  ---- ___o
jared_at_valleynet.com       ----  _`\ <,_
                       -----   (*)/o (*)
--------------------------------------