SQL & PL/SQL

Hi,
Assume that we have the following tables and the declaration of their constraints:

create table a(col1 number(1), col2 date, col3 number(1), col4 varchar2(20), col5 number(1), col6 varchar2(10));
alter table a add constraint a_pk primary key(col1, col2);

create table b(col1 number(1), col2 date, col3 number(1), col4 varchar2(20), col5 number(1), col6 varchar2(10));
alter table b add constraint b_pk primary key(col2, col3);
alter table b add constraint b_chk1 check(col4 is not null);

create table c(col1 number(1), col2 date, col3 number(1), col4 varchar2(20), col5 number(1), col6 varchar2(10));
alter table c add constraint c_pk primary key(col3, col5);
alter table c add constraint c_chk2 check(col1 is not null);
alter table c add constraint c_chk3 check(col2 is not null);

I want to have the columns of each table in the following order:
First the columns of primary key,
Second, the columns of not null constraints, preferably by the ordering of column column_id in the user_tab_cols view,
Third, all the remaining columns by the ordering of column column_id in the user_tab_cols view.

The query i have written so far to get the above prerequisites is:
select column_name
from
(
select utc.column_name,
rank() over(partition by ucc.column_name order by case when ucc.constraint_type = 'P'
then to_char(ucc.position)
when ucc.constraint_type = 'C'
then ucc.column_name
end) rnk
from user_tab_cols utc
left outer join
(select uc.table_name, uc.constraint_type, ucc.column_name, position, uc.constraint_name
from user_constraints uc
join user_cons_columns ucc
on ucc.constraint_name = uc.constraint_name
where uc.constraint_type in ('P', 'C')
) ucc
on ucc.table_name = utc.table_name
and ucc.column_name = utc.column_name
where utc.table_name = :i_table_name
)
where rnk = 1;

The above query succeeds in getting the primary key columns and not null columns - assume that using the constraint type = 'C' fetches the not null columns only- not always with the desired order(first the pk and then the not null columns) but it seems it gets the columns before all the remaining columns.

So, for the table A, this is the desired order:
col1, col2, col3, col4, col5, col6

For the table B:
col2, col3, col4, col1, col5, col6

For the table C:
col3, col5, col1, col2, col4, col6

The truth is that sometimes the column order is as i want it to be, but it is not always that for sure simply because it is not used the clause 'order by'. If i use it, the results are also wrong (since it neglects the existence of primary key and the not null columns):

             select column\_name, column\_id  
             from  
             (  
             select utc.column\_name, utc.column\_id,  
                       rank() over(partition by ucc.column\_name order by case  when ucc.constraint\_type = 'P'  
                                                                                then to\_char(ucc.position)  
                                                                                when ucc.constraint\_type = 'C'  
                                                                                then ucc.column\_name  
                                                                                end) rnk  
                      from user\_tab\_cols utc  
                      left outer join  
                               (select uc.table\_name, uc.constraint\_type, ucc.column\_name, position, uc.constraint\_name  
                                 from user\_constraints uc  
                                join user\_cons\_columns ucc  
                                on ucc.constraint\_name = uc.constraint\_name  
                               where uc.constraint\_type in ('P', 'C')  
                               ) ucc  
                         on ucc.table\_name = utc.table\_name  
                        and ucc.column\_name = utc.column\_name  
                      where utc.table\_name = :i\_table\_name  
                     )  
                     where rnk = 1  
                     order by column\_id;  

How can the query be written ?

Note: I use Oracle DB11g v2.

Thank you,
Sim