-
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
sat
-
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;
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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
Last edited by portal; 10-19-2004 at 06:16 PM.
sat
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|