Urgent help with this qry

[portal]

Folks I'm trying to run this query and its returning this error.
Please help me out with this at the earliest..

create table &prex.diff_cols1
( TABLE_NAME VARCHAR2(30),
COLUMN_NAME VARCHAR2(30),
DATA_TYPE VARCHAR2(9),
DATA_LENGTH NUMBER,
DATA_PRECISION NUMBER,
DATA_SCALE NUMBER,
NULLABLE VARCHAR2(1),
COLUMN_ID NUMBER,
DEFAULT_LENGTH NUMBER,
DATA_DEFAULT varchar2(2000));
declare
cursor c1 is
select
l.TABLE_NAME ,
l.COLUMN_NAME,
l.DATA_TYPE ,
l.DATA_LENGTH,
l.DATA_PRECISION ,
l.DATA_SCALE ,
l.NULLABLE,
l.COLUMN_ID ,
l.DEFAULT_LENGTH ,
l.DATA_DEFAULT
from user_tab_columns l,&prex.common_tables c
where c.table_name=l.table_name ;
TYPE rec is record (
TABLE_NAME VARCHAR2(30),
COLUMN_NAME VARCHAR2(30),
DATA_TYPE VARCHAR2(9),
DATA_LENGTH NUMBER,
DATA_PRECISION NUMBER,
DATA_SCALE NUMBER,
NULLABLE VARCHAR2(1),
COLUMN_ID NUMBER,
DEFAULT_LENGTH NUMBER,
DATA_DEFAULT varchar2(2000)
);
c rec;
begin
open c1;
loop
fetch c1 into c;
exit when c1%NOTFOUND;
insert into &prex.diff_cols1 values
(c.table_name,c.column_name,c.data_type,c.data_length,
c.DATA_PRECISION, c.DATA_SCALE, c.NULLABLE, c.COLUMN_ID,
c.DEFAULT_LENGTH, c.DATA_DEFAULT);
end loop;
end;
/
error is
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 32
Thanks a Lot

[LKBrwn_DBA]

Some of your columns may be too small, try this:

Create Table &Prex.Diff_Cols1
     ( Table_Name	Varchar2(32)
     , Column_Name	Varchar2(32)
     , Data_Type	Varchar2(32)
     , Data_Length	Number
     , Data_Precision	Number
     , Data_Scale	Number
     , Nullable		Varchar2(1)
     , Column_Id	Number
     , Default_Length	Number
     , Data_Default	Varchar2(2000)
     );
Declare
Cursor C1 Is
Select
       L.Table_Name 
     , L.Column_Name
     , L.Data_Type 
     , L.Data_Length
     , L.Data_Precision 
     , L.Data_Scale 
     , L.Nullable
     , L.Column_Id 
     , L.Default_Length 
     , L.Data_Default 
  From User_Tab_Columns L, &Prex.Common_Tables C
 Where C.Table_Name=L.Table_Name ;

Begin
  For C In C1 Loop
    Insert Into &Prex.Diff_Cols1
    Values (C.Table_Name
           ,C.Column_Name
           ,C.Data_Type
           ,C.Data_Length
           ,C.Data_Precision
           ,C.Data_Scale
           ,C.Nullable
           ,C.Column_Id
           ,C.Default_Length
           ,C.Data_Default);
  End Loop;
End;
/

Or just this:

Insert Into &Prex.Diff_Cols1
Select
       L.Table_Name 
     , L.Column_Name
     , L.Data_Type 
     , L.Data_Length
     , L.Data_Precision 
     , L.Data_Scale 
     , L.Nullable
     , L.Column_Id 
     , L.Default_Length 
     , L.Data_Default 
  From User_Tab_Columns L, &Prex.Common_Tables C
 Where C.Table_Name=L.Table_Name;

[portal]

Thanks you very much , Used your idea and the problem is solved..
but this still exists can you please give me some idea on this too.

select a.constraint_name,a.table_name,b.table_name from
user_constraints a, user_constraints@tcaint b,
(select z.constraint_name from
(select constraint_name, table_name from useR_constraints union
select constraint_name, table_name from user_constraints@tcaint) z
group by constraint_name having count(*) >1) q
where a.constraint_name = q.constraint_name and
b.constraint_name=q.constraint_name
and a.table_name != b.table_name;

ERROR at line 2:
ORA-01445: cannot select ROWID from a join view without a key-preserved table
ORA-02063: preceding line from TCAINT
Please advice
Thanks