get field type form tables

[castleWolf]

Hallo,
I wanna to find the all 'number' type of fields inside the tables of whole databases
for example
1)loop inside the all_tables
2)find all 'number' type fields
3)if found insert into new table ,table name and field name

that is all what i want
i did some parts but i can't check the number type
thanx in advance

 

[SantaMufasa]

Wolf,

Here is a script I built for you, making certain presumptions about your needs/preferences:

col a heading "Owner.Tablename" format a20
col b heading "Column_name" format a20
col c heading "Data definition" format a20
select owner||'.'||table_name a
      ,column_name b
      ,data_type||decode(data_precision||data_scale
                        ,NULL,NULL
                        ,'('
                        )
                ||decode(data_precision
                        ,NULL,NULL
                        ,data_precision
                        )
                ||decode(data_scale
                        ,NULL,NULL
                        ,','||data_scale
                        )
                ||decode(data_precision||data_scale
                        ,NULL,NULL
                        ,')'
                        ) c
from all_tab_columns
where data_type in ('NUMBER','FLOAT')
order by a, COLUMN_ID
/

Owner.Tablename      Column_name          Data definition
-------------------- -------------------- ---------------
...
TEST.S_EMP           ID                   NUMBER(7,0)
                     MANAGER_ID           NUMBER(7,0)
                     DEPT_ID              NUMBER(7,0)
                     SALARY               NUMBER(11,2)
                     COMMISSION_PCT       NUMBER(4,2)
TEST.TT_280          A                    NUMBER
                     B                    NUMBER(5,0)
                     C                    NUMBER(5,2)
                     D                    NUMBER(,0)
                     E                    FLOAT(63)
TESTNEW.S_EMP        ID                   NUMBER(7,0)
                     MANAGER_ID           NUMBER(7,0)
                     DEPT_ID              NUMBER(7,0)
                     SALARY               NUMBER(11,2)
                     COMMISSION_PCT       NUMBER(4,2)
...

Notice the definitions for table "TEST.TT_280":

a) a "NUMBER" data definition with no precision and scale values results from a "<col> NUMBER" column declaration. It can hold a value up with magnitude of 10 ** 125, with 40 digits of accuracy.

b) a "NUMBER(,0)" data definition results from a "<col> INTEGER" column declaration.

c) a "FLOAT(63)" data definition results from a "<col> REAL" column declaration.

Let us know if any of this is helpful.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[castleWolf]

Yes it helped me thanks,but i have another problem i must make the pivot of this result(write all in one row for one table )
exactly :
table col1 col2 col3 col4 col5
TEST.S_EMP ID MANAGER_ID DEPT_ID SALARY COMMISSION_PCT
TEST.TT_280 A B C D E

i was informered by using decode but there has problem of column
in every table has the different column and his count

 

[SantaMufasa]

...this is all what I want.

Obviously, "it was not all what you want." It is when we have all the specifications up front, Wolf.

So, here is your new code. (Since you didn't specify what character you want to separate your output columns, I built the function to allow you to specify any characters string to become your separator when you invoke the function.):

Section 1 -- User-defined function to "string up" the names of the number columns for tables you pass.

create or replace function col_stringer
    (owner_in varchar2,tab_in varchar2,delim varchar2)
    return varchar2
is
    hold_string varchar2(2000);
begin
    for c in (select column_name
                from all_tab_columns
               where owner      = owner_in
                 and table_name = tab_in
                 and data_type in ('NUMBER','FLOAT')
               order by column_id) loop
        hold_string := hold_string||delim||c.column_name;
    end loop;
    return ltrim(hold_string,delim);
end;
/

Function created.

...invocation and output of the function:

col a heading "Owner.Tablename" format a20
col b heading "Numeric Colums" format a100
select owner||'.'||table_name a
      ,nvl(col_stringer(owner,table_name,', '),'(no numeric columns)') b
  from all_tables
  where col_stringer(owner,table_name,', ') is not null
    and table_name in('TT_280','S_EMP','YADA2')
 order by a;

Owner.Tablename      Numeric Colums
-------------------- -----------------------------------------------
TEST.S_EMP           ID, MANAGER_ID, DEPT_ID, SALARY, COMMISSION_PCT
TEST.TT_280          A, B, C, D, E
TESTNEW.S_EMP        ID, MANAGER_ID, DEPT_ID, SALARY, COMMISSION_PCT

In the invocation, above, I limited output rows to just three tables (to avoid overwhelming you readers); and I wanted just rows with numeric columns, i.e., "col_stringer(owner,table_name,', ') is not null".

Let us know if this is closer to what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.