Urgent help with this qry

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

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

Code:

---

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:

Code:

---

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;

---

:rolleyes:

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