Urgent help with this qry
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Urgent help with this qry

  1. #1
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87

    Angry 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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Talking

    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

  3. #3
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87
    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 07: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
  •  


Click Here to Expand Forum to Full Width